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.
Selecting tables to include in a query
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.
There are no labels assigned to this post.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.