Welcome to Discovery.
My name is Brian Corcoran.
I'm a JMP development manager.
My team develop JMP Live,
and I'm here to talk about automatic refresh of data in JMP Live 17.
If you've seen any of my previous Discovery talks,
you'll know that I frequently talk about data acquisition and repeatability.
In fact, in a previous Discovery,
I demonstrated how to take a variety of scripts for data acquisition,
piece them together to produce a dashboard, and published it to JMP Live.
And then I used a Windows Task Scheduler
to schedule a refresh of that report every day.
It was useful, but a lot of customers said,
"Gee, it's overly complicated,
and it should be nice if this just happened on the JMP Live system
without my intervention."
So that's what we're going to talk about today.
Before I do that, though,
I think it would be worth having an overview of what JMP Live is
for those that may not know about it.
JMP Live is a separate product from JMP.
It is a web- based collaboration site.
JMP desktop users publish reports and data from JMP to JMP Live.
JMP Live users can interact with their reports, explore the data,
and do some further filtering of data if they so desire.
Users on JMP Live can interact with that site
without having a JMP license or JMP on their desktop.
If they want to and they have JMP desktop,
they can download those reports and work on them in the JMP desktop session
for further analysis if they so desire.
In JMP 17,
we worked very hard to make replacement of data and reports easier.
Because in JMP Live 15 and 16,
a lot of this had to be done on the desktop.
In order to do this,
we had to make data kind of a first- class citizen the way we refer to it here,
or an equal of the report.
Before, when you published a report on JMP Live,
the data went along for the ride and you really couldn't see it.
Now, if you want, you can actually publish the data separately.
Any reports that use that data
will be refreshed on the JMP Live server when it gets new data.
We've also provided a JMP Live Data V iewer
so that you don't have to ever go back to the desktop if you don't want to
to see what's in the data.
All of this provides of the foundation for our refreshable data.
That means that the contents of the data can be refreshed on the server
without you having to do anything on your JMP desktop session.
Any reports that use that data will be refreshed.
And we provided a repeatable way with a task scheduler on JMP Live
that's hopefully very simple to use
so that you can tell JMP Live when to refresh the data.
Refreshable data typically comes from a database
that you access through ODBC or a website with a rest endpoint.
And the examples of that are the World Bank or the Census Bureau.
JMP Live can store credentials that you need to access that data
securely within the JMP Live database.
So those are the basics.
Let's go ahead and actually do a demo
so we can see what I'm talking about here.
Let's go ahead and bring up JMP.
This is JMP Pro, but all of this would work with regular JMP as well.
I'm going to open a data set that ships with the product called Financial.
All this is, is Fortune 500 companies, names have been withheld,
but with sales and profitability and, number of employees.
Let's go ahead and make a report with this.
I want to just do sales and number of employees.
And then I'm going to apply a data filter
for the type of industry that we're talking about here.
And you'll see within the JMP report
that I can kind of cycle between the industries to look at the individuals
and see what their profitability is, number of employees, things like that.
Let's go ahead and just publish this to JMP Live.
I'm going to use an internal JMP Live server we have here.
I've created a folder for this information already.
You could create a new folder if you so desired.
I'm going to go ahead and publish this.
Let's go ahead and look at JMP Live.
And I'm just going to use a Chrome session.
And we'll refresh this.
And I should have a new report here.
There's my information.
It's going to load that.
There's a lot going on in this particular server.
So it might take a second, but there it is.
We'll cycle through the various industries,
and there it is.
Let's suppose that I've decided...
I wish I had put a fit line and mean on this.
I can go back to JMP and go ahead and do that.
What I'm going to demonstrate here
is the separation between report and data.
Let's suppose though, that I also got information, in the meantime,
that company number one is actually a $19 billion company.
But I'm waiting on verification on that,
and I don't want that outlier to show up in my report.
What I can do is I can go ahead and publish this to JMP Live,
and I'm going to replace an existing report.
And I'm going to select my financial report.
But I'm going to say rather than updating JMP Live data table,
I'm going to use the existing one up there.
Let's go ahead and look at JMP Live.
It's reloading our report.
There's our fit lines.
But we haven't gotten our outlier company yet.
Still 9.8 billion in sales.
Let's go ahead and close this window.
Let's shut this report down.
Let's suppose that I do find out that my data is accurate for drugs,
drug company number one,
and it's a $19 billion company.
I can just select to publish just the data now.
And I'm going to update the data,
and I'm going to go ahead and replace my financial post.
And we'll look here
and it's going to regenerate with my new data.
And sure enough, I have my outlier company here.
If I so desired, I could go in and look at the data here
and see that there's my $19 billion company as well.
So there's our Data V iewer in JMP Live 17.
Let's go ahead and shut this information down.
That just shows you how you can selectively update reports and data.
For my next part,
I'm going to get into a refresh script
and I'm going to kind of put a sheemless plug- in
for a new facility called the OSI Pi Data Server Import.
OSI Pi is something called the historian database.
And historian databases will collect information from
usually a lot of industrial processes, machines, meters, things like that.
And a lot of times it's real- time information
collected in the database so you can analyze it later at your leisure.
And JMP 17 has a new facility to do that.
So if I go into database and import from OSI Pi server,
I can connect to the Pi server within SaaS.
And we have some simulated data for a data center for like cloud computing.
And in those you're going to find a variety of servers and things like that.
Here we're looking at the server rack number one
for how much power is it consuming.
And we can look at that.
I'm going to go back
and I'm going to collect three days of data at a certain point in time.
And I'm going to say let's go up to 10,000 points.
Let's go ahead and import that.
So we got 6300 points.
And you'll notice something here called the source script.
Let's take a look at that.
The source script provides JMP scripting language to recreate the data fetch.
And this is going to be important for us in our refresh script.
It contains the information on how to connect to the server,
what days we're sampling, how many points we want to do, things like that.
Let's go ahead and create a simple control chart, run chart with this data.
I'm going to go ahead and make this a label.
Let's go ahead and put that information in here.
And there is our chart.
Let's go ahead and publish this.
Publish it new.
And you know what,
I think I'm going to create a new folder for this,
and I'm going to put it in there.
We'll go ahead and publish that information.
Now let's go up and take a look at that.
There is our new report.
I want to go ahead and look in my space where this report resides.
There's our Pi data.
So let's go ahead.
Here's a files tab, and you'll see that we have a report and a data table.
So let's go ahead and look at that report.
That's kind of what we expect.
And if we hover over the points, there's our values and when they were collected.
But now let's look at the table,
and we'll go into settings here
and we'll see that that very source script we were just talking about
got pushed up to the server along with our report.
Now there's something called a refresh script .
The source script is kind of provided as a starting point
for what you'll do with your refresh script.
The refresh script is a script that's going to run to provide data
for our update.
So whatever you put in here is what's going to be updated for your report.
Let's go ahead and make this refreshable.
And let's copy our source script and edit our refresh script.
I'm going to paste that in there.
First, we got to make sure this has an ending semicolon in it.
But this has the information to connect to the Pi server from our server process.
The Pi Server, by default can get a whole lot of data tables at once
because you might have like five different power supplies.
So you get five different tables, and you can return that as a data table list.
That's how the Pi server works.
So we have to know that in order to do this right,
because rule number one,
and the, really, only rule you need to remember for refresh scripts is
it needs to return a data table as its last item,
last thing it does.
Let's go ahead and we're going to return our data table list here
from our Pi call.
And we're going to take the first item off of that list because
w e really only have one data table for our Atlanta Data Center power meter.
So we'll take the first item off of that list.
And since this is setting up a data table with the last action in the script,
we should be good for our rule.
The last thing we're going to do is return a data table.
But while we're at it, let's go ahead and get one more days worth of data.
We'll get four days' worth going back ways.
So there is all we have to do, I believe, for our refresh script.
Let's go ahead and try it out manually by saying refresh data.
It's saying refreshing and says updated a few seconds ago.
Let's go ahead and go back and look at our report.
There it is.
And you'll see it's a bit different than what we had on the desktop
because we went and got an additional day's worth of data.
So you can see it's matching up to this last day.
And then we have some additional data.
So there is a refresh script from our OSI Pi source.
So that's our first example.
And if we wanted to, we could view that.
The history pane contains information on how well things went.
You can see I asked for an on- demand data refresh here.
It took only two seconds.
If we are going to look at the details, we'd say it just returned a data table.
This isn't going to return the contents of the JMP log.
JMP is running in the background to fetch the data for you on the server.
So there's your first example.
Let's try another one.
I'm going to clean this up.
Another common way that we get data is from a traditional database
like Oracle or SQL Server.
So let me do an example in which we use SQL Server.
I'm going to go ahead and I'm going to bring up Query Builder
and open a new connection here.
I have some stock data
and for this particular case it's for Apple Computer.
But let's imagine that we have a table full of stock quotes to get updated
at the close of the market, like at 04:15 PM every day.
And we have some background process updating those.
Let's go ahead, and we're going to get our quotes that we have right now.
I'm going to build a very simple query, bring in all our data.
This is going to provide the Apple quotes from the beginning of the year.
I'm going to select Date as a label.
We'll go ahead and do another run chart with quotes.
We can go and hover over points,
see what the closing price was on any given day.
Stock market has gone down, but Apple's bouncing back up recently.
Let's go ahead and change the name of this.
Let's go ahead and publish that to JMP Live now.
I'm going to create a folder for this too.
Maybe we have a whole bunch of stock quotes in here.
Let's go ahead and rename this too,
and we'll go ahead and publish that.
Now let's go ahead and we'll just shut this down.
Now let's look up here,
and there are stock quotes as we expected,
and if we look at our table,
there is our source script.
Let's go ahead and take a look at that.
Let's make our data refreshable again like we did with our Pi server.
And let's copy that and edit.
You'll see here that our query consists of a connection section.
That's how we're going to reach the database,
in this case SQL Server, using an ODBC driver.
This was present on the source script on the desktop as well,
but the password has been replaced by a placeholder, as have the user ID,
both for security reasons.
And because the credentials on the server
may be different than the credentials you're going to use on the desktop.
Let's go ahead and we're going to complete our refresh script.
We need an ending semicolon here.
And the new SQL query just returns a single table
and we're going to assign that to a data table variable in our script.
And as an insurance mechanism,
I like putting an empty reference to the table
at the very end of our refresh script
to make sure the last thing we do is return a data table.
So what about the password and user ID?
Well, we're going to talk about that.
Let's save our script out.
We can assign credentials and we go down here,
and I have some I created before,
but let's create a new one just to show you how you would do this.
It's called the SQL Server for JMP T est .
And what I'm going to do is
I'm just going to put the credentials for the server's access of the database.
So maybe these are administrative credentials.
Maybe they're test credentials that you use up here.
And I'm going to say,
let's go ahead and assign this set of credentials for our refresh script.
What will happen is the user ID will be substituted into this field
within our refresh script where you see UID
and the password will be replaced with password.
And it will be fetched in a secure manner from an encrypted database
only when that fetch needs to be made.
So nobody will ever see your credentials.
Let's go ahead and try to refresh this.
We had a failure.
Let's take a look at that and see what happened.
Here's our JMP log.
And if we read through this, it's a real long explanation to say
it didn't quite understand the connection credentials.
Let's take a look at that and see what might be going on.
So this is really something important to know about
when you're doing refresh scripts on JMP Live.
JMP Live runs a variety of JMP sessions
to help you fetch data and recalculate analyses in the background,
they're all hidden from you,
but they run on Windows Server along with JMP Live.
You must have the ODBC driver that Windows Server needs
to access that database installed on the server.
It turns out that the Windows ODBC driver has a different name than my Mac driver.
Remember I'm using a MacBook and it was actual ODBC SQL Server driver.
My SQL Server driver from Microsoft is just called SQL Server,
so I need to modify that driver name here in my connection script.
Let's go ahead and save that.
Now let's go ahead and try refresh again.
And it says we updated a few seconds ago.
Okay, that's great, except it's the same data we already had.
So it's not too exciting.
So now let's simulate what would happen if the data got updated.
We're going to create a refresh schedule.
Let's go ahead and say...
Normally we probably do this around this time of day
because the market will be closed and we can get new data.
So I'm going to go ahead and put in like 04:18 PM.
And I'm going to say
we're not going to do this on Saturdays and Sundays
because the market is not open.
So let's go ahead and save that.
So it'll start calculating when it's going to first run.
It's going to run in a minute or two.
So let's go back to JMP,
and I have a script that's going to, behind the scenes,
update the stock market data.
Like what normally might happen from a data feed elsewhere.
And we're going to put a stock value of 200 in there for today's data.
Make sure that ran JMP now.
And now we're just going to wait on our JMP Live session.
It's going to run in a few seconds.
And while I do this,
I just mentioned that your scheduling allows you to both set termination dates.
If you want it to only run for like a month and stop,
you can get greater granularity
so that it runs like every five minutes, every hour, things like that.
By default, it's going to run once a day.
And like I showed you, you can select which days of the week it runs.
Says it's going to run again in a day.
Let's go ahead and see what our history says.
Our scheduled data refresh succeeded.
Let's go ahead and look at our report.
Okay. And there is our value of 200.
Stock really rocketed up, maybe it's time to sell it.
But the important thing to know is that our refresh schedule worked.
And from now on, now that we set this up,
it's going to run five days a week, every day,
and we'll automatically get our report generated
so that when we come in in the morning,
we're going to see our updated quotes
and be able to make decisions
on our reports and the latest information that we have.
So that's updatable, refreshable data
in its simplest form on JMP Live and JMP Live 17.
I hope you found this interesting.
I really appreciate you attending this talk.
Have a good day.