Connecting to SQream Using TIBCO Spotfire

Overview

The TIBCO Spotfire software is an analytics solution that enables visualizing and exploring data through dashboards and advanced analytics.

This document is a Quick Start Guide that describes the following:

Establishing a Connection between TIBCO Spotfire and SQream

TIBCO Spotfire supports the following versions:

  • JDBC driver - Version 4.5.2

  • ODBC driver - Version 4.1.1

SQream supports TIBCO Spotfire version 7.12.0.

The Establishing a JDBC Connection between TIBCO Spotfire and SQream section describes the following:

Creating a JDBC Connection

For TIBCO Spotfire to recognize SQream, you must add the correct JDBC jar file to Spotfire’s loaded binary folder. The following is an example of a path to the Spotfire loaded binaries folder: C:\tibco\tss\7.12.0\tomcat\bin.

For the complete TIBCO Spotfire documentation, see TIBCO Spotfire® JDBC Data Access Connectivity Details.

Creating an ODBC Connection

To create an ODBC connection

  1. Install and configure ODBC on Windows.

    For more information, see Install and Configure ODBC on Windows.

  2. Launch the TIBCO Spotfire application.

  3. From the File menu click Add Data Tables.

    The Add Database Tables window is displayed.

  4. Click Add and select Database.

    The Open Database window is displayed.

  5. In the Data source type area, select ODBC SQream (Odbc Data Provider) and click Configure.

    The Configure Data Source and Connection window is displayed.

  6. Select System or user data source and from the drop-down menu select the DSN of your data source (SQreamDB).

  7. Provide your database username and password and click OK.

  8. In the Open Database window, click OK.

    The Specify Tables and Columns window is displayed.

  9. In the Specify Tables and Columns window, select the checkboxes corresponding to the tables and columns that you want to include in your SQL statement.

  10. In the Data source name field, set your data source name and click OK.

    Your data source is displayed in the Data tables area.

  11. In the Add Data Tables dialog, click OK to load the data from your ODBC data source into Spotfire.

Note

Verify that you have checked the SQL statement.

Creating the SQream Data Source Template

After creating a connection, you can create your SQream data source template.

To create your SQream data source template:

  1. Log in to the TIBCO Spotfire Server Configuration Tool.

  2. From the Configuration tab, in the Configuration Start menu, click Data Source Templates.

    The Data Source Templates list is displayed.

  3. From the Data Source Templates list do one of the following:

  • Override an existing template:

    1. In the template text field, select an existing template.

    2. Copy and paste your data source template text.

  • Create a new template:

    1. Click New.

      The Add Data Source Template window is displayed.

    2. In the Name field, define your template name.

    3. In the Data Source Template text field, copy and paste your data source template text.

      The following is an example of a data source template:

      <jdbc-type-settings>
        <type-name>SQream   </type-name>
        <driver>com.sqream.jdbc.SQDriver   </driver>
        <connection-url-pattern>jdbc:Sqream://&lt;host&gt;:&lt;port&gt;/database;user=sqream;password=sqream;cluster=true   </connection-url-pattern>
        <supports-catalogs>true   </supports-catalogs>
        <supports-schemas>true   </supports-schemas>
        <supports-procedures>false   </supports-procedures>
        <table-types>TABLE,EXTERNAL_TABLE   </table-types>
        <java-to-sql-type-conversions>
         <type-mapping>
            <from>Bool   </from>
            <to>Integer   </to>
          </type-mapping>
          <type-mapping>
            <from>VARCHAR(2048)   </from>
            <to>String   </to>
          </type-mapping>
          <type-mapping>
            <from>INT   </from>
            <to>Integer   </to>
          </type-mapping>
          <type-mapping>
            <from>BIGINT   </from>
            <to>LongInteger   </to>
          </type-mapping>
          <type-mapping>
            <from>Real   </from>
            <to>Real   </to>
          </type-mapping>
               <type-mapping>
            <from>Decimal   </from>
            <to>Float   </to>
          </type-mapping>
           <type-mapping>
            <from>Numeric   </from>
            <to>Float   </to>
          </type-mapping>
          <type-mapping>
            <from>Date   </from>
            <to>DATE   </to>
          </type-mapping>
          <type-mapping>
            <from>DateTime   </from>
            <to>DateTime   </to>
          </type-mapping>
         </java-to-sql-type-conversions>
        <ping-command>   </ping-command>
      </jdbc-type-settings>
      
  1. Click Save configuration.

  2. Close and restart your Spotfire server.

Creating a Data Source

After creating the SQream data source template, you can create a data source.

To create a data source:

  1. Launch the TIBCO Spotfire application.

  2. From the Tools menu, select Information Designer.

    The Information Designer window is displayed.

  3. From the New menu, click Data Source.

    The Data Source tab is displayed.

  4. Provide the following information:

    • Name - define a unique name.

    • Type - use the same type template name you used while configuring your template. See Step 3 in Creating the SQream Data Source Template.

    • Connection URL - use the standard JDBC connection string, <ip>:<port>/database.

    • No. of connections - define a number between 1 and 100. SQream recommends setting your number of connections to 100.

    • Username and Password - define your SQream username and password.

Troubleshooting

The Troubleshooting section describes the following scenarios:

The JDBC Driver does not Support Boolean, Decimal, or Numeric Types

When attempting to load data, the the Boolean, Decimal, or Numeric column types are not supported and generate the following error:

Failed to execute query: Unsupported JDBC data type in query result: Bool (HRESULT: 80131500)

The error above is resolved by casting the columns as follows:

  • Bool columns to INT.

  • Decimal and Numeric columns to REAL.

For more information, see the following:

Information Services do not Support Live Queries

TIBCO Spotfire data connectors support live queries, but no APIs currently exist for creating custom data connectors. This is resolved by creating a customized SQream adapter using TIBCO’s Data Virtualization (TDV) or the Spotfire Advanced Services (ADS). These can be used from the built-in TDV connector to enable live queries.

This resolution applies to JDBC and ODBC drivers.