Query Builder: The New JMP® 12 Tool for Getting Your SQL Data Into JMP®

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).

Section 1: Making the Connection

Initiate Query Builder using File > Database > Query Builder from the menu:

You can also launch it from the first page of JMP Starter:

Lastly, on Windows, you can also find a Query Builder tool on the Home Window toolbar:

After launching Query Builder, you will be greeted with Select Database Connection dialog box:

The Select Database Connection dialog box is a subset of the Open Table window. Query Builder and Open Table use the same ODBC connections and in fact share connections, so any connections you make in either place will show up in both.

Click New Connection… to connect to your database using an existing DSN or to create a new DSN. You will be prompted to supply any required credentials:

After connecting, JMP will retrieve schemas from the connection to help you make sure this is the connection that you want:

You can also see the tables available in the selected schema. Click Next to continue to the next step.

New in JMP 12.2: Notice the two Search boxes in the Schemas – Tables panel. These are new in the JMP 12.1 update, and they do filtering-as-you-type in the Schemas and Tables lists. We know many of you have hundreds of schemas and hundreds of tables in a given schema, so the new Search boxes can help you narrow the list down so you can find what you’re looking for.

Section 2: Selecting Tables for the Query

Figure 1: Selecting Tables for the Query

When you click Next, the Select Tables dialog box will appear. In the Select Tables dialog, you will select which tables you want to include in your query (see Figure 1 above). Select tables in the Available Tables list that you want to include in the query and add them to the Primary and Secondary table lists at the upper right.

The Primary table in a query is normally the table from which all rows should be retrieved, regardless of whether they have a matching value in some other table. This is your fact table, in data warehousing terms. There can only be one primary table in a query.
Secondary tables are normally look-up tables. By default, rows from secondary tables are only included in the query when the value of a column in the primary table matches the value of a specified column in the secondary table. These are your dimension tables, in data warehousing terms. You can add as many secondary tables to the query as you want.

The panel in the lower right shows information about the selected table. The Columns tab displays information about each column in the table, including whether it is a key of some kind:

New in JMP 12.2: Note the Search Box in the Available Tables panel, which is new in JMP 12.1. Use it to find the table you are looking for when your schemas contain a lot of tables.

The Table Snapshot panel shows a snapshot of the data so that you can make sure the table has the information you need:

Normally, when building a query, you have a question or questions in mind that you are hoping to answer, and those questions drive decisions about which tables your query needs to include. The data we are exploring is from a fictitious DVD rental company. The questions I would like to answer are:

Which movie genres are bringing in the most revenue?
Which actors are bringing in the most revenue?

The rental table has a record for each DVD rental event over the life of this company, so that is going to be the primary table for this query. I can see from the Columns panel that rental has columns with foreign key relationships to the inventory and customer tables:

Figure 2: Foreign Keys

A foreign key relationship (also known as a foreign key constraint) means that the values in the column of one table are required to be found in the primary key column of another table. From Figure 2, we can see that values from the inventory_id column of the rentals table must exist in the primary key column of the inventory table, values from the customer_id column of the rentals table must exist in the primary key column of the customer table, and values from the staff_id column of the rentals table just exist in the primary key column of the staff table. For this query, I am looking for more information about the movie rented, rather than the customer of the staff member involved in the rental, so I will add the inventory table to the query as a secondary table.

Looking at the Columns panel for the inventory table, I can further see a foreign key relationship with the film table:

Figure 3: inventory table details

So the film table goes into the query also. Looking at the details of the film table, I see a rental_rate column, which should be what I need for computing revenue, but I don’t see anything about the category of the film or the actors in the film:

Figure 4: Columns in the film table

However, looking at the Available Tables list, I see a film_actor table and a film_category table, which appear to map a film’s ID to its actors and category, as well as actor and category tables that have more information about the actors and categories. So I am going to want these tables in the query as well. However, if I simply add those 4 tables, something appears to go wrong:

Figure 5: Automatic join unsuccessful

The “stop sign” icons with the “X” in the middle signify that JMP was unable to automatically join those two tables, so I will have to set up the joins manually. If you find that JMP’s automatic join function frequently does not do what you need it to do, you can turn it off from the red triangle next to Select Tables for Query.

