Choose Language Hide Translation Bar

Strategies and Examples for Data Acquisition From Distributed and Complex Sources (2021-EU-30MP-758)

Georg Raming, Senior Manager, Siltronic AG

 

In the course of daily work, users often need to analyze the same or similar data from distributed sources. Because these users are rarely involved in defining the IT infrastructure, it is often the case that the needed data is located across a variety of different platforms (databases, fileservers, etc.). Users are then forced to spend a lot of time querying and combining the data to get it in a form appropriate for analysis. JMP offers several possibilities to query data from different sources and to connect them afterwards. In this presentation, some examples of workflows are shown that can be used to efficiently get data into table(s) and effectively meet the requirements of analytical users. Methods used to accomplish these tasks include Query Builder, SQL, JSL, JMP add-ins and Virtual Join.

 

 

Auto-generated transcript...

 


Speaker

Transcript
Georg Raming (Siltronic AG) Hello everybody.
  Today I want to talk about strategies and examples for data acquisition from distributed and complex sources.
  My name is Georg Raming, my job is process development of ??? grown single crystals at Siltronic. I have some experience with statistical evaluation and process
  and product data and statistical education. I'm responsible also for JMP software at Siltronic and training activities for a few hundred users.
  Siltronic is one of the world's leading manufacturers of highly specialized hyper pure silicon wafers.
  Some technical hints. So I'm working here with data tables instead of database in this presentation, but the concepts shown here are originally used for getting data from relational databases via ODBC connection.
  And the JMP Query Builder is working in a similar way for JMP data tables and for database tables.
  What is the target of today's presentation? It is to establish some ways for getting data from database in an easy and efficient way into your JMP or into a JMP table.
  Let's first talk about the building blocks.
  So one may be a JMP data table.
  So if you have generated a data table from a database query, it may look like this. So I have taken here the famous sample table from JMP, Big Class, and I have queried the data via JMP Query Builder. I deleted all the script that comes with the table.
  And you will find
  for this data table if you query it by JMP Query Builder, several scripts and table variable.
  So inside that variable, you can see the SQL. So the definition of the data table is drawn from the table or database.
  And you have these scripts, a source script that simply
  gets another copy of the original data table like this. And here you can see the original scripts are also in there, but there is again these scripts from the JMP Query Builder.
  There is also a modify query script that lets you edit this query on the JMP data table and also run it.
  And there is a query for update from database. So if you change the data in your data table
  or there is new data in the data table, you can simply update the data by pressing this button.
  Like this. Here also the scripts have come again from the sample table.
  Okay, so the next building block is JMP Query Builder.
  So once you have one or several tables at database, you want to make a query on like here, I took Big Class and Big Class Families from JMP sample table
  directory and use the JMP Query Builder like
  here on the tables. JMP Query Builder.
  JMP sees these open tables
  and there is also one in the primary field, and I can add a secondary table like this, and JMP automatically then creates a join between both tables. So how they are joined. And you can edit this join
  like this.
  You'll see the details here.
  And then you can go on building the query
  like this. You have both tables here available.
  Here you also can edit again the join
  And maybe you want to add all
  columns,
  like this.
  And maybe you want to put a filter,
  like this.
  And run the query. Then you again
  get this result table with the scripts from the
  sample tables we do not need here.
  I delete them. And these are the scripts, the scripts and variable written by JMP Query Builder
  like I used it before. And again, here you can
  edit this query. The script for the query is saved in this result table here, like we have defined it here before.
  So this is how the JMP Query Builder works.
  And as mentioned before, it works the same way on database tables as it works here on the JMP sample tables.
  The next building...I also put here some scripts and you always can switch from visual Query Builder
  to a custom SQL
  query. So this works here. Another red triangle menu.
  Convert to custom SQL, and what you have defined here visually,
  you can define here in a text SQL query.
  You can run it.
  And when looking here at this query or pressing Modify Query button,
  you will see the text query. So this is only one way,
  converting from visual query to custom SQL query.
  So let me just tidy
  some tables. Okay.
  Next building block is to get data from database and you need an ODBC connection to that data sources.
  And usually the ODBC data sources and drivers are set up by your IT administration. So under Windows, you can find
  these connections under the ODBC data source administrator.
  And please keep in mind that the bit-ness of the database sources should fit to JMP. So in this case I have 64-bit JMP application and therefore I need 64-bit drivers
  And the user sources, you can set up yourself.
  And system DSNs are set up by the administrator, and also drivers for different types of databases are set by the administrator.
  In case of troubleshooting, you may use this tab for tracing
  where the query went and what may be the problem.
  And a tip from my side would be to check whether the connection works properly from these data sources. So if you press Configure and the data source administrator, you can hear for a certain source test the connection after you have provided your credentials.
  And if there is everything okay, so the system tells you that
  the connection succeeded.
  It's not up to Windows that the query doesn't work.
  And also other details, you can find in the JMP manual in the documentation. For example Using JMP, chapter three, you can find it here in the
  Help menu
  and the JMP documentation library. And there it is well-documented how to use all these tools.
  Okay, so there are then in scripting and JSL, in the scripting language, three ways to script these SQL database queries. The first one is a new SQL query, as we have already used before. So it's located here in the JMP menu under Table, JMP Query Builder for JMP tables.
  Or for the database, you can find it here under database Query Builder.
  And then New SQL Query is the most powerful command of all. So, it generates a new query object, you can save itself as JMP query file. It can generate a data table directly and provides very well origin of the data, as we have seen before with source modify and update script.
  Another command in JSL to query data from database is the open database command. You will find documentation of all these commands also in the scripting index. Like here,
  if I type
  open
  database,
  you can find here how it works for all commands.
  And a third way to connect to database and put some queries is database connection.
  And it needs three steps. The first one is create a database connection. The second is put your queries, one or several, to the database. And to finalize, you need to close the database connection.
  And finally, if you wrote some nice scripts.
  You should make it accessible to you, or maybe to colleagues, and there are at least two ways. So one is to put it in an add-in so that you can provide it to others easily also. And the other possibility is to put it in a custom menu, like I did here.
  And you can find it
  in the menu, then here like this. There are two add-ins installed in my system.
  And here is my personal
  custom menu.
  So let's...we have finished the building blocks. Let's go to the examples.
  And the first example is to use a table script to save table layout and the query.
  So it may look like this. If you have a data table that comes
  from database and you put some scripts in it for nice graph also,
  you may want to use it every day. And there is a nice possibility here
  to say, copy table script without data.
  And this script you can then use.
  I would like to make a new script. Sorry.
  And paste it into the script window.
  And here comes that new table by simply pressing the Run Script button.
  And it comes as ???, without data. And you can save this script, for example, in your menu and to get the data, it may be a huge amount of data. You can simply press the Update from Database button and the table gets filled with all the data from maybe database or somewhere else.
  So this is a nice possibility
  to simply use the script
  to get the data from database.
  The second example
  is to use a query from inside a table.
  So like this. If you have a data table and you want to query some additional data, dependent on the content of your current table.
  So I will show it.
  I delete some rows.
  And with these three names, I want to go to a different table to fetch some data.
  And it is done by this script.
  And here you can see from this table, I got the names and made a query
  to query data from another table from Big Class Families.
  And how does it work?
  You can find it in this script.
  It is quite short. Here, the names are taken from the first table query as defined, like here.
  The names are substituted into the query.
  And here the query is put to the table Big Class Families.
  Okay, I need to close this table too.
  This one is a custom query script with graphical user interface. So if you have, for example, a large data table on the database and often need small amounts of it,
  you can, of course, pull all the data, but maybe it's more flexible and efficient to get well-filtered data. So it works like this.
  one table for filtering the data and one table with all the columns of the big data table. And here we can filter graphically. Let's say female and age, so I took only two columns to filter,
  like this, 12 and 13 years old.
  And I may want to have all columns or remove just one column.
  I query it
  and here get the proper result with these restrictions. And you can, as you can see here, and the source script or in the SQL,
  the restrictions are here
  from the graphical user interface defined.
  And how does
  The scripting work works? It is a little
  more complex, of course. So there is the filter query.
  The columns query.
  Here is a script, a function that converts the
  data filter conditions into an SQL condition.
  This is the GUI part, graphical user interface.
  And here, finally, the graphical user interface results are evaluated and put into this custom script.
  The next example is a two step query. So let's assume you have to query first some batch IDs.
  And to take these IDs into another query, maybe to another data table to get additional data,
  you can do it like this. So here I took the Big Class data table as filter. So I filtered only the male
  and
  equal or less than 13 year old pupil.
  And took these names to query on Big Class Families, all rows, like you can see here.
  And
  both tables are connected via a virtual join, like you can see here.
  So
  this table refers via the name to the other table to the filter table. And I can use
  the columns of the filter table here in this table as if they were
  in this table.
  So this is a nice way to have several tables and use the content in one table.
  And the last example is
  how to run two or several queries in background in parallel. So I have no example here. It is discussed in the JMP Community. And if you're interested, you can have a look at.
  So now I'm finished with my examples and the journal and presentation material scripts are available online.
  Thanks to the Community for the wonderful discussions, and thanks to the JMP developers for building and maintaining this great piece of software. And finally, thank you for your attention.

Presenter

Files

Need help?