07/11/2015

[TIBCO Spotfire] Excel JDBC data source template

If you want to take full advantage of the Information Designer capabilities when connecting to non-default data sources, and your analysis can't be fully developed using only Connectors and/or ADS, you might want to create your own data source template. [1]

The idea is simple: as long as you have a proper JDBC driver and can supply basic details on how to connect and handle operations, Spotfire Server allows you to add a custom connection template that end users will be able to select when creating a new Data Source. [2]

Here is a sample connection template for Excel. Note that in this case you will need the ODBC driver as well.

 <jdbc-type-settings>   
  <!-- Informative name and display-name -->   
  <type-name>ODBC-EXCEL</type-name>   
  <!-- point out the JDBC-ODBC bridge -->   
  <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>   
  <!-- Pattern displayed to administrator when setting up the Datasource -->   
  <connection-url-pattern>jdbc:odbc:excel-odbc-source</connection-url-pattern>   
  <!-- Table types allowed for EXCEL is TABLE and SYSTEM TABLE -->   
  <table-types>TABLE, SYSTEM TABLE</table-types>   
  <!-- As ping command we will use the integer constant 1. Could really be any pseudo function. -->   
  <ping-command>SELECT 1</ping-command>   
  <connection-properties />   
  <!-- Excel does not support catalogs nor schemas -->   
  <supports-catalogs>false</supports-catalogs>   
  <supports-schemas>false</supports-schemas>   
  <!-- Found an error in excel not allowing Distinct in combination with order-by on all columns. -->   
  <!-- Choice between supporting order by and distinct in favor for distinct -->   
  <!-- to make prompt without duplicates and support distinct conditioning. -->   
  <supports-order-by>false</supports-order-by>   
  <!-- Format pattern for date, time and datetime (same as timestamp). -->   
  <date-literal-format-expression>{d '$$value$$'}</date-literal-format-expression>   
  <time-literal-format-expression>{t '$$value$$'}</time-literal-format-expression>   
  <date-time-literal-format-expression>{ts '$$value$$'}</date-time-literal-format-expression>   
  </jdbc-type-settings>   


[1] Data source template documentation - Chapter 11.5
[2] Sample guidelines to add Attivio as data source

2 comments:

  1. I would recommend not to use the "init commands data source" - it may be removed at any time in the future and the default one performs much better. So, unless there's a compelling reason to keep it, I would suggest that you remove the connection property.

    ReplyDelete
    Replies
    1. I think at the time the sample source had it in, but I will update the post to remove it, thanks :)

      Delete

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.