If I select public.film_actor (t14) and click the Edit Join button ( ), the Edit Condition dialog box comes up, allowing me to configure the join. Initially, the dialog box looks like this:

Figure 6: Editing the join condition

since no join condition exists yet. I want to join film_actor.film_id to inventory.film_id. To accomplish this, I simply select the inventory table from the Left Column list and select film_id in both lists:

Figure 7: Editing the join condition

Similarly, I can set the join condition for public.film_category to be inventory.film_id = film_category.film_id. After doing so, things look better:

Figure 8: Joins between tables are valid

All of the secondary tables are showing the left outer join Venn Diagram icon. Using the join we just made between film_actor and inventory as an example, a left outer join means that all rows from the inventory table, which is the left table in this case, will be included in the query whether there is a match in the film_actor table or not, but only rows from the film_actor table that match a row in the inventory table will be included.

By the way, the t1, t2, t3, etc., in parentheses after the table names are the table aliases that JMP assigns to the tables as you add them to the query. Table aliases make it easier to generate correct SQL and make the generated SQL more readable. You can change the table alias for a table by right-clicking on the table in the Primary or Secondary table list and selecting Change Alias. Table aliases cannot contain spaces or special characters.

If you need something other than left outer join, you can change the join type using the check boxes on the Edit Join dialog. The join type and Venn Diagram icon will change to reflect the state of the checkboxes.

Figure 9: Changing the join type

To check whether you have the joins set up correctly, you can click the Preview Join button. That will run the query and bring up a snapshot of the first few hundred rows of data so that you can check that things look right.

Now that we have all the tables in the query that we need, we can click Build Query to bring up Query Builder itself, where we can specify which columns to include in the query, filter criteria to use, how the resulting data should be ordered, and much more.

Section 3: Building the Query

When the Query Builder window comes up initially for this query, it looks like this:

The Query Name box holds the name of the query, which defaults to the name of the primary table. The Query Name is the name assigned to the JMP data table that results from running the query, and is also the default file name when saving the query to a file. The Data Source shows you the name of the data source that the query is using.
The Tables box shows you the tables that are currently in the query. To add or remove tables or change joins, click the Change… button.
The Available Columns list shows all of the columns from all of the tables in the query. Select columns from this list and drag them to the Included Columns, Filters, or Order By panels. Or, if you prefer, there are right-click options to accomplish the same thing.
New in JMP 12.1: The Available Columns panel now has a Search box in case you need to find a particular column in a long list. As with the other Search boxes, it filters as-you-type.
The Included Columns panel shows a grid of all columns that your query includes. Use the Included Columns grid to set things like the format and modeling type that data for the column should have after being imported into JMP. You can also specify aggregations along with Group By for columns if you want your data summarized before it comes into JMP.
There is also a Sample tab in this panel for generating random samples of data on the server, for those databases that support sampling.
The Query Preview tab in this area automatically updates to show a snapshot of the data your query will return when you run it. The SQL tab shows the SQL that Query Builder is generating based on current selections. The Post-Query Script tab allows you to specify a JSL script that will be run after your query completes. The Query Status tab shows the status of queries that are running in the background and allows you to cancel them.
The Filters panel lets you specify criteria for filtering the data (creating the WHERE clause) and turn the filter into a prompt that runs each time you run the query.
The Order By panel lets you specify the column or columns to use for ordering the returned rows (ORDER BY clause).

Adding Columns

Every query needs some columns. Add columns to the query using any of these methods:

Click the Add All button to add all available columns from all tables in the query
Select specific columns from the Available Columns list and click Add or drag them over.
Double-click on a column in the Available Columns list.

For this query, I will add film_id, title, and rental_rate from the film table, category_id and name from the category table, and actor_id, first_name, and last_name from the actor table.

Figure 10: After initially adding columns

Using the JMP Name, modeling type and Format columns of the Included Columns, I can control what the name, modeling type and format of each column will be after the data is imported into JMP.

Figure 11: After adjusting column details

For the purposes of this analysis, I would also prefer that the actor names were concatenated in a single column rather than split into separate first_name and last_name columns. To achieve that, I can add a computed column by selecting Add Computed Column from the red triangle menu on the Available Columns panel.

