Making an Easy Button for Data Access (2021-US-30MP-868)

Speaker

Transcript

Peter Hersh

Alright, well, thanks for joining us today. Hadley and I are going to be talking about data access and a way to streamline that and make it a little easier using the JMP application builder.

So here is an overview of the analytic workflow inside of JMP, and the first step in any analytic journey is to access your data.

And oftentimes this can be a time-consuming and tedious process and it's many times overlooked as a

critical process that needs to be done before you can do any of the learning from your data. You need to be able to access it. So Hadley and I are going to show a couple tricks on how we can access data from different sources and hopefully ease the pain of that.

So what we're going to show today is creating an easy button, which is

basically using the app builder inside of JMP to access data externally, either through an OBDC connection or a webAPI.

You can also apply this to local files or any type of data source that you might have that JMP can access. And then we'll also show how we can combine sources

and apply filters to multiple different sources in the data pull. And then finally we're going to wrap up by showing an interface on how to enter data manually,

but make that a little easier process. And then in the end, how we can distribute anything that we make throughout the organization and make this a

distributable add-in that we can share. So we're going to start out with Hadley showing us how to connect through the ODBC driver.

Hadley Myers

Yep.

Thank you very much, Pete.

Hello to everyone watching this wherever you are. So I'm going to start off by showing you this app here that was made using the application builder and what it does, is it connects to a database.

You've got, perhaps in your own system, different locations or different servers where your data is stored, so you can select the appropriate one. And what this does is it allows you to search and filter for tags. Now what you're seeing here is one

SQL query. And we've reached into the database, we've pulled out some tags, I can select the ones that are appropriate.

I can choose the time period that I'm interested in looking at, in this case, the database goes back quite a ways. Run that data and select...

you know, immediately pull in the data that I need. How we've seen people work with these is they take these things and they put them in the corner

of their desktop, like this, and anytime they need data say, Oh, I should have gotten the 102s as well. That's no problem. I can just go, pull those like that.

So this is the type of thing that we're going to show you how to build today, and the example that we're going to use is a little bit simpler than this.

You can see that here. So simply select the time period, choose whatever

one of the tags that you'd like to search for, in this case, it's an analysis method. It's a filter on one of the categorical columns, and then pull the data in. So the more complicated one you saw is just an extension of adding functionality to this.

So we're going to start out by using the query builder in JMP.

Once we've connected to our database, which I've done here already, I'm going to go in and select the table that I'd like to start with. And in this case, it's my production data and did this. I'm going to join my result

and my sample data here. So let's go ahead and build this query. I'm going to start in by pulling in my lot. I'm going to pull in all of my production data.

I'm going to put in the sample date created, and the analysis, and perhaps I'll take these and put them near the top, so that you can see them a

little bit better. So those will be the first columns,

as well as my result. So if I were to set up this query using the filters, what we could do is, for example, filter on our analysis by putting that there.

And filter on our sample date here, and maybe we'll change that to a range, so we say predict...grab data between one date and

another date. Now normally, what you would do in this case is you would

prompt all on run, so that when you ran the query,

you could have your user select

the appropriate dates, and again I think this database goes back a bit of a way, so we'll just choose a very broad range there. Press OK. And now I've got my data for that analysis and that...

those dates, but what I'm going to do

is I'm going to unprompt these, so clear prompts. I'm going to manually select a few, just so I can see how these fit into the query...

into the code.

Yeah.

So I can go ahead and copy this run script, right, which executes this query, including the filters. And now what I'm going to do is I'm going to go and open application builder.

I will choose a blank application and will create a button.

So when I right click on the button, I can choose what script I want to run when the button is pressed. And in this case we're going to simply run the entire run script from our query.

So now, if I were to run this, I press the button, I get my data in exactly the way I set up the query. But what I would like to do, and perhaps what you'd like to do, is be able to allow a user to choose the query filter

variables, so which time period to choose and which analysis, and for that it's

amazingly straightforward.

What I'm going to do is I'm going to grab two number edit boxes for the start and end times of my query.

And what I always like to do is to take these and put these within a panel box. It just makes it a bit easier for the user to see exactly what it is that they're...you know, it allows me to put an instruction. So I can write here, something like, enter start date

and enter end date.

Like that.

And we'll give these variables names that we can understand.

So we'll call this one...if I click on the number edit box, I can name this guy

start

number edit box.

And we'll call this one end

number edit box, and we can also address the formats

so that we use our

date and time calendar format here.

Just like this.

And, of course, what we need to do is get the information from these

data entry

variables into our query. And so when the button is pressed, what we'll do is we'll create a variable called start

and we'll make that equal to whatever value is

typed into our number edit box.

Get that value.

We'll get the value from our end number edit box.

And we'll take these

and insert them into our query here.

So if I were to run this,

I can choose my start date

and choose my end date.

I can run that and get my data. Now I'll show you one more very quickly, how can we filter on this analysis. For that I'm going to use this...

