Connecting to Denodo from Tibco Spotfire

Applies to: Denodo 7.0 , Denodo 6.0
Last modified on: 29 May 2019
Tags: Spotfire JDBC driver External clients ODBC driver

Download document

You can translate the document:

Goal

This document describes how to connect to a Denodo Virtual DataPort server from Tibco Spotfire. The document explains how to configure this connections using both JDBC and ODBC, the JDBC alternative is recommended over ODBC.

Connecting to Denodo using the JDBC Driver

To allow the Tibco Spotfire client application to use JDBC data sources, the data source needs to be configured first in the Tibco Spotfire Server.

In the Spotfire Server Installation and Configuration Manual, section Data Source Templates, the procedure needed to configure data sources is explained. Those data sources will be available when creating Information Links.

As Denodo’s JDBC driver is not pre-installed as part of the Spotfire Server installation, the driver must be manually installed on the Spotfire Server. The driver file can be copied from the folder <DENODO_HOME>/tools/client-drivers/jdbc of a Denodo installation to the <SPOTFIRE_HOME>/tomcat/lib folder of the Spotfire Server. After adding the JDBC driver, the Spotfire Server must be restarted.

Note: Spotfire does not provide a means to isolate classloading per data source, so a single installation can not simultaneously have different versions of the same drivers loaded.

Once the JDBC driver is added to the Spotfire installation, to add a new data source, an XML template with the data source configuration will be used. The complete list of available configuration settings and default values can be consulted in the XML Settings section of the Spotfire manual. This example can be used as a basic configuration to connect to Denodo Virtual DataPort:

<jdbc-type-settings>

  <type-name>Denodo</type-name>

  <display-name>Denodo</display-name>

  <driver>com.denodo.vdp.jdbc.Driver</driver>

  <connection-url-pattern>

  jdbc:vdb://server:port/database?autoCommit=true

  </connection-url-pattern>

  <supports-catalogs>false</supports-catalogs>

  <supports-schemas>false</supports-schemas>

  <column‐alias‐pattern>$$name$$</column‐alias‐pattern>

  <use-ansii-style-outer-join>true</use-ansii-style-outer-join>

  <metadata-provider>

  com.spotfire.ws.im.ds.sql.BasicJDBCMetadataProvider

  </metadata-provider>

<supports-procedures>true</supports-procedures>

<ping-command>Select 1</ping-command>

</jdbc-type-settings>

To connect to a database configured with the Unicode charset for identifiers, the template must include the ` character for the alias and name patterns:

<jdbc-type-settings>

  <type-name>Denodo</type-name>

  <display-name>Denodo</display-name>

  <driver>com.denodo.vdp.jdbc.Driver</driver>

  <connection-url-pattern>

  jdbc:vdb://server:port/database?autoCommit=true

  </connection-url-pattern>

  <supports-catalogs>false</supports-catalogs>

  <supports-schemas>false</supports-schemas>

  <column‐alias‐pattern>`$$name$$`</column‐alias‐pattern>

  <column‐name‐pattern>`$$name$$`</column‐name‐pattern>

  <table‐name‐pattern>`$$name$$`</table‐name‐pattern>

  <use-ansii-style-outer-join>true</use-ansii-style-outer-join>

  <metadata-provider>

  com.spotfire.ws.im.ds.sql.BasicJDBCMetadataProvider

  </metadata-provider>

  <supports-procedures>true</supports-procedures>

<ping-command>Select 1</ping-command>  

</jdbc-type-settings>

IMPORTANT NOTE: In the XML template configuration as part of the connection-url-pattern, it is very important to include the URL parameter autoCommit and set this parameter to true. Otherwise,  Spotfire will use transactions for all the queries, even for select statements introducing unnecessary overhead that will make queries to run slower. This parameter is only available in the more recent and up to date versions of the Denodo Platform. Other parameters can also be added to the connection URL as needed.

Once the XML configuration file for the data source is ready, the add-ds-template command can be used to finally set up the data source. For more information about this command check the section Reference:Commands > add-ds-template of the Spotfire Server manual. To just add a new data source with name ‘Denodo’ and a configuration named ‘denodo_data_source.xml’ file run:

add-ds-template -n Denodo denodo_data_source.xml

Once the data source has been added to the Spotfire Server it will be possible to access data from a Denodo Virtual DataPort database from the Spotfire Client using an Information Link.

Connecting to Denodo using the ODBC Driver

In order to connect to Denodo from Spotfire using ODBC, a DSN pointing to the Denodo Virtual DataPort server needs to be created first as explained in the Virtual DataPort Developer Guide, section Access Through ODBC.

After the DSN is created, to access to it from the Spotfire Client follow these steps:

  1. Go to File > Open > From Database…

  1. Choose ODBC Data Provider data source type from the menu.

  1. Select the Denodo DSN and provide the credentials.

select_dsn.png

  1. All views in the virtual database are introspected. Select the views and fields to import ( or write the SQL sentence to issue to Denodo) and provide a data source name.

 

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training