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.