Tableau Desktop and Web Authoring Help

When you use a connector for a database that supports SQL, Tableau generates SQL statements that are tuned for that database. Because Tableau has no representation of the SQL dialect used by an ODBC data source, it must infer the supported syntax through a variety of tests. If a driver reports incorrect or incomplete information about the SQL dialect it supports, you can use Tableau customisations to fine tune this connection information in order to improve functionality and performance.

In this article, you will make an ODBC connection, examine the resulting Tableau Data Source (TDS) file and use part of it to create a Tableau Data source Customisation (TDC) file that you can then use to customise your ODBC connection. Before you begin, you should be familiar with the content in Tableau and ODBC.

Note: Tableau will provide reasonable levels of customer support to assist in troubleshooting connections with ODBC drivers, but can't create or customise a connector to work with a specific ODBC driver.

Make an ODBC connection

This section shows you how to create an ODBC connection using an example. In the example, you connect to a SQLite database using the SQLite ODBC driver.

Prerequisites

The ODBC connection in this article is based on SQLite (http://www.sqlite.org/), an open-source database.

You need to download the following two items:

Disclaimer: This information refers to a third-party product. This example is not an endorsement of this product over any other competing products.

• The SQLite 32-bit ODBC driver (required for 32-bit or 64-bit Windows). Download and install the following (select the SQLite 2 drivers):

  http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe

• A sample SQLite database created from the Microsoft example Northwind database. Download and expand the following:

  http://download.vive.net/Northwind.zip

Create a connection

To create an ODBC connection, you connect to the Northwind database using the SQLite3 ODBC driver, and then save the connection as a Tableau Data Source (TDS) file.

1. Open Tableau Desktop.

2. On the start page under Connect, click Other Databases (ODBC).

   Note: For a database that already has a Tableau-supported connector(Link opens in a new window), you can click the name of that connector to create a connection.

3. Under Connect Using, select Driver, and then from the drop-down list, select SQLite3 ODBC Driver.

4. Click Connect.

5. Next to the Database Name text box, click Browse, navigate to the location of your Northwind.sl3 file, and then click Open.

6. Click OK to close the dialog box, and then click Sign In.

7. On the data source page, in the Table text box, enter Orders.

8. Drag the Orders table to the canvas, and then click the sheet tab.

   A ‘Tableau identified limitations for the ODBC data source’ dialog box opens. You can review the details or dismiss the dialog box and continue. For more information, see How Tableau determines the capabilities of the ODBC driver.

9. Select Data > Orders [path to Northwind.sl3] > Add to Saved Data Sources.

10. Click Save.

11. Close the workbook. When asked to save changes, click No.

Now you can open the TDS file to examine the SQLite connection.

Review the XML structure of a TDS file

Open the saved Tableau Data Source (TDS) file in a text editor to view its XML structure. By default, the file you created above is named Northwind.sl3.tds and is in the following path:

Users\[your name]\Documents\My Tableau Repository\Datasources

or

Users\[your name]\Documents\My Tableau Repository (Beta)\Datasources

This is an XML document describing the SQLite connection to the Northwind Orders table. Within the <connection> section, find the <connection-customization> element, which contains <customization> elements that you can edit.

Vendor and driver name

The connection customization section begins with the names Tableau detected for the ODBC driver and the database vendor. Tableau uses this information to associate a specific connection customisation with a single type of ODBC data source. The section looks like this:

<connection-customization class='genericodbc' enabled='false' version='10.1'>
<vendor name='SQLite' />
<driver name='SQLite3 ODBC Driver' />
<customizations>

Types of customisations

Tableau permits two types of customisations: Tableau-specific capabilities, and ODBC API calls to SQLGetInfo. These customisations are made of name/value pairs, with the names following a convention of CAP_ for Tableau capabilities and SQL_ for the SQLGetInfo API calls.

The saved data source file contains examples of both types of customisations. These customisations represent the values that Tableau was able to detect by querying the driver when you connected. The list of customisations might be incomplete or incorrect. You can use customisation to modify and shape Tableau's behaviour when connecting to an ODBC data source.

The following articles contain a complete reference for the customisations. You should have an idea of how you need to adjust your ODBC connection to perform as you expect in your environment. Review the lists to get an idea of the customisations you can use to tune your ODBC connection.

• Tableau Capability Customisations Reference

• ODBC/SQL Customisations Reference

Format of customisation values

• The customisation values are all represented as strings for each name/value pair.

• Tableau capabilities are all Boolean values represented by yes or no.

• The SQLGetInfo values can be long-integer, short-integer or string data, depending on the expected return value of SQLGetInfo for the given identifier.

• Many of the integer fields are bitmasks that represent a collection of capabilities.

Make customisations global with a TDC file

To make your customisation changes apply to all connections for a given ODBC data source, you need to create a Tableau Data source Customisation (TDC) file. This file contains only the <connection-customization> section and is applied to any new Tableau connection that matches the database vendor name and driver name described in the TDC file (as noted in Vendor and driver name). Any existing workbook or data source file that already has an enabled customisation section will use only the customisations that it supplies, not the TDC file.

Important: Tableau does not test or support TDC files. These files should be used as a tool to explore or occasionally address issues with your data connection. Creating and maintaining TDC files requires careful manual editing, and there is no support for sharing these files.

Create a TDC file

When you create a TDC file, you need to save it to the right location so that it can be used by your connection. If you create a workbook that you want to publish to Tableau Server, then you need to save the TDC file to the server as well. For more information, see Using a TDC File with Tableau Server(Link opens in a new window) in Tableau Knowledge Base.

Structure of a TDC file

Each TDC file follows this basic structure:

<connection-customization class=DSCLASS enabled='true' version='10.0'>
    <vendor name=VENDOR />
    <driver name=DRIVER />
    <customizations>
        <customization name='CAP_FAST_METADATA' value='yes'/>
        ...
    </customizations>
</connection-customization>

A TDC file has three main sections:

• Connection customisation variables
• Vendor and driver names
• The customisations themselves

The connection-customization section includes the following:

• Class The data source you want to connect to and customise.
• Enabled Specifies whether the TDC file connection customisations are applied. In the TDC file, always set to “true”.
• Version Tableau ignores the version number.

The next section is crucial because it identifies the vendor name and driver name of the database provider for this TDC file's data source. Each TDC file can bind to only one data source type. For our native data sources, these names must both match the data source class name; for example, “teradata” for our Teradata connections. For ODBC data sources, the vendor name and driver name of the TDC file must match what is reported to Tableau by the database and driver; for example, “SQLite” and “SQLite3 ODBC Driver”, respectively.

The final section lists the actual connection customisations.

Use the TDC file with Tableau Desktop

1. Using a text editor, copy and paste the entire <connection-customization> section of your saved data source file. For reference, see the Sample SQLite TDC file.

2. Name the file odbc-sqlite.tdc and save it to Documents\My Tableau Repository\Datasources.

   Note: The file must be saved with a .tdc extension, but the name does not matter.

3. Restart Tableau Desktop to apply the change.

4. Create a new connection to SQLite as described in Create a connection.

5. Go to and open the Tableau Desktop log file, and look for a record similar to the example below to verify that this customisation file was applied to your new connection.

Use the TDC file with Tableau Prep Builder

1. Using a text editor, copy and paste the entire <connection-customization> section of your saved data source file. For reference, see the Sample SQLite TDC file.

2. Name the file odbc-sqlite.tdc and save it to the following location:

   • For Windows:

     Documents\My Tableau Prep Repository\Datasource

   • For Mac:

     Documents/My Tableau Prep Repository/Datasources

   Note: The file must be saved with a .tdc extension, but the name does not matter.

3. Restart Prep to apply the change.

4. Go to and open the log file, and look for a record similar to the example below to verify that this customisation file was applied to your new connection.

Use the TDC file with Tableau Server

1. Using a text editor, copy and paste the entire <connection-customization> section of your saved data source file. For reference, see the Sample SQLite TDC file.

2. Name the file odbc-sqlite.tdc and save it to the following location:

   • For Windows:

     In Tableau Server versions prior to 2018.2:

     Program Files\Tableau\Tableau Server\<version>\bin

     Starting in Tableau Server version 2018.2:

     Program Files\Tableau\Tableau Server\packages\bin.<build number>

     Or for all Tableau Server versions:

     ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources

     To save the file, you must be a Windows Administrator on the server computer.

   • For Linux:

     /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/

   Note: The file must be saved with a .tdc extension, but the name does not matter. The TDC file must be saved on all server nodes for consistent behaviour.

3. For Tableau Prep Conductor (running flows) or Prep Web Authoring (editing flows on the web), the TDC file must be placed in additional directories.

   Prep Conductor

   • For Windows:

     <data directory>\tabsvc\flowprocessor\Datasources

   • For Linux:

     <data directory>/tabsvc/flowprocessor/Datasources

   Prep Web Authoring

   • For Windows:

     <data directory>\tabsvc\flowminerva\Datasources

   • For Linux:

     <data directory>/tabsvc/flowmineva/Datasources

4. Restart Tableau Server to apply the change.

5. Go to and open the Tableau Server log file, and look for a record similar to the example below to verify that this customisation file was applied to your new connection.

Sample SQLite TDC file

<?xml version='1.0' encoding='utf-8' ?>
<connection-customization class='genericodbc' enabled='true' version='7.8'>
<vendor name='SQLite' />
<driver name='SQLite3 ODBC Driver' />
	<customizations>
		<customization name='CAP_CREATE_TEMP_TABLES' value='yes' />
		<customization name='CAP_QUERY_BOOLEXPR_TO_INTEXPR' value='yes' />
		<customization name='CAP_QUERY_GROUP_BY_ALIAS' value='no' />
		<customization name='CAP_QUERY_GROUP_BY_DEGREE' value='yes' />
		<customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='no' />
		<customization name='CAP_QUERY_JOIN_REQUIRES_SCOPE' value='no' />
		<customization name='CAP_QUERY_SUBQUERIES' value='yes' />
		<customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='yes' />
		<customization name='CAP_SELECT_INTO' value='no' />
		<customization name='CAP_SELECT_TOP_INTO' value='yes' />
		<customization name='SQL_AGGREGATE_FUNCTIONS' value='127' />
		<customization name='SQL_SQL_CONFORMANCE' value='4' />
	</customizations>
</connection-customization>

Customise your ODBC connection

Let's assume that you've made an ODBC connection to your data source and verified that you can get the metadata and data you need. Now you need to determine if creating an extract or if using the live connection as is gives you the functionality you need. If it does, the TDC file you created is sufficient, and there is no need to customise your connection.

If the TDC file is not performing the way you want it to, you can customise the connection. You should have an idea of how you need to adjust your ODBC connection to perform as you expect in your environment. Review the lists to see the customisations you can use to tune your ODBC connection.

• Tableau Capability Customisations Reference

• ODBC/SQL Customisations Reference

Common customisations for improving functionality

The following customisations can help improve functionality for data sources with default capabilities that are not optimal.

• CAP_SUPPRESS_DISCOVERY_QUERIES – Setting this value to true prevents Tableau from performing any SQL queries at the beginning of the connection to determine the driver's capabilities. You will need to consider which other capabilities should be explicitly enabled, since Tableau will not be able to determine those automatically.

• SQL_SQL_CONFORMANCE – This setting indicates which level of the SQL standard is fully supported by the data source. Tableau works best with at least entry-level conformance, so this value should be at least 1 if the data source supports it.

• SQL_AGGREGATE_FUNCTIONS – This setting indicates which aggregate functions are supported, such as MIN, MAX, SUM. A value of '127' indicates support for all standard SQL aggregate functions.

Handle severely limited data sources

Some data sources are so severely limited that Tableau is unable to complete the steps of creating a connection. Occasionally this is due to crashes within the driver, which cause Tableau to cease working. You can use a global TDC file to prevent Tableau from issuing queries or checking for capabilities that might be associated with the instability.

To create this file, you need to know the database vendor name and ODBC driver name.

1. Create a new connection in Tableau, open your log file, and then look for a line like the following:

   GenericODBCProtocol::Connect: Detected vendor: 'SQLite' and driver: 'SQLite3 ODBC Driver'

2. Create a TDC file with the listed vendor name and driver name. See Vendor and driver name for an example.

3. Use customisations, such as CAP_SUPPRESS_DISCOVERY_QUERIES, to constrain Tableau's interaction with a limited data source.

Configure ODBC driver settings

ODBC drivers provide dialog boxes for supplying connection details such as the server, user name and password. Many offer advanced options for controlling the connection behaviour. You should consider these options when exploring ways to improve the functionality or performance of your ODBC connection. In particular, look for settings that control the items listed below, as these have been the cause of past issues with ODBC connections in Tableau:

• Transaction Isolation - Prefer READ, COMMITTED, or SERIALIZABLE to ensure that queries do not include data from pending transactions in their results.

• Cursors - Prefer Holdable or Scrollable over Streaming cursors. Since Tableau closes a transaction after every read-only analytical query, streaming cursors may become truncated and lead to incomplete results displayed in Tableau.

• Row/Resultset Buffers - Prefer larger buffer sizes to improve the performance of fetching numerous rows, which can greatly improve the speed of creating extracts. This is sometimes called the cache size or response size.

• Character Set - In decreasing order of preference: UTF-16, UTF-8, ASCII.

Advanced SQLite customisations

Tableau has special customisations for SQLite that are built in to the product. These customisations take precedence over any connection customisations for SQLite in the workbook file, data source file or global TDS file. To make advanced changes to SQLite connection customisations, you need to trick Tableau into ignoring its own SQLite dialect. You can do so by changing the reported name of the database vendor (<customization name='SQL_DBMS_NAME' value='SQLite' />) to a different value such as SQLite-Tableau.

ODBC documentation reference

Microsoft's MSDN has documentation for the ODBC standard. The ODBC Appendixes(Link opens in a new window) are the most applicable as resources for the article information, specifically:

• Appendix C: SQL Grammar(Link opens in a new window) - especially the SQL Minimum Grammar(Link opens in a new window) and ODBC Escape Sequences(Link opens in a new window)

• Appendix D: Data Types(Link opens in a new window)

• Appendix E: Scalar Functions(Link opens in a new window)

See also

Other Databases (ODBC) – Describes how to connect to your data using the ODBC connector.

Tableau and ODBC – Provides background information about ODBC, describes how Tableau determines the functionality of an ODBC driver and lists frequently asked questions.

Tableau Capability Customisations Reference – Lists customisations you can use to define which Tableau capabilities are supported by the data source.

ODBC/SQL Customisations Reference – Lists customisations that represent the parts of the ODBC and SQL standards that the ODBC driver reports supporting.

Customise the Connection String for a Native Connector(Link opens in a new window) – This article in Tableau Community describes how to modify the connection made with a native connector using the odbc-connect-string-extras customisation.