Data Connectors in JMP

Daniel_Valente

INTERNAL USE ONLY: This document is internal use only at this time.

We will post something to the general community in the future that you and your customers can refer to.

 

Missing Data Connectors:

Please leave a comment on this document if you know of any customers that are successfully accessing data from sources not included in this list. Conversely, if you know of any roadblocks your customers have encountered, please also note them. One of us will work with you to create a defect (if appropriate) that spells out as completely as possible what is desired. If the customer has already submitted a track to Tech Support, we will need the Sirius track as well. We need at a minimum:

• Customer contact information.
• # of licenses, % of licenses that want the data access.
• Whether or not the requesting party is a JMP champion.
• Sirius support track or issues first noted source (e.g. I talked to this customer while at a JMP day or at a conference).

 

General Roadmap Thoughts:

Most of our effort right now is on ODBC, with sensitivity to Hadoop based systems.  Adding custom connectors to every data source in the big data landscape​ is not in the plan. OLEDB has been deprecated by Microsoft and will not be considered.  Working with OLAP is also out of scope. We have very limited resources, and custom connector efforts are low on the priority list. To get things in the queue, collect information from your customers (with as much detail as possible) and we will get the requests filed as suggestion defects (if appropriate). When a certain request hits a threshold where potentially hundreds or hopefully thousands of seats could benefit from the addition of a feature, then we can start considering it.

 

Current Data Connectors in JMP:

Your data comes in many forms. Fortunately for you, JMP is hungry for data. A running list of all the data sources you can connect to from JMP.

File > Open

 

You can import many file formats into JMP and save them as data tables. JMP opens many files by default. The file formats which JMP does not support by default require specific Open Database Connectivity (ODBC) drivers. If the vendor has an ODBC 3.0 compliant driver available, JMP can most likely access it.

 

Format	Extension	Support	How	Additional Information
Comma-separated values	(.csv)	Default	File > Open command	You can open text files with the extensions .txt, .csv, and .tsv, and the text is converted to a data table. Files with the .dat extension that consist of text are also supported.
files that consist of text	(.dat)	Default	File > Open command	You can open text files with the extensions .txt, .csv, and .tsv, and the text is converted to a data table. Files with the .dat extension that consist of text are also supported.
ESRI shapefiles	(.shp)	Default	File > Open command
Flow Cytometry versions 2.0 and 3.0	(.fcs)	Default	File > Open command
Hierarchical Data Format, Version 5	(.h5)	Default	File > Open command	Available In JMP 13.
HTML	(.htm, .html)	Default	File > Open command
JSON	(.json)	Default	File > Open command
MATLAB	(.m, .M)	Default	File > Open command	.mat/.MAT not supported.
Microsoft Excel 1997 through the current version	(.xls, .xlsx on Macintosh)	Default	File > Open command
Microsoft Excel 2007 through the current version	(*.xlsx, *.xlsm on Windows)	Default	File > Open command
Minitab Portable Worksheet	(.mtp)	Default	File > Open command
Plain text	(.txt)	Default	File > Open command	You can open text files with the extensions .txt, .csv, and .tsv, and the text is converted to a data table. Files with the .dat extension that consist of text are also supported.
R	(.r)	Default	File > Open command
SAS transport	(.xpt, .stx)	Default	File > Open command	Another option is connecting to a SAS server by selecting File > SAS > Browse Data.
SAS versions 7 through 9 on Macintosh	(.sas7bdat)	Default	File > Open command	Another option is connecting to a SAS server by selecting File > SAS > Browse Data.
SAS versions 7 through 9 on Windows	(.sas7bdat, .sas7bxat)	Default	File > Open command	Another option is connecting to a SAS server by selecting File > SAS > Browse Data.
SPSS files	(.sav)	Default	File > Open command	JMP opens SPSS files as data tables and maintains several SPSS features
Tab-separated	(.tsv)	Default	File > Open command
Teradata database	(.trd)	Default	File > Open command
Triple-S	(.sss, .xml)1	Default	File > Open command	1On Macintosh, rename the .xml file as an .sss file before you open it. The Macintosh operating system does not recognize the .xml file as a Triple-S file.
xBase data files	(.dbf)	Default	File > Open command
Import Microsoft Excel Files		Excel Wizard		Microsoft Excel files open in the Excel Import Wizard by default. The wizard shows a preview of the data. You can then modify the settings before importing the data.
Database (dBASE)	(.ndx, .mdx)	Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver   .dbf files do not require an ODBC driver
Microsoft Access Database	(.mdb)	Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Open SAS Data Sets		Query Builder	File > SAS > SAS Query Builder	SAS Query Builder is the preferred method for selecting and importing data from a SAS server. You can preview the data before importing it into a data table. You can also save the queries to modify and run later or to reference in a JSL script.
Cloudera Impala	Hadoop	Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Cloudera Hive	Hadoop	Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Hortonworks Impala	Hadoop	Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Hortonworks Hive	Hadoop	Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
MapR for Apache Hadoop (v2.x or later)			File > Database > Query Builder
Amazon Redshift		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Amazon Aurora		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Amazon MariaDB		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Amazon Elastic MapReduce		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Microsoft SQL Server		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
PostgreSQL		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Oracle		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Oracle MySQL		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
SQLite		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Google Analytics		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Salesforce		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
Firebird (v2.14 or later)		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver
OLEDB		Query Builder	File > Database > Query Builder	V3+ compliant ODBC driver   1​With free Snapshot OLEDB.jmpaddin

