Home / Knowledge Base / Northbound Connections / How to connect to Denodo from Tableau Desktop

How to connect to Denodo from Tableau Desktop

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 21 Oct 2019
Tags: DSN Connectivity Tableau External clients ODBC driver

Download document

Introduction

This document explains how to configure Denodo and Tableau Desktop to maximize the compatibility between both tools.

Instructions for Tableau 10.4 and Newer Versions

Denodo is included as a Data Source option since Tableau 10.4, this native connector simplifies the configuration required to enable the connection between Tableau and the Virtual DataPort server.

NOTE: Newer Tableau versions are only compatible with Denodo updates starting update 20190312. The reason is that newer Tableau versions use the PostgreSQL driver version 9.6.5 which is compatible with Denodo starting update 20190312.

Creating the Denodo Data Source in Tableau Desktop

  1. In Tableau, go to Data > New Data Source
  2. Select To a Server > More… > Denodo
  3. In the Denodo dialog fill in the following fields:
  1. Server: name of the server where the Virtual DataPort server is running
  2. Port: ODBC port number, by default it is 9996
  3. Database: name of the Denodo virtual database you want to connect to.
  4. Authentication: select between the authentication methods provided:
  1. Username and Password: Uses standard authentication.
  2. Integrated Authentication: With this option, the adapter will use “Integrated Authentication” (single sign-on) to connect to the Denodo server.
  • Server: When using “Integrated authentication”, enter the Fully Qualified Domain Name of the host where the Denodo server runs..
  • Database: The name of the database that has configured the Kerberos authentication for ODBC/ADO.NET connections.

Before using this option, you need to enable Kerberos authentication for the ODBC interface of the Denodo database you are connecting to. To do this, follow the 1 to 4 of the article How to set up SSO with pass-through for ODBC connections to Denodo.

  1. Select Require SSL if SSL is needed.
  2. Finally, click Sign In.

In addition, Tableau Help includes one article explaining how to configure the connection from Tableau to Denodo.

NOTE: Tableau Server support for “Integrated Authentication” (single sign-on) was introduced in Tableau 2019.2. Older Tableau Server version can connect to Denodo with Kerberos authentication using a service account.

Increasing the Fetch Size of the Tableau Connector

This section explains how to increase the "fetch size" of the Denodo connector of Tableau.

In some scenarios, the connection between Tableau and the Denodo server is slow. For example, when a majority of reports send queries to Denodo that return a large number of rows, and Tableau and/or Denodo run on the cloud or in a network with high latency. In these cases, increasing the fetch size may be beneficial.

By default, the Denodo connector of Tableau requests that the Denodo server sends data back to Tableau in network packets that are 2,048 bytes long. This is called "fetch size". Increasing the size of the network packets, reduces the number of network trips. However, increasing it too much may cause queries to run slower than with the default value. That is because if the fetch size is too big, Denodo will have to wait for packets "to be filled with data" before sending the packet to Tableau. Therefore, if the majority of reports of your users only return a few hundred rows, it is better to use the default fetch size.

To increase the default fetch size you have to use a Tableau Data Source Customization file (a .tdc file).

Create a file called “denodo.tdc” with the following content:

<?xml version='1.0' encoding='utf-8' ?>  

<connection-customization class='denodo' enabled='true' version='8.10'>  

        <vendor name='denodo'/>  

        <driver name='denodo'/>  

        <customizations>  

                <customization name='odbc-connect-string-extras' value='Fetch=10000;Protocol=7.4-0' />  

                <customization name='CAP_ODBC_FETCH_BUFFERS_RESIZABLE' value='yes' />  

        </customizations>  

</connection-customization>  

In this file, the element odbc-connect-string-extras has two attributes:

  • Fetch=10000: fetch size in bytes. You can tweak the value. Increasing it over 10,000 rarely provides any benefit but can slow down some reports.
  • Protocol=7.4-0: by adding this, the Denodo connector does not send the command SAVEPOINT. Sending one less command slightly improves the performance.

