Spotfire® and ODBC data sources

Introduction

Spotfire® provides native data connectors for a wide range of data sources. Spotfire connectors are tailored to each individual data source to provide for example a complete mapping between database functions and Spotfire custom expressions functions. Many of the connectors are also certified, for example by SAP and Cloudera. For a complete list of data sources with native Spotfire connector support, see this article..

If you want to analyze data in a data source that Spotfire currently doesn't provide a native connector for, you can use Spotfire's generic ODBC support. The major differences in functionality between connectors and generic ODBC are listed in the table below.

Connecting Spotfire to a generic ODBC data source

Before you can connect to your ODBC data source in Spotfire, there are a few prerequisites to keep in mind.

• You must have the corresponding data source driver for the ODBC data source installed on your computer. 
• You must have added and configured the data source as a DSN in the ODBC Data Source Administrator on your computer. 

When you connect to a generic ODBC data source, you don't specify the data source information in Spotfire. Instead, you select a DSN, which you have set up in the ODBC Data Source Administrator on your computer. There, you enter all the required information, such as server name and port number, when you add the DSN for the data source. In Spotfire, you only need to select the DSN and enter your database credentials. 

Note that if you want to open an analysis with a generic ODCB data source in a web client, you must make the same configurations on the computer running the web client server. 

To add data from your ODBC data source in Spotfire:

1. In Spotfire Analyst, select File > Add Data Tables, and then click Add > Database. 
2. In the Open Database dialog, select the data source type ODBC Data Provider, and click Configure....
3. In the Configure Data Source Connection dialog, click System or user data source and, in the drop-down menu, select the DSN of your data source.
4. Enter your database username and password and click OK. In the Open Database dialog, click OK again.
5. In the Specify Tables and Columns dialog, specify what data you want to include by selecting the checkboxes of the tables and columns in the list. Give your data source a name and click OK. 
6. In the Add Data Tables dialog, click OK to load the data from your ODBC data source into your Spotfire analysis. 

For more information on connecting to other types of data sources, see the Spotfire Analyst User's Guide.

Example: Connecting to 1010Data using ODBC

Spotfire currently doesn't include native support for 1010Data but you can use the generic ODBC support described above to retrieve data.

Driver download and installation

Begin with downloading the 1010Data OBDC here.

ODBC Documentation is available here.

If you encounter any issues with the ODBC driver support requests are submitted here.

Install the driver on the Windows client you are running Spotfire Analyst on. Don't forget to install the driver on the Web Player nodes you plan to share your analysis on as well.

Specify proxy information and if you will use stored procedures.

Configuring the DSN

1010data is a stateful database, normally mapping a single client connection to a single resource on the server side. In production, to use a single set of credentials for multiple connections you leverage what is called a "SAM pool". In this tutorial, we don't need to get into SAM. Instead in our ODBC DSN Configuration panel on the Connection tab we switch the "kill" drop-down from "yes/no" (probably "no" right now) to "possess". This will allow you to share a single server-side session along multiple client-side connections. Please note that parallel queries will queue. That said, it's a simple way for you to simulate a production SAM pool.

Details on selecting what tables and columns to bring into Spotfire

In Spotfire, go to File > Add Data Tables... > Add menu > Database... > ODBC Data Provider > Configure...

Select your DSN like below:

If you have stored the username and password in the DSN you don't need to enter them in Spotfire's dialog below.

NOTE: If you need to make changes to the selection of tables and columns or modify the SQL you will need to start over by going to go to File > Add Data Tables... > Add menu > Database... > ODBC Data Provider > Configure...

Click OK twice.

You should now be able to specify tables and columns like in the image below.

As you begin to select tables and columns the corresponding SQL statement will be generated. You will notice that Spotfire uses the table.* idiom which 1010Data currently does not support. Instead, modify the generated SQL or test with a query like:

 SELECT * FROM pub.demo.baseball.allstars

Give the data source a name. This will be the default name of the final data table in Spotfire as well.

The SQL statement above loads all rows from the pub.demo.baseball.allstars table which could take a while.

SELECT * FROM pub.demo.baseball.allstars was used in the example below. 3707 rows and 4 columns were loaded in about 10 minutes.

Try to customize your SQL using for example a WHERE clause, for example like:

 SELECT * FROM pub.demo.baseball.allstars WHERE year >= 1933 AND year <= 1960

The query above limited data between 1933 and 1960 and loaded 1423 rows and 4 columns into Spotfire in about 3.5 minutes.

The data source can be seen in Spotfire's SourceView:

Editing the ODBC data source

To edit the SQL in your ODBC data source go to Edit > Data Table Properties. Select the final Spotfire data table. Choose Refresh Data - With Prompt from the menu to the right in the dialogue. This will launch the Specify Tables and Columns dialogue.

You can also go to Edit > Data Table Properties and tick the check box Prompt for new settings before loading.

Whenever you need to edit the data source go to the Source View and click the data source's two reload arrows.

Replacing an ODBC data source while keeping data transformations intact

A common enhancement request is an ability to replace Spotfire's ODBC data sources while keeping data transformations and calculations based on the data table intact. One use case for this is when moving between a test and a production database. This is not yet possible without the workaround below but please vote for the idea here.

The good news is that there is an easy-to-use work around to the problem above. Use the following steps:

1. Connect to 1010Data, define an ODBC data source, define your original Spotfire data table and load the corresponding rows.
2. Create a copy of the original Spotfire data table from File > Add Data Tables... > Add menu > select the existing 1010Data table under the From Current Analysis section.
3. Visualize data and create data transformations based on the copy of the original Spotfire data table.
4. Whenever the ODBC data source needs to be replaced, define a new ODBC data source, define a new original Spotfire data table and load the corresponding rows.
5. Use File > Replace Data Table... to replace the previous ODBC data source and its original Spotfire data table with the new ODBC data source and original Spotfire data table.
6. Done.

By doing all data transformations and visual analytics on a copy of the data table produced by the ODBC data source, you are able to use Spotfire's replace data table feature to switch out the original data source while keeping data transformations intact.

As you create new versions of the ODBC data source (with modified SQL) all data transformations will be intact because they are performed on a copy of the original data table.

The new 1010 (2) table is visible in the SourceView. Note that the table's update behaviour will be Automatic, meaning the table will update if the original 1010 data table is replaced.