JMP Live as a Data Warehouse (2025-US-PO-2594)

Hello, everyone. Welcome to my presentation. My name is Timothy Whetstone, and I'm here with my co-presenter, Sage Darling. We both work at IDEXX Laboratories, and this presentation is about using JMP Live as a data warehouse.

We've been using JMP Live quite a bit over the past year. We think it's really great that we can basically set up auto-refreshing tables to our various data warehouses that already exist when we have robust pipelines in place from the source of collection to the warehouse and then into JMP Live. It's really great for that use case.

Since we've been using it more, we found a few potential use cases that we would like JMP Live to be able to do, but it doesn't quite have the functionality to just inherently do it easily. That's mostly in this case of having some data source or some data generation source that is not going into any cloud service or large data warehouse, where you're just basically getting files, getting data, and they're basically only available locally. Especially in cases where you have that, and you keep getting data that really belongs in the same ultimate data set for what you're trying to show, but it's happening temporarily over time. A lot of longitudinal-type studies are things that we were thinking of as we went through this.

We have these manual files. We want them basically all tracked together in the same place. JMP Live is usually a good place for that, but we need a more automated way of getting it there rather than to keep downloading, concatenating, and then re-uploading tables manually.

In talking with the staff at JMP, they had given me some of this bit of code here. This was really key in the work that we've done. It's how you can use a script to download and open a table from JMP Live. It was after we had gotten this bit of code that we really got a lot of ideas for how we could utilize it. This is just pretty much just an HTTP request. It's something that has existed and is used to get data from other websites, but the real key to getting this thing to work is actually how you assemble and construct the URL for your JMP Live tables, and we'll be getting into that in a little bit. Sage, I'm going to hand off to you for a second.

I got that example that Timothy was just describing from JMP in terms of downloading files, and my use case, I was ready to just run with it. What I decided to do was start by just building a way to essentially take tables from JMP Live in their own folder and concatenate them locally, and then push them back up to a specific location. In order to do that, there are some things that you got to be able to work out in terms of being able to connect to JMP Live and being able to get the information from JMP Live. This is where, with my code, I created an associative array that I use on initiation.

This is an example of the code that we have located in the add-in that pulls this information. My script does something similar. What it does is it looks at what the user has access to in JMP Live, and it pulls the folder locations that it has access to, and then we'll continue to find folders that are inside folders in order to continue to add what JMP calls the children to that folder location.

In this case, with this code, we dynamically build that associative array. This way, when I go to look at the folders that you have access to, to be able to do the upload, this is the code that would end up looking for that information on the start of the script. This way, you can see everything you have access to, so you can pull files from any folder to concatenate and then put them in whatever location your heart chooses.

This is only one small part. This associative array gets built in this fashion. What we end up doing is that from this add-in, you have three essentially options from what I built.

The first option is you have to be able to select a JMP Live folder to concatenate from. This is where, on initiation with the associative array, I pull all the folders you have access to, and this gives you a list of all the folders you have access to. This way, you can pick that folder to concatenate from, and then you can pick a folder to save to, which comes from the same associative array list. The next thing is that you get to select a table to update.

Based off of the information that gets put in here, what ends up happening is that all the files that are located in the Live folder to concatenate from get concatenated and then saved to this folder, that's the Save to Folder, and then it gets saved as that selected table inside of that folder. Those all map to those associative arrays that I just showed you in the previous screen for the code that pulls that information from JMP Live based off of the JMP Live connection that's unique to you.

In order to do all this, this is only part of the brains of the work. The associative array pulls what you have access to, but you have to be able to pull down the information and then put it back up. One of the things that JMP Live has that's really nice is there's a URL that you can actually construct in order to pull based off of that HTTP request. This is an example of what we use to create that URL, which essentially downloads those files locally.

As an example, there's a base URL that we end up using for JMP Live. There's an example here of the HTTPS JMP Live company name. In our case, it's IDEXX. We're allowed to use that to link to our JMP Live. Then from there, there are locations that you have to do that are stagnant. Like the API packages, that's a stagnant part of the URL related to JMP Live.