This will add a column named Calc1 to the Available Columns list, but I can right-click it and use Rename Column to rename it to ActorName, add it to the query and remove first_name and last_name. Here is how the preview of the query looks after doing so:

Figure 12: Query Preview with computed column

(Those Actor Name values may look a little strange, but those are in fact the correct names from the database. The actor names appear to have been generated by randomly pairing a first name with a last name.)

Saving the Query

At this point, I have enough time invested in this query that I would like to save it so that I do not lose my work. Simply click the Save button at the bottom-right of the Query Builder window to save the query. You will be prompted to supply a file name, with the text from the Query Name box supplied as the default. I will name the query film_category_actor. A message will be displayed at the bottom-left of the Query Builder window confirming that the query was saved:

Saving a query creates a file with a .jmpquery extension. Back on the Query Builder window, you’ll notice that the Query Name has been changed to match the file name, so it is now film_category_actor.

If you need to create another query that is similar to the one you have open, but you want to preserve the original, you can use the Save As… button to save a copy with a different name.

Running the Query

I can also run the query at this point to create a JMP data table containing all the rows from the database. Click the Run Query button at the bottom of the Query Builder window to run the query. By default, JMP runs queries in the background so that you can continue working while your queries run. Use the Query Status tab in the bottom-center section of the window to see the status of queries that are still in process from this Query Builder window, or use View > Running Queries to see the status of all queries that are currently running in the background. This particular query takes just a second or two, returning 88,020 rows:

Figure 13: Results of query in JMP Data Table

Notice that the resulting JMP data table has 3 scripts and a SQL table variable defined:

The SQL table variable contains the SQL that was submitted to the database to perform the query.
The Source script will run the query again.
The Modify Query script will bring the Query Builder window back up with the query loaded in it.
The Update from Database script will run the query again and update this JMP data table in-place with the results.

Answering the Questions

At this point, I am close to being able to answer the original questions I was seeking answers to, but I have a decision to make: Do I want to have the database summarize the results, or do I want to bring the raw data into JMP and have JMP do the work? If the raw data is not too large to fit in memory and does not take a prohibitively long time to import, then importing the raw data into JMP and doing the analysis there as many advantages – JMP is much more capable of analyzing data than a relational database.

For the task at hand, it is a simple matter of using the Distribution platform on Category Name and Actor Name, using Rental Rate as the Freq variable. If I order the resulting histograms by Count Ascending, I get a result that answers both of our questions:

Figure 14: Using the Distribution Platform on the query result

If, however, bringing all the raw data in was prohibitive in size or elapsed time, I can as an alternative use the Aggregation column in the Included Columns panel to have the database summarize the data. For that, however, I will need to answer the two questions one at a time.

To answer the question of Which movie genres are bringing in the most revenue?, I can remove the columns having to do with actor information from the query and ask the database to sum Rental Rate grouping by Category Name, and then I can order the result by the summed Rental Rate. Here are the steps:

Click the Aggregation column for Rental Rate and select Sum.

Notice that Group By becomes checked for all variables that do not have a specified aggregation. Once any column in a query specifies an aggregation, all columns in the query that do not specify an aggregation are required to be Group By columns.
Right-click on the Variable Name column for Rental Rate and select Order By.
Select SUM(t4.rental_rate) in the Order By panel and click the downward-pointing arrow button (Descending).

The result looks like this:

Similarly, taking the category columns back out and putting the actor columns back in, I can answer the question of Which actors are bringing in the most revenue?:

Filtering

Let’s go back a moment to the state where our query was retrieving all of the raw data and we were using the Distribution platform to answer our revenue questions. One option we have when doing the analysis in JMP is that we can request the script for the JMP analysis:

I can then paste that script into the Post-Query Script panel of Query Builder:

Figure 15: Adding a post-query JSL script

That script will now run each time I run the query.

Now, I would like to refine my questions a bit. Rather than look at category and actor revenue over for all rentals in the database, I may want to look at it based on the country where the movie was rented, the store from which it was rented, or over a certain date range.

