Applies to:
Denodo 8.0
,
Denodo 7.0
,
Denodo 6.0
Last modified on: 20 Jan 2021
Tags:
Connectivity
DSN
External clients
ODBC driver
Tableau
This document explains how to configure Denodo and Tableau Desktop to maximize the compatibility between both tools.
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 8, Denodo 7 updates starting update 20190312 and Denodo 6 update 20190422. The reason is that newer Tableau versions use the PostgreSQL driver version 9.6.5 which is compatible with Denodo 6, 7 starting the mentioned updates and 8.
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.
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 versions can connect to Denodo with Kerberos authentication using a service account. |
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' /> </customizations> </connection-customization> |
In this file, the element odbc-connect-string-extras has two attributes:
Follow these steps if you are using Tableau Desktop:
{"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:
If you use multiple worker nodes, copy the tdc file to all of them.
More information about Tableau Datasource Customization (.tdc) files
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.
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:
CREATE MAP i18n us_pst_tableau (
'country' = 'US'
'datepattern' = 'yyyy-MM-dd HH:mm:ss'
'doubledecimalposition' = '2'
'doubledecimalseparator' = ''
'doublegroupseparator' = ''
'language' = 'en'
'timezone' = 'PST'
);
set i18n to us_pst_tableau
to “Connect Settings”.
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='"' /> <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
Once Tableau is configured to connect to Denodo a Tableau report can be created:
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:
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' /> </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 connected through Denodo using the Denodo named connector, not the "Generic" connector.
Customizing and Tuning ODBC Connections
Denodo connector in the Tableau Help