Then we know we have a Live ID. One of the things that gets pulled from that associative array that I just talked about a little bit ago is the Live ID that links to the folders and then the data tables. This way, this URL can be dynamically constructed based off of that associative array as you build it. Then you have another static piece, data, followed by a JMP Live data table name that you will then pull from this URL, and then download the contents, which is the ".JMPLive/contents".

Again, only part of the magic. The next thing you have to do is be able to replace the JMP Live table. Once you have all your files together in your folder, and you've concatenated them inside of your local JMP instance, you then have to put it someplace. This is the example of the code that we use to replace that JMP Live table.

What happens is that we create a new connection and then based off of that associative array, where you have the information that you selected the table that you would like to upload to, that then gets delivered as new JMP Live content that then gets pushed back up to JMP Live in order to update the data. This way, you can dynamically update based off of your concatenated tables in your folder.

What was my use case? Because this was something that, as we were playing around, it was like, this would be cool to use in our local stuff. One of the things that we were able to use this for was actually stability trending. In this case, here's an example of what the code is that I wrote and what it does. You can see from the beginning, there's the starting window of what this code does, which shows three inputs that I just showed you a couple of slides before. Which is that you have to select the folder. Then the next step is to select the folder you're going to pull data from. In this case, I have stability raw data. Anytime I have technicians run any more data, it gets put in this specific location in this raw data folder location. That's what gets picked for my starting folder to concatenate from.

Following from there, I then know I want to add it to my trending data because I want to continue to concatenate onto that file. That's my next selection, where I select a JMP Live folder to save to. In this case, it becomes my stability trending.

Then, because my stability trending data table only has one, my next selection is the Stability Trending data table. However, this is the one I started. It can be named whatever you want. The associative array will pull it, however, in that location. In this case, I made the table first. In this case, what I've been using so far is that the table has to exist already in JMP Live, but that would be a great feature in the future is to be able… that's a hard thing to say, feature in the future. That would be a great thing to continue to add as a dynamic piece, where you can actually create new data tables that you concatenated in order to save them to locations, because data could change.

What does this look like from the JMP Live side? On the JMP Live side, what you see from the JMP Live side is you see the Starting Folder. You could see my independent time points that people have uploaded. In this case, it's me. Technicians will be able to upload from this location, too, should they have access to be able to put information here. Those are the tables that would get concatenated. You can see below, I have an example in my folder of my stability trending, which is a separate folder from my raw data, and you can see the data table that I'm modifying data from.

What does this look like in the back-end once that actually happens? When you can watch concatenation, magic happens. You can actually physically see JMP Live say the data table has completed its updates when you actually go through and run this code, because what it does is it updates it in real-time. In the case on the left, you can see my starting data table only has timepoints 0 and 1. As you saw on the previous table, there were timepoints that also went up to number 3. Now, after running the code, you can see that there is now a third timepoint that gets concatenated to these tables.

With the conclusion of this and continuing to use this data, I then collaborated with Timothy and said, "Hey, I have this code. You might find it useful, given what you're doing here in this talk." That's when Timothy took it and turned it up to 11.

Thank you, Sage. I had then taken a lot of the work that Sage had done, and I was like, well, it's really nice to have the ability to take a bunch of files that are on JMP Live and concatenate them together into a new table and put it back on JMP Live. In my use case I had where I have data that really only exists locally, I need a good way of taking that and then putting it into JMP Live but also concatenating it with a bunch of data that's already there. This is something that I wanted to share with other people and collaborate with other people, not necessarily at the same location, where we could both be independently adding data to the same data set. I really didn't want people doing a lot of manual manipulation.

I thought, well, what if I had a tool, and I can basically just use this tool to connect to specific tables? Not only that, but I also want to have the ability to run any data ETL. If I want to add new columns, if I want to make summary tables and upload those instead, I really want a way that I can essentially build a JMP workflow script and then essentially attach it to certain tables and certain projects, and then to be able to share that with other people so that we can all be running the same thing, updating the same tables, and then see the results in real-time.

