Tableau and ODBC

Applies to: Tableau Desktop

Tableau includes built-in connectors for many databases. These connectors take advantage of capabilities and optimizations specific to these data sources, and they're developed and tested to ensure they are robust and provide good performance.

But you might be working with a database that Tableau doesn't have a connection for. In that case, you still might be able to connect Tableau to the database. Tableau has a connector that uses the ODBC standard. By using ODBC, you can access any data source that supports the SQL standard and implements the ODBC API.

ODBC basics

ODBC (Open Database Connectivity) is an industry standard that enables a wide variety of software to access data. The basis of ODBC is a standard syntax for SQL queries that is used by software applications to connect to and request data from databases. The ODBC driver, usually provided by the database vendor, accepts requests in this standard syntax and converts the request to the native format that the target database prefers. In effect, the ODBC driver is a translation layer to convert from a general-purpose request to the database-specific request.

For example, the ODBC specification details that dates should be specified in SQL statements by using the syntax: {d 'yyyy-mm-dd'}. This format is then translated by the driver to the proper date syntax of the actual database. If you use the ODBC connector to connect to an Oracle database, the ODBC connector sends a request to the Oracle ODBC driver in this format:

select name from emp where birthdate > {d '1987-12-29'}

The driver converts your request to the actual format the Oracle database requires, which is this:

select name from emp where birthdate > '29-DEC-87'

The ODBC specification includes syntax for making function requests, join syntax, data types, and data type conversions. The SQL language itself supports complex concepts such as nested queries, correlated subqueries, temporary tables, and a variety of functions that can be used in select, where, group by, join clauses, and more. The ODBC driver is responsible for the proper conversion of all these requests to the syntax of the target database.

How Tableau determines the capabilities of the ODBC driver

There are differences in how each database vendor implements capabilities of the ODBC standard. Tableau uses a feature-discovery API in ODBC to question a database driver about the functionality it supports. One example of how Tableau changes behavior based on what the driver reports is the list of functions available when creating a calculated field. Fewer driver limitations means that more functions are available.

In some cases Tableau will require you to create a data extract from the data returned by the ODBC connector. And there are some ODBC drivers and databases that Tableau can't connect to.

ODBC discovery

During the initial connection, Tableau performs requests to the driver and SQL discovery queries in order to determine the capabilities of the driver.

Some examples of the capabilities discovery that Tableau performs include:

Available scalar and aggregate functions.
Available date and time manipulation functions.
Can you create temporary tables?
Can you use IF and SELECT into statements?
Are subqueries supported?
Are top and limit queries supported?
What join styles are supported (outer, inner, full)?
Supported data types.

Based on the outcome of the capabilities discovery, Tableau will classify the current connection as being in one of four categories:

Fully Functional. This is a driver that supports all the functions and capabilities that Tableau uses.
Minor limitations. This is a driver that has a small number of non-critical limitations. An example would be a driver that doesn't support the full set of numeric, string, or date functions that Tableau normally makes available. Tableau will modify its behavior to account for these limitations and proceed.
Major limitations. This is a driver that has a large number of limitations or critical capabilities that Tableau relies upon but that are not supported. However, even with these major restrictions, Tableau might be able to connect well enough to create a Tableau extract to retrieve the data into Tableau for further work offline from the database. In this case, Tableau will recommend that you create an extract. When you create the extract, you might not be able to create a filter on the data source to reduce the amount of data in the extract. (For more information, see Filter Data from Data Sources.) After the data has been extracted, full Tableau functionality is available when working with the extract.
Fatal limitations. This is a driver that does not support even the most minimal set of capabilities that Tableau needs in order to connect and run the basic queries to create extract files. Therefore, Tableau is unable to proceed any further with this driver.

After the connection is established, if Tableau has determined that the capabilities available classify this connection as anything less than fully functional, a message is displayed to inform you of the limitations that have been detected. For example, a connection to an ODBC driver with minor limitations displays this message:

For more critical limitations, the warning dialog box will advise you to create a Tableau extract in order to proceed.

Tune ODBC connector performance

Tableau supports the ability to customize your ODBC data connection, which can improve the connection experience. For more information, see Customize and Tune a Connection.

Tableau support for ODBC connections

Tableau provides no guarantee or warranty that using Tableau with any particular ODBC driver or database will be able to successfully connect and query data. Some ODBC drivers will support the full Tableau interactive experience, while others may work only for creating extracts. Some ODBC drivers might not work with Tableau.

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.

ODBC FAQ

What's a typical use case for the ODBC connector?

The most common use case for an ODBC connection is to access data specifically to retrieve it into a Tableau extract. Bringing this data into a Tableau extract then allows the full Tableau capabilities to work with the data. Many ODBC drivers support the needed functionality to connect and perform the simpler queries required to perform an extract. Whether you extract all of the data or just select a few columns and set some filters to retrieve a relevant subset, this use of ODBC is a good scenario to keep in mind.

Where do I get ODBC drivers for my database?

Most database vendors distribute ODBC drivers for use with their databases. Contact your database vendor to obtain the drivers. Additionally there are a number of third party manufacturers of ODBC drivers that can provide drivers for a variety of common databases.

What ODBC version driver do I need?

Be sure your ODBC driver is a version 3 or higher driver, meaning it implements the ODBC specification version 3. Each driver provider will have their own version numbering system for their driver versions, which will likely be different from the ODBC version number it implements. The ODBC version 3 specification was introduced in 1995, so it is likely your database provider has a driver that implements this level of compliance. We have seen a number of Tableau customers with drivers that are not version 3 compliant (which fail to connect), who then upgraded to a newer driver and were able to proceed.

Has Tableau tested database [x]?

Tableau has tested ODBC connections with many data sources, but due to the volume of ODBC drivers available on the market we haven't tested against every possible driver, nor can Tableau perform this breadth of testing. The best recommendation for any particular driver is to give it a try, and let us know how it works.

What do I do if it doesn't work?

First check the driver version. If you look at the Tableau logs we will echo back the ODBC version level the driver reports. Search the logs for an entry like the following:

ODBCProtocol: driver ODBC version: 03.52

The number at the end indicates the ODBC version level. If it is less than 03.00, the driver needs to be upgraded.

If you get warnings when you connect about capabilities not supported, check with your database vendor to see if they have an updated driver that supports more capabilities. Not all drivers will work with Tableau.

Should I use the named database connector or the ODBC connector?

If you are connecting to a database for which Tableau has a named connection option, then use the named connector. The named connectors are optimized for the particular database.

Tableau Desktop and Web Authoring Help