Was this page helpful?
Yes No
Have a comment? Please leave it here. Thanks for your feedback!
All Tableau Help > Tableau Help > 
Applies to: Tableau Desktop

Customize and Tune ODBC Connections

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 customizations 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 .tds file and use part of it to create a Tableau Datasource Customization (.tdc) file that you can then use to customize 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 customize a connector to work with a specific ODBC driver.

In this article

Make an ODBC connection

Review the XML structure of a TDS file

Make customizations global with a .tdc file

Customize your ODBC connection

ODBC documentation reference

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.

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).

  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 data source (.tds file) in a text editor to view the XML structure of the .tds file. 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 customization 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 customizations

Tableau permits two types of customizations: Tableau-specific capabilities, and ODBC API calls to SQLGetInfo. These customizations 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 customizations. These customizations represent the values that Tableau was able to detect by querying the driver when you connected. The list of customizations might be incomplete or incorrect. You can use customization to modify and shape Tableau's behavior when connecting to an ODBC data source.

The following articles contain a complete reference for the customizations. 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 customizations you can use to tune your ODBC connection.

Format of customization values

Make customizations global with a .tdc file

To make your customization changes apply to all connections for a given ODBC data source, you need to create a Tableau Datasource Customization (.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 customization section will use only the customizations 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 in Tableau Knowledge Base.

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 using 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 customization file was applied to your new connection.

Log File Location Example of Record
By default, C:\Users\<user>\Documents\My Tableau Repository\Logs Found matching TDC 'C:\\Users\\<name>\\Documents\\My Tableau Repository\\Datasources\\odbc-sqlite.tdc' for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'

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 Program Files\Tableau\Tableau Server\<version>\bin.

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

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

  3. Restart Tableau Server to apply the change.

  4. In Tableau Desktop, create a new connection to SQLite as described in Create a connection.

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

Log File Location Example of Record
C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Logs Found matching TDC 'C:\\ProgramData\\Tableau\\Tableau Server\\data\\tabsvc\\vizqlserver\\Datasources\\odbc-sqlite.tdc for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'

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>

Customize 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 customize your connection.

If the .tdc file is not performing the way you want it to, you can customize 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 customizations you can use to tune your ODBC connection.

Common customizations for improving functionality

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

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 customizations, 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 behavior. 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:

Advanced SQLite customizations

Tableau has special customizations for SQLite that are built in to the product. These customizations take precedence over any connection customizations for SQLite in the workbook file, data source file, or global .tds file. In order to make advanced changes to SQLite connection customizations, 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 are the most applicable as resources for the article information, specifically:

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 Customizations Reference – Lists customizations you can use to define which Tableau capabilities are supported by the data source.

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