This is showing just the start of a tool window. We'll be doing a live walkthrough here in a few seconds. I just want to go over a little bit more of the code first. In addition, script-wise, to what Sage has done, it's just taking all of that and packaging it together. When we use this, I'm also using a lot of associative arrays. That's how I store and save a lot of the project-specific information and the special scripts.

You may have seen in the previous window, we have a script edit window. The magic to make that work is really just using the Eval Parse function, and I wrap that inside of an otherwise empty function. That's how we enable users to just paste scripts into that window. Then, when they run it, everything works perfectly for parsing tables and then joining them and concatenating them with what we have on JMP Live. We're going to get out of this presentation for a second just to show some quick examples.

First, here I have a JMP Live space, and then here I have my tool that's running. This is what the tool looks like when you first open it up. At the very top pane here, we have this settings section. These are settings that you can save, and then any time you run the add-in, whatever settings you have here will become populated. It's the name of your JMP Live connection, the base URL for your JMP Live, and then whether or not you want a list of tables to pre-populate into the tool. That list of tables is this section down here.

This is how we decide what tables are ones that I want to track and associate scripts with. This is an instance where I actually took some of what Sage had done as far as being able to add new tables, which we'll do that in a second. One of the important things here for collaboration is the ability to load project lists. If you share project lists with people, they can then load these in.

If I just go ahead, and I just load a project list that I have pre-created, we'll see I have this project list, and it contains two tables. It has this Not my Cat table, and it has this Weekly Weather Data. You'll notice down in the script portion that these are a little bit different size. This Not my Cat table, I have just a basic scripting here that, when this is run, it prompts the user to select a file to open, and then it returns that file after opening it. Then, in the case of what this means with the best of this is that once this file is returned, this is the file that will be concatenated with the listed table here.

In this other example, not only is the file open, but I'm also adding a column which is calculating the mean of two other columns. An example of this running, I can go ahead, and I can find this Weekly Weather Data table that I have here. This is quite a large table already. I'm at 124 rows and going across various weeks. I think this is Weeks 1 through 10. I can go scroll through the table.

When I run this tool, it's going to prompt me to select a table. I happen to have a folder with a bunch of these tables here, and I can take the new table, I click the Open button, and then we wait a second, you'll see some tables pop up and then disappear, and then you'll notice in my JMP Live that this data has been updated, and I can reload the table, and now I'm at 393 rows.

Backing up a bit, if I wanted to add another table here, if you'll notice in JMP Live, I have a second table here called Class Measurements, and I have a report that goes along with it. I'm going to click on the report. I can see here in this report that I'm tracking some measurement, and it's going over three timepoints for a bunch of different people. I don't have that here in my list yet, but I can add it. Using the same script that Sage developed to search through JMP Live or what you have access to, we get a very similar window pop-up here. I can go into here, I can find my example folder, and then I can select my Class Measurement table and add it to this project list.

When I add this to the project list, you'll see I can still switch between these other items, and it also pre-populates the very basic script into the window that allows me to simply run this and select a file to concatenate. I'm going to do that right now. I see I go to timepoint 3 in this table, and I happen to have the file for timepoint 4. When I click the Open button, the file pops up, and then it goes away, and we can see in JMP Live that this report is regenerating and timepoint 4 has been added to it. Anyone who has access to this table could use this tool to update these JMP Live tables with the data that they have that's for this project, as long as I give them access to it in JMP Live.

We can go backwards. We can see that, yes, the Class Measurements table has also been updated as it is linked to that report, and we'll see that the timepoint 4 data is there. I have a fifth timepoint data. I may as well run this again to add it to it as well. Once again, a tail pops up, it goes away, and then an updated version of this table is ready. I can hit RELOAD, and I can see that the extra data has been added to the table. I can go backwards again, and I can see that my chart has also updated.

This is something that's been very exciting for us as far as the collaboration on certain projects and just getting people to have a little bit more standard workflow, being able to share scripts, especially for people that are still learning workflow builder and how to automate some of these flows. It can be really nice to just give them some tools like this, just to really avoid certain issues on some of these multi-site projects that we sometimes have.