Subscribe Bookmark
erichill

Staff

Joined:

Oct 1, 2013

Coming in JMP 12: Query Builder

We hear frequently from JMP users that they spend a great deal of time preparing their data before they can start analyzing it. We added Query Builder to JMP 12 to streamline the process of getting data from relational databases into JMP and ready for analysis. JMP has had the Open Table feature for opening tables from relational databases for many releases, but Open Table has some notable limitations: it does not help with joins or filtering, and the queries it does produce can be difficult to share.

Query Builder has been designed to help you build much more complex database queries without having to become an expert in SQL syntax. It can automatically join database tables based on primary and foreign key relationships and help you interactively filter the data. Also, with a few clicks, you can turn the filter into a prompt that can help others use the query you built to get the data they need.

Figure 1

Selecting tables to include in a query

 

Figure 2

Query Builder example with filters

 

While building your query, you can specify the modeling types and formats that variables should have once they are in JMP, eliminating the need to do this manually or write a JSL script to do it. You can even include Recode or other cleanup scripts to be run after data is returned by the query. The result is a single, easily shareable file that delivers analysis-ready data to you or your colleagues in one step.

Query Builder also has a “Custom SQL” mode. If you get to a point building a query interactively where you need something that Query Builder does not provide, you can switch into “Custom SQL” mode and modify the SQL directly to get exactly what you need.

Look for more details on how to use Query Builder in future posts here when JMP 12 is released.

Editor's note: This post is part of a series of previews of JMP 12 written by the people who develop the software.

3 Comments
Community Member

Walter Paczkowski wrote:

This is an exciting addition. The use of SQL is something I stressed at a presentation I gave to the JMP developers this past summer. A question I have is -- maybe a dumb one : What constitutes a database to open with the Query Builder? I would like to be able to open a JMP data table and also use it with an already open data table. Can I? Or it is restricted to special databases/tables that have to be, but are not already, opened?

Thanks,

Walt

Staff

Eric Hill wrote:

Hey, Walt - A "database" for Query Builder is either a relational database (Oracle, SQL Server, DB2, Teradata, PostgreSQL, MySQL, others) that you can connect to via ODBC, or a SAS server. Query Builder cannot be used on JMP tables. We are, however, aware of the desire for doing more SQL-like things with JMP tables, though such features did not make it into JMP 12. If there are specific things that Query Builder does that you'd most like to also be able to do with JMP tables, especially once you've had a chance to use Query Builder, you can respond here or perhaps start a discussion in the

Thanks!

Eric

Community Member

Michael Clayton wrote:

One small step for mankind.

Only about 25% of the data I have to retrieve and analysis from clients is in relational DB.

The rest are flat file repositories with many header rows of text strings generic to the array of data below. Re-structuring that data intelligently for each and every repository type is like creating a DB loader for that data...Python job plus domain knowledge and engineering beta testers.

However, since many engineers at my clients need that same data, these repositories are set up with all the data in ascii csv format. However their are many rows of header information that has to be reformatted as context column data for JMP.

Same issue for IMPORTING EXCEL files...lots of array context data in header rows in text strings.

So profound knowledge of SQL, Python, and domain naming conventions (data dictionary, glossary of technical terms, etc) all wrapped up in a customized robotic GUI?? Someday.