Eric Hill, Software Developer, JMP
Presented at JMP Discovery Summit Europe 2015
Summary
JMP puts a vast array of analytical tools at your fingertips, but those tools do you no good if you can’t get the data you need to analyze into JMP in the way that you need it. JMP has had support for importing data from relational databases into JMP for quite some time via File > Database > Open Table. Open Table is fine as far as it goes, but it does have some notable limitations:
- Most data in relational databases is stored in a normalized way, requiring joins to get all of the data you need, but Open Table does not help you with joins; you have to write the SQL yourself.
- While Open Table provides the Formula Editor for building a WHERE clause, it does not, for example, show you the distinct values of a categorical variable to help you filter on it.
- It can be difficult to securely share your queries with others, because Open Table stores your password in the connection string of the JSL scripts that it generates.
- Open Table does not help you with post-processing steps that are required once data has been imported, such as data clean-up, modeling types and formatting.
Query Builder was designed from the ground up to address the limitations of Open Table, helping you build multi-table queries with prompted interactive filtering that can be securely shared with co-workers.
In this paper, I will use Query Builder with a freely available PostgreSQL sample database called dellstore2. PostgreSQL is an open-source database that can be freely downloaded from here: http://www.postgresql.org/download/
The dellstore2 sample database can be found here: http://wiki.postgresql.org/wiki/Sample_Databases
The dellstore2 sample database is also available for other database platforms (MySQL, Oracle and SQL Server).
Query Builder is a new feature in JMP 12 that helps you build highly customized multi-table SQL queries against your relational databases. The goal is to make it easier for you to get your data into JMP in analysis-ready form more quickly than was possible before and to make it easier to share your queries securely. We hope you will try it out and send us feedback to Eric Hill EricHill (eric.hill@jmp.com) on what works well and what doesn’t work for you.