I've got different options for different buttons I can use. I think what I'm going to do is I'm going to include this checkbox here, and I can put in the values that I'd like them to select on.

Now, maybe we'll change this to analysis.

Yeah, check box. So here I'll simply create another variable analysis.

And if I can't remember what command I need to use to get the info...to get the selection out of this, all I need to do is click on this.

And if I right click, a nice trick is I can go right to the help menu, right to the scripting index, and I can see now that what I need is the get selected command to be able to get those.

I'll type here

Get selected.

And now we can take this analysis

and replace our

query parameters right in there. So if I would run this.

Once again,

I can select my start time

and select my end time.

And I can choose one of the parameters to run or the other, or both.

So the next step...

and of course, I can add to this as much as I want, but what I'd like to do at this point is distribute it within my organization, and the trick here is that anyone who has access to this

database location will be able to access or make use of this add-in if I were to share with them. So what I'm going to do is click here and save the script to an add-in.

We'll call it database

access

and

dbaccess1,

just to give it a unique name.

Access data. And the entire script needed to be able to make the add-in work is located within here, so it's already...it's already all done. I'll save that to my

desktop.

And as you can see now, it's installed right here.

So all I need to do is take this add-in, which I think is saved on my other screen.

Here it is, and I can email that to anybody within my organization. I've written this email ahead of time

already for your convenience. Email that and if they have access to this location, they'll be able to install the add-in and use it right away. And at this point I'd like to stop sharing and send...

move back over to Pete, who will show us how to access data from the web.

Peter Hersh

All right, thanks, Hadley, And yeah, great great demonstration. So in this part we're going to build off what Hadley had shown there.

And we're going to end up with an add-in that looks like this. So going through the same process that Hadley did. I'm going to skip the ODBC part and show

an API part, but the idea here is, I have two different data sources. I'm going to pull data from both of those sources, join it together, and then create a final data table like this. So we're gonna start with what what Hadley had shown already completed, which is

a simple ODBC pull with even less detail than Hadley had, where we just have

data on two different stock types and we have one filter that it's looking at to select that stock type, and what we're going to do is build in a

API connection. So I'm going to go through a similar process that Hadley did. He described it so well, I'm going to just go a little quick here and add a panel box with a text edit box in here, and like Hadley showed, we can

use and reference these

boxes, because they have a name. And I could go through and rename these like Hadley did.

In this case, this is just called TextEdit1, and what I'm going to tell people to do is enter their API key entity here and that API key we'll use to

have each individual person be able to access data using their own API key, so not having to distribute in the API key along with this. And then, just like Hadley showed, I'm going to create a button here and we'll call this button

API data pull.

All right, and and again the same thing, we're going to right click on here and add a script on press. So where we're accessing this data from is

this website called Alpha Vantage, and this is just a good way to demo and try out an API data pull. It has very nice API documentation. If you were going to do this inside of your own company, you would have slightly different

commands that you would be using, but the general idea will be the same, is that you have to pass a few things from a...

from JMP to that API to pull in. And here, I've prewritten this script, so basically, I have to pass in a key, I have to tell it the URL, what type of request I'm going to do, and then I have some

more script down here that works on getting that data in the right form for JMP, so parsing that JSON. So I'm going to select this, which I've already written here. Going to copy that and then, just like we've seen before, if I put that in here, now I have my

data pull in there. So a couple things I need to do here is this key is...I actually want to reference that API key here.

So what I'm going to do is say, okay, that key equals

textedit1 and I'm going to just say, just like Hadley showed you could

go through that help index and figure out what is needed to grab that text, but in this case I'm just going to say, the key is...you go to that textedit1 and you get the

text. The other thing here is, I want to reference this box here, which happens to be called Combo1, to filter on. So I'm going to add that

down here as well, and we'll just call that stock

equals that variable name, Combo1, and then this again, just like you Hadley showed, is get selected.

Alright, so now what I'm going to do is my query is going to grab that key, grab that stock, and then go out to the website, ping that, and let's go ahead and run this to show. So I'm going to just use a demo key here. We'll maybe change the stock.

It will...oh, you know what? That's a good

thing here is, I forgot to change that in the code here, so I'm saying the stock is combo1, but over here

this is telling me that my symbol is Microsoft, so that is...if I don't put in that stock there, it won't do the pull properly. So let's show that one more time here. Show you that it is now looking at the filter and not hard coded in, so now it's pulling the right data.

Okay, so now, the next step is I want to actually grab data from both these sources and bring them together. So there's a nice feature inside of JMP 16,

which hopefully you've all heard about here, which is this new, improved log, and I'm going to go ahead and clear that out.

And this log, what it does is it captures any type of data manipulation you might do. So let me show you an example here of a join. So I'm going to just join together these two

data tables and you can pick how you'd want to join, maybe, what type of

columns you want in that join, maybe name

your

data how you want to. And once you're happy with this, you can hit Okay. And so you'll notice here in that log that it has captured all I need for that join, so I'm going to go under here, go to save script to the clipboard and then I will just