Open SAS Data Sets with SAS Query Builder

 

 

Build SQL Queries in Query Builder

Query Builder is the preferred method for selecting and importing data from an SQL database without writing SQL statements. You can preview the data before importing it into a data table. Share your queries so that other users can customize and run the queries.

 

 

Connect to a Database

Your operating system provides an interface for JMP to communicate with databases using ODBC data sources. Create and configure data sources with operating system software. For example, on Windows 7, use Control Panel > System and Security > Administrative Tools > Data Sources (ODBC); on the Macintosh, use Applications > Utilities > ODBC Manager.

 

Import Triple-S Survey Data

Triple-S (SSS) is a specification for survey data. The survey data is stored in a pair of files: .xml or .sss, and a .csv, .dat, or .asc file. The .xml or .sss file defines the variables and describes the survey. The accompanying .csv, .dat, or .asc file contains the data gathered from each respondent.

 

Import HDF5 Data (in JMP 13)

Hierarchical Data Format, Version 5 (HDF5) is a portable file format for storing data. An HDF5 file consists of groups and datasets. When you import the file, JMP opens a group to present the names of the inner datasets. For example, if the file contains data for a group of musical recordings with songs and artists tables, JMP prompts you to open either “songs” or “artists”. If the file does not contain a group, JMP prompts you to open a single table.

 

Import Data from R / MATLAB

JMP provides a scripting interface to R and MATLAB. See the Scripting Guide for details.

 

Other Data Sources we know we can get to:

Hadoop

• Impala (Apache -- http://impala.io) -- "Apache Impala is the open source, native analytic database for Apache Hadoop. Impala is shipped by Cloudera, MapR, Oracle, and Amazon." -- ODBC driver through the Query Builder.
• Apache Hive "Apache Hive is a data warehouse infrastructure built on top of Hadoop for providing data summarization, query, and analysis." -- ODBC driver, accessed through the query builder.

Amazon

• Amazon Redshift -- "Amazon Redshift is a hosted data warehouse product, which is part of the larger cloud computing platform Amazon Web Services."
• Amazon Aurora (connect using the MySQL ODBC driver)
• Amazon MariaDB (connect using the MySQL ODBC driver)

 

Competitive Info: Data Connectors

(source: http://technologyadvice.com/blog/information-technology/tableau-vs-spotfire-business-intelligence-for-the-non-it-guru/)

 

Data visualization and analytics applications are built on the idea of aggregating and interpreting data from outside sources, so they’re only useful insomuch as they integrate with data sources and other business systems (like CRM or marketing automation), servers, and databases.

Each vendor provides a set of native data connectors, which means it can seamlessly pull data from those sources without custom configuration or coding.

Here’s what you’ll get from Spotfire (DLV note: this may be a slightly outdated list), but Tibco lists out their Data Connectors here: http://support.spotfire.com/sr_spotfire_dataconnectors.asp

And here is another list: https://community.tibco.com/wiki/tibco-spotfirer-database-drivers

A few of Spotfire’s connectors for the Platform edition aren’t specified on this list (which represents the Desktop edition): Apache Spark SQL, Cisco Information Server, and OData.

And here’s what you’ll get from Tableau:

As you can see, both solutions provide a good mix of big data, relational database, and cloud integrations. Any databases or applications not listed would presumably require API integration and/or middleware to sync.

Tableau does offer a handful of native connectors that Spotfire does not: DataStax, EXASOL, Firebird, Google Analytics and BigQuery, Microsoft Azure, and SAP Sybase.

Here is a pretty good listing of Tableau drivers: http://www.tableau.com/support/drivers

Daniel_Valente

HI stanley.koprowski1​ -- I made good progress on filling this out. Can you please post the public question on the discussion forum about where does your data list and have you been able to successfully access it in JMP? Also we'd like to hear about where you have tried to get access to data and failed.