C63 Tablet Data Preparation*Do Not Share The SQLite File With Customers*
Apart from the tablet dissolution data, it is made up of artificial data that will immediately spoil the illusion that this is a realistic case study.
What do you like about this case study?
- It is a Science and Engineering situation.
- It is the data from one of our best demo case studies for exploratory data analysis: Tablet Production.
- You can show database querying in Windows or MacOS without needing to worry about complications of ODBC drivers. (We use a SQLite database that can be opened directly from File > Open without any drivers or database connection routines.)
- You can show a full automated workflow for data import, joining, cleaning, and analysis that can be built with no scripting required.
With this demo you can show customers how they can automate the process of data preparation from multiple sources with no scripting required.With this demo you can show customers how they can automate the process of data preparation from multiple sources with no scripting required.With this demo you can show customers how they can automate the process of data preparation from multiple sources with no scripting required.
Who is it for?
- It should be most relevant for anyone in manufacturing in a science or engineering industry.
- Anyone from science and engineering that has the challenge of accessing data from external sources, including databases.
How should I use it?
(See screenshots below)
- You can use the SQLite file to show how easily you can query a database to pull the Dissolution data for the Tablet case study. Say that this is a QC database or LIMS database.
- You can then import the Process Data (using Excel Import Wizard) and the Supplier data (Text import). Say that the process data is manually recorded in Excel by the operators and the supplier data is from a Supply Chain database that can only export data as .txt.
- You can join the Process and Supplier data with the Dissolution data using JMP Query Builder. Say that the interface works in exactly the same way as the database Query Builder.
- You can show how the above steps have produced a JSL script that will repeat the whole process and import and join the latest version of the data from the 3 sources.
- You can show errors in some of the data using Distribution and how to recode and capture the script to append this to the query as Post Query Script.
- You can build a dashboard on the complete dataset and save as an addin that will include the data import and join process - the whole data and analysis flow can be automated without any scripting! You could show this as the last-thing-first then walk through the steps to achieve this.
What platforms can I show?
- Query Builder
- Excel Import Wizard
- Text import
- JMP Query Builder
- Distribution
- Recode
- Dashboard Builder
Open the .SQLite and then configure the joins like this in Query Builder.Open the .SQLite and then configure the joins like this in Query Builder.Open the .SQLite and then configure the joins like this in Query Builder.
Set up the query for Dissolution data like this.Set up the query for Dissolution data like this.Set up the query for Dissolution data like this.
Use Excel Import Wizard to fix problems with the process data on import.Use Excel Import Wizard to fix problems with the process data on import.Use Excel Import Wizard to fix problems with the process data on import.
Use the text import preview with Tab as delimiter to import the Supplier data.Use the text import preview with Tab as delimiter to import the Supplier data.Use the text import preview with Tab as delimiter to import the Supplier data.
Use JMP Query Builder to join the 3 data sources on API Lot number like this.Use JMP Query Builder to join the 3 data sources on API Lot number like this.Use JMP Query Builder to join the 3 data sources on API Lot number like this.
These are the data entry problems that you can fix with Recode.These are the data entry problems that you can fix with Recode.These are the data entry problems that you can fix with Recode.
An example of a dashboard addin that I have created from the integrated data.An example of a dashboard addin that I have created from the integrated data.An example of a dashboard addin that I have created from the integrated data.
Hi @mia_stephens ,
Yes, absolutely. I first created the demo before JMP Live was created so the end-point of the workflow was a dashboard addin for sharing. But I have also presented HTML dashboards and JMP Live reports using this demo.
Re: Tablet Data PreparationCould this be used for the end-to-end workflow, from import to publish?
Re: Tablet Data PreparationThanks @benfrancis .
I think the difference that you are seeing might be due to connecting with the database in 2 different ways:
1. ODBC driver using File > Database > Query Builder (Your JMP 15 example).
2. Directly opening the SQLite using File > Open (Your JMP 16 example).
Both are possible in JMP 15 and 16, I believe.
I only found out about method 2 when I had to demo this from a Mac and I didn't have the ODBC driver.
I would now strongly recommend method 2 for demo purposes. It is much easier than having to mess around with ODBC drivers. And there is no point in showing ODBC database connection to a customer as that will be entirely dependent on how their IT have set up their database. The important part to show is the Query Builder, which you need with both methods.
Re: Tablet Data PreparationJust to note that from JMP 15 to 16 the scripting has changed with the SQLlite connection. In my source script...
JMP 15
Connection(
"ODBC:DSN=SQLite3 Datasource;Database=C:\Users\SUKBFR\OneDrive - SAS\Demos\Tablet Production\QCdata.sqlite;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;"
)
JMP 16
Connection(
"SQLite:Database=/C:/Users/SUKBFR/OneDrive - SAS/Demos/Tablet Production/QCdata.sqlite"
)