The rental table has rental_date, and customer_id fields, so I’m close to having what I need. We saw earlier that customer_id is a foreign key reference to the customer table. From the customer table, we can get the country that the customer is from and the store from which the DVD was rented.

If I click the Change… button in the Tables panel of Query Builder, I can go back and add more tables to the query. To get country information, I will need to add the customer, address, city, and country tables to the query. Fortunately, with the key relationships that the administrator of this database has set up, all of the tables auto-join successfully.

After adding the necessary additional tables, back in Query Builder, I can add the columns I need to the query: rental_date, store_id, and country. I have also set friendly JMP Names and corrected the modeling type of store_id:

Figure 16: After adding Rental Date, Store ID, and Country columns

First, let’s filter on the customer’s country. I can right-click on the t7.country variable in the Included Columns panel and select Filter By. After doing so, a filter for country appears in the Filters panel:

Figure 17: Country filter

At this point, I can simply scroll down through the list of countries and check the ones I am interested in, and then run the query, and only rows from those countries will be included.

Similarly, I can add filters for store_id and rental_date, after which the Filters panel looks like this:

When I add the rental_date filter, since it is a continuous variable, I get a Simple Comparison filter by default. What I would like instead is a Range filter. Using the red triangle menu next to t1.rental_date, I can change the filter type to Range:

Now the filter for t1.rental_date looks like:

allowing me to specify a date range. So, if I want to find all rentals from the second half of 2005, I can do:

Figure 18: Date Range Filter

All of the filter types in Query Builder have a way for you to effectively disable them, allowing you to add several filters to a query even though not all of them may be active for a given query. For categorical filters showing a list of values, selecting the (All) item at the top disables the filter. For filters requesting numeric values, setting the value to missing (.) disables the filter (or part of the filter). So if I want all rentals from January 1, 2006 forward, I can do this:

Now the Filters panel for my query looks like Figure 19 below.

Figure 19: Using Missing to Inactivate Filter

Figure 20: Filter with two OR groups

With the filter selections shown in Figure 19, running the query now will return rows where the customer’s country is either France or Germany, and the DVD was rented at store 2 on or after January 1, 2006. By default multiple filters are AND’d together, so a row must match all filters to appear in the result. If you need an OR filter, click the OR button, which will create another filter group, and the two filter groups will be OR’d together. For example, the filter shown in Figure 20 above returns rentals where the category is Drama or the movie title contains “Bucket”.

Prompting Filters

While it is nice to be able to specify filter criteria in Query Builder, you may also want to create a query to be used by others that don’t really need to be presented with the entire Query Builder user interface, but you want them to be able to enter different filter criteria each time they run the query. The simplest way to accomplish this is to select All Prompt on Run from the red triangle menu next to Filters:

That turns all of the filters in the query into prompting filters. If you only want some of the filters to be prompting and not others, you can turn prompting on and off for each individual filter using its red triangle menu.

After setting All Prompt On Run, when I run this query, I am presented with a prompt to set the filter values the way I want:

The default settings for the prompt will be whatever was selected in Query Builder (or in the saved query file if the query is run directly from the file) before the query was run.

You can also customize the labels used in the prompt. For example, suppose instead of “Country:” I wanted the prompt to say ”Customer country:”. Back in Query Builder, using the red triangle menu for the t7.country filter, select Configure Prompt. That brings up a dialog box allowing you to change the label for the prompt and also change the table that supplies the category levels for the prompt:

Sampling

If your database supports random sampling, the Sample tab in the top center section of Query Builder will show the sampling options that your database supports. I’ve been using PostgreSQL for the examples in this paper, which supports simple random sampling of a specified number of rows:

SQL Server and Oracle support different sampling options, so the Sample tab will reflect the different options:

Figure 21: SQL Server sampling options

Figure 22: Oracle sampling options

The sampling is performed by the database, before the data is downloaded to JMP, so this is a good way to limit the number of rows retrieved from queries that would otherwise retrieve a prohibitively large number of rows.

Writing Your Own SQL

We recognize that, as many features as Query Builder has now or may have in the future, there are times when you may need a query that does things that Query Builder does not directly support. Also, you many have already written and painstakingly debugged some SQL that performs exactly the query that you need JMP to perform. Query Builder has two options for supplying your own SQL for a query:

Custom Filter Expression – If Query Builder is doing what you need as far as selecting tables and specifying the columns to include, but you just want to have a custom SQL expression included as part of the filter, you can select Add Custom Expression from the Filters red triangle menu:

Figure 23: Adding a custom filter expression

Figure 24: The resulting filter

Enter whatever SQL you want into the Custom Expression box, as long as it is valid SQL for the query.
If a Custom Expression filter is set to be a prompting filter, the custom expression will appear in the filter prompt dialog and be editable.

Convert the entire query to a Custom SQL query – If you just want to write your own SQL for an entire query, then, after launching Query Builder and specifying the data connection, you can use the red triangle menu at the top left of Query Builder next to the Query Name field and select Convert to Custom SQL:

Figure 25: Convert to Custom SQL

The Query Builder window will switch to custom SQL mode, giving you a large area to enter your own SQL. If you had started building the query interactively, the Custom SQL box will be initialized with the SQL generated based on the selections you have made so far (see Figure 26 below).

Figure 26: Custom SQL query

You can edit the SQL as needed, or paste in SQL from another source, and then you can click the Update button to update the Query Preview. Some caveats:

You cannot do prompted filtering with a custom SQL query.
Once you convert a query to custom SQL and edit the SQL, if you revert the query back to interactive, you will lose any customizations you have made to the SQL. Query Builder does not attempt to interpret the SQL.

Section 4: Managing Background Queries

Query Builder runs queries in the background by default, and it is also possible from JMP Scripting Language (JSL) to run queries in the background (see Section 6). JMP provides two ways for you to view the list of currently running background queries and stop running queries if necessary.

Query Status Panel

In the bottom middle section of Query Builder, there is a Query Status tab. Any background queries that were launched from that Query Builder window will appear in that tab:

If you want to stop a running query, simply select it in the list and click the Stop button. If some rows have been retrieved, JMP will open a data table containing whatever rows have already been retrieved. The name of the data table will reflect that the result is partial.

Runninq Queries Window

From the View menu on the Home Window or other JMP window that includes a menu, select Running Queries. This will open a window that looks the same as the Query Status tab of Query Builder, but the Running Queries window will contain all queries that are currently running in the background, regardless of how they were invoked (a Query Builder window or JMP Scripting Language):

Here again, select a query and click Stop to stop it from running, opening any partial result.

Section 5: Sharing Queries

In the introduction, it was mentioned that Open Table created scripts that were difficult to share, because the database password is included in the connection string. Query Builder works differently. If you save a .jmpquery file for a query whose connection required a password, and you open that file in a text editor, you will see something like this:

You can see that the user name (UID) is present in the connection string, but then you see PWD=%_PWD_%. “%_PWD_%” is not the password; it is a placeholder put there by Query Builder. When Query Builder sees that placeholder, it knows that a password was required originally, so if this query is opened at a time when the user opening it does not already have a connection to the Postgres dvdrental DSN, Query Builder will know to prompt the user for the password.

And what about the DSN itself? What if you send this .jmpquery file to a JMP user who does not have a DSN named Postgres dvdrental defined on their machine? Open Table is unable to proceed in such a scenario, but Query Builder will inform the user of this situation and give them a chance to connect to a different DSN (that presumably connects to the same database) or create a new one.

Because of the potential of different users having different DSN’s defined on their machines, some organizations have moved to DSN-less connections. Query Builder cannot currently convert a DSN-based connection string to a DSN-less one, but if you change a connection string in the script for a query so that instead of specifying a DSN, it specifies a DRIVER and SERVER, Query Builder will propagate this DSN-less connection in any scripts or .jmpquery files that it generates.

Section 6: Running Queries from JMP Scripting Language (JSL)

Queries built by Query Builder can also be run from JMP Scripting Language (JSL) scripts. The best option is to save the query as a .jmpquery file, open it from JSL, and run it. Suppose, for example, that you have a query saved in the c:\users\public\temp folder named pg_rentals.jmpquery. Here are some things you can do with that query file from JSL:

Open a .jmpquery file into Query Builder:

open( "c:\users\public\temp\pg_rentals.jmpquery" );

