Example: Customize an ODBC Connection

Applies to: Tableau Desktop

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 Tableau Data Source (TDS) file and use part of it to create a Tableau Data source 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.

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.

Open Tableau Desktop.
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.
Under Connect Using, select Driver, and then from the drop-down list, select SQLite3 ODBC Driver.
Click Connect.
Next to the Database Name text box, click Browse, navigate to the location of your Northwind.sl3 file, and then click Open.
Click OK to close the dialog box, and then click Sign In.
On the data source page, in the Table text box, enter Orders.
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.
Select Data > Orders [path to Northwind.sl3] > Add to Saved Data Sources.
Click Save.
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

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

The customization 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 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 Data source 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: TDC files can be utilized to customize the functionality of a connection or to diagnose and resolve issues related to connecting to your data. Tableau is unable to thoroughly test every potential modification you might make with these files. Creating and using TDC files requires manual editing and it is crucial to test them thoroughly before deploying them in a production environment.

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 savethe 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'/>
        <customization name='CAP_SOMETHING_ELSE' value='yes'/>
        ...
    </customizations>
</connection-customization>

A TDC file has three main sections:

Connection customization variables
Vendor and driver names
The customizations themselves

The connection-customization section includes the following:

class: The data source you want to connect to and customize.
enabled: Specifies whether the TDC file connection customizations 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 customizations.

Here are two example TDC files: First, a generic ODBC connection, and second, a modification to a native Salesforce connection. Note these are for example purposes only and the customizations listed are not selected to accomplish a specific goal.

<?xml version='1.0' encoding='utf-8' ?>
<connection-customization class='genericodbc' enabled='true' version='8.10'>
  <vendor name='Gen_ODBC_Vendor'/>
  <driver name='Gen_Driver'/>
  <customizations>
    <customization name='CAP_FAST_METADATA' value='yes' />
    <customization name='CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS' value='yes' />
    <customization name='CAP_ODBC_BIND_PRESERVE_BOM' value='yes' />
  </customizations>
</connection-customization>

<?xml version='1.0' encoding='utf-8' ?>
<connection-customization class='salesforce' enabled='true' version='8.10'>
  <vendor name='salesforce'/> 
  <driver name='salesforce'/>
  <customizations>
    <customization name='CAP_ODBC_EXPORT_CONTINUE_ON_ERROR' value='yes' />
    <customization name='CAP_ODBC_EXPORT_FORCE_SINGLE_ROW_BINDING' value='yes' />
    <customization name='CAP_ODBC_EXPORT_SUPPRESS_STRING_WIDTH_VALIDATION' value='no' />
  </customizations>
</connection-customization>

Use the TDC file with Tableau Desktop

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.
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.
Restart Tableau Desktop to apply the change.
Create a new connection to SQLite as described in Create a connection.
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'`

Use the TDC file with Tableau Prep Builder

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.
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.
Restart Prep to apply the change.
Go to and open the 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 Prep Repository\Logs	`Found matching TDC 'C:\\Users\\<name>\\Documents\\My Tableau Prep Repository\\Datasources\\odbc-sqlite.tdc' for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'`

Use the TDC file with Tableau Server

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.
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 behavior.
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
Restart Tableau Server to apply the change.
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

Log File Location	Example of Record
<install path>\Tableau Server\data\tabsvc\log\vizqlserver <install path>\Tableau Server\data\tabsvc\log\backgrounder <install path>\Tableau Server\data\tabsvc\log\dataserver <install path>\Tableau Server\data\tabsvc\log\vizportal	`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'`

<install path>\Tableau Server\data\tabsvc\log\vizqlserver

<install path>\Tableau Server\data\tabsvc\log\backgrounder

<install path>\Tableau Server\data\tabsvc\log\dataserver

<install path>\Tableau Server\data\tabsvc\log\vizportal

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.

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.

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'
```
Create a TDC file with the listed vendor name and driver name. See Vendor and driver name for an example.
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:

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

Tableau Desktop and Web Authoring Help