Making an Easy Button for Data Access (2021-US-30MP-868)
Hadley Myers, JMP Sr Systems Engineer, SAS
Peter Hersh, JMP Senior Systems Engineer, SAS
Accessing data is often a very time-consuming and aggravating step of the data workflow. It can delay solutions being implemented or allow problems to persist undetected, often with disastrous consequences. Why can’t someone create an “easy button” for data access, allowing you to pick your data source and filter down to what you want? Could it be used to combine data from multiple sources, if needed, and even automate reporting so that issues are flagged immediately rather than at the end of a lengthy and tedious “data munging” exercise? In this talk, we show how to create an interface from JMP’s application builder utilizing SQL, and then build a WebAPI example from scratch. The methods shown could also be equally applied to data stored in other formats. The final application will make data access as easy and fast as pushing a button.
Auto-generated transcript...
|
Speaker |
Transcript |
| Peter Hersh | hey Larry. |
| Oh I'm not connected, can you hear me now. | |
| Oh. | |
| let's see I can't hear you. | |
| Something where is it running through. | |
| yeah yeah there you are. | |
| Larry LaRusso | Okay, I can hear you. |
| that's really weird. | |
| To me. | |
| It was working fine so who knows, maybe. | |
| Maybe. | |
| Peter Hersh | If you do three ones bound to fail. |
| The law of averages. | |
| Larry LaRusso | and actually maybe I'm living a better life. |
| Peter Hersh | Total totally usually it's like to will fail. |
| Larry LaRusso | In fact, the last zoom meeting I have not even sure if it. |
| If it recorded properly because I always get a notification that. | |
| It got recorded and I didn't get one, and now I went into the meaning it doesn't look like it did. | |
| Peter Hersh | So no. |
| Larry LaRusso | Well it's worse than that it's an external guy. |
| he's retired, you know. | |
| Peter Hersh | I name sounds familiar I don't. |
| I don't know him personally. | |
| Larry LaRusso | Rochester. |
| Kodak and then Boston mom and he's super nice he's retired he's kind of working on his own time or whatever, but so he probably won't be the end of the world, but. | |
| still looking forward to going back to him and saying Oh, remember that. | |
| Peter Hersh | Remember what you did there. |
| Larry LaRusso | I'm going to check with Jeff and just make sure that I'm not missing something, but I don't see this one has a little recording thing, and I could have sworn in as well, so. |
| yeah. | |
| Peter Hersh | yeah I'm just closing out of all those things like teams. |
| They had. | |
| Hadley Myers | hey. |
| Peter Hersh | I like I like the guitar that's nice touch. |
| Hadley Myers | Well it's it, I wonder if I was just thinking that I don't have a background here, I wonder if I should add one. |
| Peter Hersh | No, no, no, keep it there and let's do the start and you'll just be playing the guitar and I'll be like had had someone's here. |
| Sorry yeah. | |
| yeah. | |
| Peter Hersh | yeah it's way better than. |
| The JMP by default here. | |
| Larry LaRusso | All right, you have you guys have done. |
| I mean I don't think I've seen you before but. | |
| Hadley Myers | I think we've been introduced. |
| Larry LaRusso | yeah. |
| Hadley Myers | Great nice to see you again. |
| Larry LaRusso | yay good to. |
| See you in person, so I'm I'm going to pull up the list, but you guys have done this before, so you know you know the deal here but. | |
| I think we're we are automatically recording release it says that upper left hand corner so we're going to splice it here and there. | |
| I'm going to go ahead and mute myself and get rid of my video just to make sure that there's no way I pop up here anywhere, and if you guys could just kind of give me. | |
| A very obvious, thank you for joining us today are welcome, or whatever you want, so they started and then, if you close it down deep you know you're doing the live version. | |
| Peter Hersh | No we're not. |
| Larry LaRusso | Okay okay so just close it out with thanks for joining us today, so no window to chop it, but the backgrounds look great. |
| So I don't know that I have a whole lot of other things, the same and just whenever you're ready, and if you guys feel like there's just something that you complete they can't live with and want to start again that's that's totally. | |
| Hadley Myers | Fine, the pizza john wait wait wait wait pete's con. |
| Larry LaRusso | O p. |
| Hadley Myers | We lost him. |
| let's wait a second for him to come back. | |
| In the meantime, I want to make sure that I'm using the right audio. | |
| And it doesn't look like I am I want to be this sounds good thing I checked how's that is that better. | |
| Larry LaRusso | Yes, a little lower. |
| Hadley Myers | doesn't really. |
| Larry LaRusso | yeah the. |
| Hadley Myers | Better. |
| Larry LaRusso | That sounds good. |
| Hadley Myers | Okay last API we got worried. |
| Peter Hersh | And that was a fun trick. |
| Larry LaRusso | Well now, now I'm not one of three we talked a little bit. |
| But that was. | |
| Peter Hersh | yeah that was on me, I was trying to hide my taskbar and, for some reason zooms thought that match shut down. |
| So. | |
| Hadley Myers | I think my taskbar it's OK, with everyone because I just have JMPed in one I don't know why I don't see why should. |
| yeah I think there's a fire anybody if I keep it. | |
| Peter Hersh | zoom might zoom might shut down. |
| Hadley Myers | All right. |
| Shall we just practice switching screens, to make sure that we can. | |
| Peter Hersh | Okay I'll share mine first. |
| Make sure I can do that. | |
| Hadley Myers | And we keep our cameras and mike's own when we're not presenting right and. |
| Peter Hersh | You guys aren't seeing this up here right you're not seeing. |
| Or are you. | |
| Hadley Myers | What are you putting up. |
| Peter Hersh | So I'm sharing and then I see the zoom window up here, but you guys don't see that right. |
| You just see a PowerPoint is that that. | |
| Hadley Myers | Will I see a PowerPoint in not in presentation mode. |
| Peter Hersh | yeah yeah. |
| I would present I will go like that okay. | |
| Hadley Myers | No that's all I see is a PowerPoint. |
| Peter Hersh | Okay perfect okay so, then I would go blah blah blah. |
| And then. | |
| Then I'll say okay JMP DEMO hand it to Hadley So do you want me to stop sharing or can you just steal it from me which. | |
| Hadley Myers | let's try, I think I need to. |
| let's see. | |
| yeah if I just do that does that. | |
| yeah you can see my screen. | |
| Peter Hersh | yep. |
| Hadley Myers | stole it from me, I can show you somebody can go up here pull up my dashboard whatever. |
| Peter Hersh | yep. |
| Hadley Myers | And now, can you grab it back. |
| Peter Hersh | yeah Let me try. |
| Okay, then blah blah blah. | |
| All right. | |
| Hadley Myers | I think allison. |
| Peter Hersh | Okay perfect. |
| Larry LaRusso | Good. |
| Hadley Myers | So you start right. |
| Peter Hersh | Yes. |
| Hadley Myers | All right. |
| Larry LaRusso | I'm going away guy. |
| Okay yeah and. | |
| You know that they're recording the speaker view as well, so we're getting we're doing individual speaker slip slide and speaker just slide so just know that you have the potential to be on screen when you're not talking but you guys are. | |
| All right, okay I'm gone guys take it away whenever you're ready. | |
| 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. | |
| Larry LaRusso | awesome thanks guys. |
| Well, you haven't been happy with it. | |
| Peter Hersh | We kept it under a half hour, we were. |
| When we time this out, the first time we we had to take a little time off so. | |
| We figured since it's not semi live it's. | |
| Probably not a big deal right like we. | |
| We can have a be 28 minutes instead of 24 or whatever it was supposed to be so. | |
| Larry LaRusso | It was great so great so I'll go ahead and get this. |
| out to the Jeff and his team, to make sure they've got everything if there's some issues will let you know, but I suspect you're in here. | |
| Peter Hersh | Alright well we'll see if the recording work this time. |
| Larry LaRusso | Oh, my God, I know it did this time. |
| Actually, I like I said it's it's blinking up there, recording so I'm assuming it's good badly, I was telling you before you joined us I data recording with an external guy yesterday and I'm almost certain that didn't record. | |
| I've got to go back to them it's going to be a terrible discussion and I'm super nice he's retired, so I think he has a little more time, but if it's gonna be. | |
| Hadley Myers | Good luck with that so. |
| Larry LaRusso | Luckily, he was a very, very, very nice guy it seems like you're probably pretty well but it's pretty. |
| Alright guys. | |
| Peter Hersh | Well, thanks Larry thanks. |
| Hadley Myers | guys up as well excellent. |
| Peter Hersh | perfect one, take all right bye. |
| Hadley Myers | guys see ya. |
