Accessing, Querying and Importing External Databases and Data

[#1]

Databases are structured data stored either on a file or on a server. They usually consist of multiple tables that are linked together in someway to efficiently store, access, and retrieve data.

See how to:

• Connect to an ODBC-compliant database (CTRL+SHIFT+D on Windows - see all JMP Win and Mac shortcuts)
• Share database connections using JMP 18 Data Connectors option
• Share multiple connections from one JMP Add-In file
• Import Single Tables
  • Import Views (pre-joined tables in a database) for tricky database structures where joins can be complex
• Import data from multiple tables
• Understand database structure and how small tables are linked together by Keys
• Use JMP Query Builder for simple joins of tables stored in multiple database tables
  • Automatically join using primary and foreign keys in the database
  • Manually join
• Query the schema/data structure to see overall structure
• Modify queries
  • Adjust existing queries
  • Rerun query to capture in a new data table
  • Update from database to add any new data from database into existing table
• Make queries dynamic
  • Modify filters via GUI when the query runs
  • Use JMP scripting to build dynamic and easy tools for common tasks
• Automate the daily process checks from database

Questions answered by Chris Kirchberg @Chris_Kirchberg  and Jed Campbell @Jed_Campbell  at the live webinar:

Q: The connections Add-In will work assuming the users have the correct drivers for those ODBC connections, correct? JMP doesn't share drivers does it?

A: Yes, the Add-In will work with the correct drivers supplied by your and your organization.  JMP does not ship ODBC drivers.

Q: What if my database isn't set up with keys and joins?  How do I set those up on my own?

A: You can manually specify which column(s) should be the unique identifier(s) to join the tables together. This may help you.

Q: Using Query Builder, can you see a count of the total records, not just a preview of the 1,000 rows in preview mode.

A: If it is really simple, you can use the Count to see number of values for a particular factor.  You may be able to make a change in Preferences to preview the total number of records viewed for a more complex query, but the downside might be that, if the number is too high, it could slow down or crash your production database system.  See below:

Some questions answered at a previous live webinar by JMP Developer Eric Hill  @Eric_Hill :

Q: When in the initial Query Builder window where you can join tables, can you join between secondary tables?

A: Absolutely.  Any table in the query can be joined to any other. 

Q: Can I join two tables if key/column is numeric in one table and character in the other?

A: For some databases, join columns with mismatched types are coerced to be the same type, but others not.  If you have this situation, the  best thing to do is drop into custom SQL and cast the string to a number.  Though this StackOverflow article suggests that SQL will just do the conversions on your behalf.

Q: When one chooses to aggregate using Average, how is that done? For example, in the Movie Rental example, when we take average age, is that averaging across unique customers or looking at all the records within the movie information that defines a group? The concern is that if a customer is renting multiple times the same movie, they might get counted multiple times and thus the sample size would be inflated. I suspect this might be dependent on the DB type.

A: The database is going to average across whatever rows are in each group.  The best thing to do is to make the query you want without aggregating, making sure if you only want each customer to be present one time that that’s what you’ve got, and then apply the aggregation.  It may require custom SQL or doing one query that gets the rows you want and then a JMP query against the result that does the aggregation.

Resources:

• Documentation on importing data from a database.