Get the query as a scriptable object without opening it in Query Builder:

query = open( "c:\users\public\temp\pg_rentals.jmpquery", Private );

Open the query in Query Builder for editing:

query << Modify;

Run the query in the foreground or background, depending on the user's preference setting:

query << Run();

Run the query in the foreground. The resulting data table will open in JMP when the query finishes. dtResult will be a reference to the result data table.

dtResult = query << Run Foreground();

Run the query in the foreground, but do not open the resulting data table in JMP; keep it private. dtResult will be a reference to the result data table.

dtResult = query << Run Foreground( Private );

Run the query in the background. The resulting data table will open in JMP when the query finishes. Run Background cannot return a reference to the result data table because the query is simply launched in the background. Examples following this one show how to get a reference to the result data table when running queries in the background.

query << Run Background();

Run the query in the background. If the query succeeds, invoke the Distribution platform on the result data table (queryResult is a special JSL variable within an On Run Complete script that refers to the result data table). If the query is canceled by the user or has an error, write a message to the JMP Log.

query << Run Background(

On Run Complete(

queryResult << Distribution( Nominal Distribution( Column( :title ) ) );

),

On Run Canceled( Write( "\!NThe query was canceled!" ) ),

On Error( Write( "\!NError running query!" ); )

);

Run the query in the background. Upon successful completion of the query, invoke the MyFunc function. The query result data table is always passed as the first parameter to the function.

::MyFunc = Function( {dt}, {Default Local},

Write( "\!nNumber of Rentals: ", NRows(dt) );

dt << Distribution( Nominal Distribution( Column( :title ) ) );

);

query << Run Background( On Run Complete( ::MyFunc ) );

If opening a query from a separate .jmpquery file in your JSL script is not satisfactory, you can get the raw script for creating a query scriptable object from the red triangle menu at the top left of the Query Builder window:

For example, invoking Save Run Script to Script Window for a simple query gives me:

New SQL Query returns a SQL Query scriptable object just like Open does when opening a .jmpquery file and can be used in all of the same ways.

What’s New in JMP 12.1?

Search

Many people we talk to have mountains of data in hundreds of schemas, tables, and columns in their databases. In the JMP 12.1 version of Query Builder, we have added Search boxes to the lists of schemas, tables and columns to help you find the ones you are looking for quickly:

All of these Search boxes update as you type, and you have the option of using regular expressions to refine your search.

Add Related Tables

In the Select Tables for Query dialog box, if a table that you have added to the query has foreign key references to other tables, you can add all of those related tables to the query by clicking the Add Related Tables button.

Open as Template

Frequently, you need to build a query that is very similar to another query that you have built and saved to a .jmpquery file. You can always open the previously saved query, make changes to it, and use Save As to save it to a different .jmpquery file, but if you were to accidently hit Save by mistake, your original query would be overwritten. The Open as Template feature exists to prevent that scenario. Open as Template is available from the Home Window context menu for .jmpquery files on both Mac and Windows:

When you open a .jmpquery as a template, you are really opening a copy of the original query that has no association with the .jmpquery file on disk. If you have opened a query as a template, made changes, and accidentally hit the Save button, you will be prompted to provide a file in which to save the query.

You can also open a .jmpquery file as a template from JMP Scripting Language (JSL):

open( "C:\Users\Public\Temp\rentals.jmpquery", As Template );

Edit a Computed Column Formula

In JMP 12.0, you could add a computed column to your query that would be computed on the database server, with the resulting values returned in the query. However, if you needed to edit the formula of a computed column you had created previously, it was not possible to do that. Instead, you had to delete the computed column and add a new one, possibly requiring you to recreate a complex formula. In JMP 12.1, you now have the option to edit a computed column. Simply right-click the column in Available Columns and select Edit Formula:

Bug Fixes

As you would expect, we have fixed a number of bugs in Query Builder in JMP 12.1, some that were reported by customers. If you encounter a problem using Query Builder, please report the problem via SAS Technical Support so that we can address it.

Conclusion

Query Builder is a new feature in JMP 12 (updated in JMP 12.1) 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 (eric.hill@jmp.com) on what works well and what doesn’t work for you.

Introduction