go back and create a new button in my app

here. And again, I'm going to use a button box, but you could pick whatever type you'd like. Again give...maybe give this a name; we'll call this join.

Right click on here.

And do that script on press.

And then just a copy and paste.

And there now, it will go through and do the join. So really, you know, we could pull each individual ones and join, but I think the end goal is to do this all in one step so.

All you have to do is grab all of these scripts into one place, which I have happened to do ahead of time,

and the...and then create a new button. I'm going to show two things I added to this script once I put that in there, so we'll call this the easy button to stick with the theme here.

Easy button.

And I'm going to just

grab this script here, so I'll select all of this, copy that and then

do the same procedure here.

And paste that.

Alright, so a couple things I added to this script beyond just copying and pasting what was already there, was this wait command and what that's doing is giving me a little bit of time to

to let the data pull happen before it starts joining, and then I'm closing out those two additional data tables, so I don't...I'm not just building in multiple tables. So let's

look at this. I'm going to put in my API key, I'm going to select my stock. I'll hit the easy button, pulls from two places, waits for two seconds, and then joins them together.

So that is the end goal here, and now I'm going to pass it back to Hadley to show a good example on how to do the data entry a little smoother.

Hadley Myers

Yeah.

Thank you very much, Pete. So we've showed you now how to get data from an external database using SQL

database and Pete showed you how to get data from an API...web API and then combine it with some data that you've got. It could be that you want your data in some other source, maybe you need to access it using Python, that's all fine,

or it's in different files, but it also could be that you need to input the data manually. And this is an example of a data set here,

where we've got cereal names and manufacturers, and all of this data has been inputted manually from the labels of these boxes, which unfortunately aren't in a format where the data can be easily pulled in automatically. So somebody's going to need to do this

manually. I used to have a job like that once where I did it, and so I can definitely sympathize with the person who needs to do that.

Now, you could have it inputted on the...from the table like this, but what I've done is I've made it a little simpler by creating this application here.

So you can see now that they can enter it from simply, you know, one screen and then that will add it to the table. Now I have intentionally left out the last two

columns. The method for doing this would be exactly the same. What we're going to do is...so you see here that the first thing that the button does

is it adds a row to the table. It figures out how many rows the table has and then, for each column and row number,

it will simply grab the data from each of our inputs and then save them to each one of those cells, right. So let's go ahead and do it for the last two.

And you might have noticed that this is quite even and, you know, nicely spaced out, so that was done using these nice splitters here, the horizontal and vertical splitter. So I'm going to go ahead and add a horizontal splitter just at the bottom here.

And to this, we will add a panel box

and a number edit box at the bottom.

And I will add yet another panel box

and another number edit box. So we'll call this,

our last two columns here, which is weight per serving and our

cups per service from our cereal labels.

Right now, I can rename these guys, but I won't do that for now. All I need to do here is go ahead.

And in our data table column, weight per serving.

And our row number.

I will make that equal to our number edit box that we just created.

The same thing here

for our

cups per serving

Hadely Myers

under edit2.

Hadley Myers

Right, so now, if I were to run this, I can put in all of the data and then get it

into these

cells or to add the row at the bottom, but I'm going to show you one last thing, which, if you're creating this for someone, I promise you they will greatly appreciate it, that's to add

a little bit of color to the picture just over there to the...to the application. So let's go ahead and you can add a company logo, or you can just add a stock photo like I've done here.

So it doesn't have to be perfect. If we just add it in like that, you can see that when we run this, it makes somebody's day just a little bit nicer than it was before. Now, rather than saving this as an add-in or saving it

to a script, what I'm going to do is go ahead and save this right away to the data table.

Call it add

data or add row, whatever and now you can see here we've got our add-in right there. So I can run this add-in

cereal. The nice thing is that if I press tab, it goes to the next one.

I'll just add some pretend data

and so on. Right, add that row and you see that.

So, hope you found that interesting, hope there's an opportunity that some of you have to be able to use these or create these. And with that, I will pass things back to Pete.

Peter Hersh

Great, thanks, Hadley.

I am just going to wrap up here with a couple more

slides. So summarizing what we saw here, there's oftentimes difficulty with accessing data, and that means problems can persist longer than they needed to. So we hope that you found some benefit out of this and can streamline that data access or even data entry.

And

getting... getting your data in the right format and

easily accessible is about 80% of your solution, so usually that's where you spend 80% of your time. So if you can streamline that, it can be a huge time savings,

and have your smart analysts and scientists and engineers working on the data problems, not not data access problems.

And we showed that creating easy buttons is pretty straightforward inside of JMP's application builder, where you're able to choose a source, filter, combine multiple sources, and even add data manually.

And then, like Hadley showed, we can distribute that throughout the organization with an add-in or we can also do that with just a script or an app itself.

So thank you for your time. Hope you guys found this useful and we will answer any questions that you guys put into the chat down below. Thank you.