Follow these steps if you are using Tableau Desktop:

  1. Stop Tableau Desktop.
  2. Copy the file denodo.tdc to C:\Users\<your_user>\Documents\My Tableau Repository\Datasources.
  3. Start Tableau Desktop and try to connect to Denodo.
  4. To verify that the TDC file is in place, open the file C:\Users\<user>\Documents\My Tableau Repository\Logs\log.txt and look for a line like this one:

{"ts":"2019-03-28T10:46:35.671","pid":142076,"tid":"20094","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"msg","v":"Found matching TDC 'C:\\Users\\<your_user>\\Documents\\My Tableau Repository\\Datasources\\denodo.tdc' for class='denodo', vendor='denodo', and driver='denodo'."}

Follow these steps if you are using Tableau server:

  1. Copy the file denodo.tdc to one of these locations:
  1. For Windows, copy the file to ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources
  2. For Linux, copy the file to /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/

If you use multiple worker nodes, copy the tdc file to all of them.

  1. Restart Tableau server.

More information about Tableau Datasource Customization (.tdc) files

Instructions for older Tableau versions

Using the Generic (ODBC) adapter, in order to increase compatibility, you can customize the connection using a Tableau configuration file for a specific driver. To handle Tableau date formats a i18n map in Denodo, compatible with the date format used by Tableau, will be defined.

Creating a custom i18n map

As mentioned above, connections from Tableau need to use a specific i18n map that defines the date format used by Tableau. Tableau represents dates like ‘2016-01-01 00:00:00’. The date pattern is ‘yyyy-MM-dd HH:mm:ss’.

To create a map for Tableau based on existing i18n maps, these steps can be followed:

  1. Open the VDP Administration Tool and open the VQL Shell
  2. Get the VQL of an existing i18n map. For example, for the “us_pst” map, run the following command: DESC VQL MAP I18N us_pst
  3. Edit the VQL obtained in the previous step with the new date pattern, and create a new map with a new name, for example, us_pst_tableau 

CREATE MAP i18n us_pst_tableau (

    'country' = 'US'

    'datepattern' = 'yyyy-MM-dd HH:mm:ss'

    'doubledecimalposition' = '2'

    'doubledecimalseparator' = ''

    'doublegroupseparator' = ''

    'language' = 'en'

    'timezone' = 'PST'

);

Defining the ODBC Connection

  1. Install the ODBC driver. In Denodo 7.0 and 6.0, you can find the ODBC driver in <DENODO_HOME>/tools/client-drivers/odbc. For previous versions of the Denodo Platform download the driver from http://www.postgresql.org/ftp/odbc/versions/msi/.
  2. To find the details on how to install and configure the ODBC driver, go to the Virtual DataPort Developer Guide section "Configuration of the ODBC Driver in Windows".
  3. Create a DSN that points to Denodo, following the instructions described in the Virtual DataPort Developer Guide
  4. In the configuration of the DSN, specify the new i18n map. To do so, open the Options > Datasource section, go to Page 2, and add

set i18n to us_pst_tableau

to “Connect Settings”.

Add the Tableau configuration file for Denodo data sources

Once the DSN to be used from Tableau is defined, to increase the compatibility between Tableau and Denodo, a Tableau configuration file can be defined to set the behavior that Denodo expects in the connections and queries coming from Tableau. The following configuration file can be used:

odbc64-denodo.tdc

<connection-customization class='genericodbc' enabled='true' version='9.3'>

  <vendor name='PostgreSQL' />

  <driver name='DenodoODBC Unicode(x64)' />

  <customizations>

    <customization name='CAP_CREATE_TEMP_TABLES' value='no' />

    <customization name='CAP_ISOLATION_LEVEL_READ_COMMITTED' value='no' />

    <customization name='CAP_ISOLATION_LEVEL_READ_UNCOMMITTED' value='no' />

    <customization name='CAP_ISOLATION_LEVEL_REPEATABLE_READS' value='no' />

    <customization name='CAP_ISOLATION_LEVEL_SERIALIZABLE' value='no' />

    <customization name='CAP_ODBC_BIND_DETECT_ALIAS_CASE_FOLDING' value='no' />

    <customization name='CAP_ODBC_BIND_FORCE_DATETIME_AS_CHAR' value='no' />

    <customization name='CAP_ODBC_BIND_FORCE_DATE_AS_CHAR' value='no' />

    <customization name='CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS' value='no' />

    <customization name='CAP_ODBC_BIND_FORCE_MEDIUM_STRING_BUFFERS' value='no' />

    <customization name='CAP_ODBC_BIND_FORCE_SIGNED' value='no' />

    <customization name='CAP_ODBC_BIND_FORCE_SMALL_STRING_BUFFERS' value='no' />

    <customization name='CAP_ODBC_BIND_SUPPRESS_INT64' value='no' />

    <customization name='CAP_ODBC_BIND_SUPPRESS_PREFERRED_TYPES' value='no' />

    <customization name='CAP_ODBC_BIND_SUPPRESS_WIDE_CHAR' value='no' />

    <customization name='CAP_ODBC_CURSOR_DYNAMIC' value='no' />

    <customization name='CAP_ODBC_CURSOR_FORWARD_ONLY' value='no' />

    <customization name='CAP_ODBC_CURSOR_KEYSET_DRIVEN' value='no' />

    <customization name='CAP_ODBC_CURSOR_STATIC' value='no' />

    <customization name='CAP_ODBC_ERROR_IGNORE_FALSE_ALARM' value='no' />

    <customization name='CAP_ODBC_FETCH_BUFFERS_RESIZABLE' value='no' />

    <customization name='CAP_ODBC_FETCH_BUFFERS_SIZE_MASSIVE' value='no' />

    <customization name='CAP_ODBC_FETCH_CONTINUE_ON_ERROR' value='no' />

    <customization name='CAP_ODBC_METADATA_STRING_LENGTH_UNKNOWN' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_SELECT_STAR' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLCOLUMNS_API' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLFOREIGNKEYS_API' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLPRIMARYKEYS_API' value='no' />

    <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLSTATISTICS_API' value='no' />

    <customization name='CAP_ODBC_REBIND_SKIP_UNBIND' value='no' />

    <customization name='CAP_ODBC_TRIM_VARCHAR_PADDING' value='no' />

    <customization name='CAP_ODBC_UNBIND_AUTO' value='no' />

    <customization name='CAP_ODBC_UNBIND_BATCH' value='no' />

    <customization name='CAP_ODBC_UNBIND_EACH' value='no' />

    <customization name='CAP_QUERY_BOOLEXPR_TO_INTEXPR' value='yes' />

    <customization name='CAP_QUERY_FROM_REQUIRES_ALIAS' value='no' />

    <customization name='CAP_QUERY_GROUP_BY_ALIAS' value='no' />

    <customization name='CAP_QUERY_GROUP_BY_DEGREE' value='yes' />

    <customization name='CAP_QUERY_HAVING_REQUIRES_GROUP_BY' value='no' />

    <customization name='CAP_QUERY_HAVING_UNSUPPORTED' value='no' />

    <customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='no' />

    <customization name='CAP_QUERY_JOIN_REQUIRES_SCOPE' value='no' />

    <customization name='CAP_QUERY_NULL_REQUIRES_CAST' value='no' />

    <customization name='CAP_QUERY_SELECT_ALIASES_SORTED' value='yes' />

    <customization name='CAP_QUERY_SORT_BY_DEGREE' value='yes' />

    <customization name='CAP_QUERY_SUBQUERIES' value='yes' />

    <customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='yes' />

    <customization name='CAP_QUERY_SUBQUERY_QUERY_CONTEXT' value='no' />

    <customization name='CAP_QUERY_TOPSTYLE_LIMIT' value='yes' />

    <customization name='CAP_QUERY_TOPSTYLE_ROWNUM' value='no' />

    <customization name='CAP_QUERY_TOPSTYLE_TOP' value='no' />

    <customization name='CAP_QUERY_TOP_0_METADATA' value='no' />

    <customization name='CAP_QUERY_TOP_N' value='yes' />

    <customization name='CAP_QUERY_WHERE_FALSE_METADATA' value='no' />

    <customization name='CAP_SELECT_INTO' value='no' />

    <customization name='CAP_SELECT_TOP_INTO' value='yes' />

    <customization name='CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API' value='no' />

    <customization name='CAP_SET_ISOLATION_LEVEL_VIA_SQL' value='no' />

    <customization name='CAP_SUPPRESS_CONNECTION_POOLING' value='no' />

    <customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='yes' />

    <customization name='SQL_AGGREGATE_FUNCTIONS' value='64' />

    <customization name='SQL_CATALOG_NAME_SEPARATOR' value='.' />

    <customization name='SQL_CATALOG_TERM' value='catalog' />

    <customization name='SQL_CATALOG_USAGE' value='1' />

    <customization name='SQL_COLUMN_ALIAS' value='Y' />

    <customization name='SQL_CONVERT_BIGINT' value='0' />

    <customization name='SQL_CONVERT_BINARY' value='0' />

    <customization name='SQL_CONVERT_BIT' value='4104' />

    <customization name='SQL_CONVERT_CHAR' value='0' />

    <customization name='SQL_CONVERT_DATE' value='0' />

    <customization name='SQL_CONVERT_DECIMAL' value='0' />

    <customization name='SQL_CONVERT_DOUBLE' value='0' />

    <customization name='SQL_CONVERT_FLOAT' value='0' />

    <customization name='SQL_CONVERT_FUNCTIONS' value='1' />

    <customization name='SQL_CONVERT_INTEGER' value='4104' />

    <customization name='SQL_CONVERT_LONGVARBINARY' value='0' />

    <customization name='SQL_CONVERT_NUMERIC' value='0' />

    <customization name='SQL_CONVERT_REAL' value='0' />

    <customization name='SQL_CONVERT_SMALLINT' value='4104' />

    <customization name='SQL_CONVERT_TIME' value='0' />

    <customization name='SQL_CONVERT_TIMESTAMP' value='0' />

    <customization name='SQL_CONVERT_TINYINT' value='4104' />

    <customization name='SQL_CONVERT_VARBINARY' value='0' />

    <customization name='SQL_CONVERT_VARCHAR' value='4104' />

    <customization name='SQL_CURSOR_COMMIT_BEHAVIOR' value='2' />

    <customization name='SQL_DBMS_NAME' value='PostgreSQL' />

    <customization name='SQL_DBMS_VER' value='9.0.1' />

    <customization name='SQL_DRIVER_ODBC_VER' value='03.51' />

    <customization name='SQL_DRIVER_VER' value='09.03.0400' />

    <customization name='SQL_IDENTIFIER_QUOTE_CHAR' value='&quot;' />

    <customization name='SQL_MAX_IDENTIFIER_LEN' value='64' />

    <customization name='SQL_NUMERIC_FUNCTIONS' value='7798783' />

    <customization name='SQL_ODBC_INTERFACE_CONFORMANCE' value='1' />

    <customization name='SQL_ODBC_VER' value='03.80.0000' />

    <customization name='SQL_OJ_CAPABILITIES' value='127' />

    <customization name='SQL_QUOTED_IDENTIFIER_CASE' value='3' />

    <customization name='SQL_SCHEMA_TERM' value='schema' />

    <customization name='SQL_SCHEMA_USAGE' value='29' />

    <customization name='SQL_SPECIAL_CHARACTERS' value='_' />

    <customization name='SQL_SQL92_DATETIME_FUNCTIONS' value='7' />

    <customization name='SQL_SQL92_NUMERIC_VALUE_FUNCTIONS' value='63' />

    <customization name='SQL_SQL92_PREDICATES' value='16007' />

    <customization name='SQL_SQL92_RELATIONAL_JOIN_OPERATORS' value='1022' />

    <customization name='SQL_SQL92_STRING_FUNCTIONS' value='255' />

    <customization name='SQL_SQL92_VALUE_EXPRESSIONS' value='15' />

    <customization name='SQL_SQL_CONFORMANCE' value='1' />

    <customization name='SQL_STRING_FUNCTIONS' value='3423577' />

    <customization name='SQL_SYSTEM_FUNCTIONS' value='0' />

    <customization name='SQL_TABLE_TERM' value='table' />

    <customization name='SQL_TIMEDATE_ADD_INTERVALS' value='0' />

    <customization name='SQL_TIMEDATE_DIFF_INTERVALS' value='0' />

    <customization name='SQL_TIMEDATE_FUNCTIONS' value='2097151' />

    <customization name='SQL_TXN_CAPABLE' value='2' />

  </customizations>

</connection-customization>

NOTE: This configuration file has been tested with Tableau 9.3. In order for Tableau to use the customizations in this configuration file, the version number in the header of the file must match the Tableau version so, make sure to replace the version number to match the Tableau installation.

This configuration file can be used for different Denodo and ODBC driver versions with a minor modification in the tag that defines the driver name:

<driver name='DenodoODBC Unicode(x64)' />

This tag must include the name of the driver as it can be seen in the DSN configuration, for the 32 bit of the version this entry will be:

<driver name='DenodoODBC Unicode' />

For versions of the Denodo Platform previous to 6.0 the complete name of the driver changes and, for instance, for the 64 bit driver the entry must be:

<driver name='PostgreSQL Unicode(x64)' />

Note that previous versions of Denodo that use the PostgresSQL driver have some limitations.

Once the configuration file is ready it must be copied to the Tableau installation. If the user has  access to the global filesystem, copy the file to:

<TABLEAU_HOME>/defaults/Datasources

If the access to the Tableau installation folder is not possible, the configuration can still be personalized for each user. In this case copy the configuration file to:

%HOMEPATH%/Documents/My Tableau Repository/Datasources

NOTE: when this configuration is to be applied on a Tableau Server installation, instead of on Tableau Desktop, the configuration file must be copied to:

<TABLEAU_HOME>/Tableau Server/<version>/bin

Create your report in Tableau

Once Tableau is configured to connect to Denodo a Tableau report can be created:

  1. In Tableau, create a new data source using the generic adapter 'Other databases (ODBC)'
  2. Choose the DSN created in the previous section and click on 'Connect'
  3. Choose the table or query, and click OK.

Troubleshooting

Firewall killing connections between Tableau and Denodo

A user of Tableau Desktop or Tableau Server can execute reports that hit Denodo. However, after some period of time, when the user tries to run another report, Tableau returns the following error:

Error message: Bad Connection: Tableau could not connect to the data source: server closed the connection unexpectedly

This probably means the server terminated abnormally before or while processing the request. ; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

This problem occurs when:

  • There is a firewall between the Denodo server and Tableau (Desktop or Server)
  • The firewall is configured to close connections that have been inactive after a period of time.

To avoid this, create a Tableau Datasource Customization (.tdc) file, which allows to customize the Denodo connector.

The Tableau file will look like this:

<?xml version='1.0' encoding='utf-8' ?>  

<connection-customization class='denodo' enabled='true' version='8.10'>  

    <vendor name='denodo'/>  

    <driver name='denodo'/>  

    <customizations>  

        <customization name='odbc-connect-string-extras' value='KeepaliveTime=120;Fetch=10000;Protocol=7.4-0' />  

        <customization name='CAP_ODBC_FETCH_BUFFERS_RESIZABLE' value='yes' />  

    </customizations>  

</connection-customization>

 

The parameter KeepaliveTime instructs the connector to send a Keepalive packet to the Denodo server every 120 seconds. By sending this packet, the firewall does not close the connection because the connection is being used.

This solution will only work when Tableau Server or Tableau Desktop are connecting through Denodo using the Denodo named connector, not the "Generic" connector.

References

Customizing and Tuning ODBC Connections

Denodo connector in the Tableau Help

Creating a TDC file for Tableau Desktop

Using a .tdc File with Tableau Server

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