Introduction to Customization
Tableau generates complex SQL statements which are tuned for each of the natively supported data sources. Since Tableau has no detailed representation of the SQL dialect used by a generic ODBC data source, it must infer the supported syntax through a variety of tests and ODBC API calls. If a driver reports incorrect or incomplete information about the SQL dialect it supports, Tableau allows a user to customize this connection information in order to improve functionality and performance.
Getting Started
Let's begin with a basic connection to the Northwind database. We will save this connection as a Tableau Data Source (.tds) file which we will then manipulate.
-
Launch Tableau and open the"Connect to Data" dialog.
-
Select"Other Databases (ODBC)" to bring up the ODBC connection dialog.
-
Switch the connection mode to"Driver" and choose"SQLite3 ODBC Driver".
-
Click"Connect", and browse to the location of your Northwind.sl3 file.
-
Finally choose"Orders" as your table, and complete the connection.
-
At this point you will see a dialog warning that Tableau has detected some limitations in functionality for this data source. View the details to see a list of missing functionality. Dismiss the dialogs to enter the Tableau visualization interface.
-
Save the data source from the menu Data > Data Connection > Export
-
Close the current workbook.
We can now begin editing this file to customize the SQLite connection.
Connection Customization XML Structure
Open the saved data source file in a text editor. You should recognize this as a simple XML document describing the SQLite connection to the Northwind Orders table. Within the <connection> section you will find the <connection-customization> tag, which is where we will focus our attention.
Vendor / 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. We will revisit this topic in the section 'Making Customizations Global'.
<connection-customization class='genericodbc' enabled='true' version='7.10'>
 <vendor name='SQLite' />
 <driver name='SQLite3 ODBC Driver' />
 <customizations>
 …Â
Types of Customizations
Tableau currently permits two types of customizations: Tableau-specific capabilities, and ODBC API calls to SQLGetInfo. These customizations are simple name / value pairs, with the names following a convention of 'CAP_' for Tableau capabilities and 'SQL_' for the SQLGetInfo API calls. These customizations are listed in detail in the Appendix at the end of this article:
-
Appendix B - List of Tableau Capability Customizations
-
Appendix C - List of ODBC/SQL Customizations
You will notice that the saved data source file already contains examples of both types of customizations. These customizations represent the values which Tableau was able to detect by probing the driver at the start of a connection. The list of customizations may be incomplete or incorrect, and by modifying this list we will be able to shape Tableau's behavior when connecting to an ODBC data source.
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 may be either 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 which represent a collection of capabilities.
Customizing a Single Connection
We will now proceed to modify our SQLite data source file to customize the connection.
Enabling Connection Customizations
Within the<connection-customization> tag there is an attribute named ‘enabled' which is off by default. By changing this value to 'true' we can force Tableau to adopt the connection customizations instead of probing the data source at the start of the connection.
To verify this, set enabled='true' and save your data source file. Open this file with Tableau to reconnect to your SQLite Northwind data set. Now open your Tableau log file from the Tableau repository, and verify that you see the following line: Loaded an inline TDC for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'.
Close the current workbook before proceeding.
Example - Capability Customization
To see how capability customizations can affect the queries that Tableau issues, let's see what happens when we disable an existing capability. Edit the saved data source file and change the capability named CAP_QUERY_HAVING_REQUIRES_GROUP_BY to have the value 'no'. Open the data source file from Tableau and dismiss the warning dialog. Now double-click on [Number of Records] in the data window, which will place it on the Rows shelf. This will generate a query error whose details explain that a HAVING clause requires some form of grouping field. Close this workbook and revert your change to the saved data source file.
Example - ODBC / SQL Customization
An ODBC driver can describe its level of support for various features of the ODBC and SQL specifications. In this example we will configure the maximum number of characters which can be used in an identifier. Edit the saved data source file and change the capability named SQL_MAX_IDENTIFIER_LEN to have the value '10'. Open the data source file from Tableau and dismiss the warning dialog. Now double-click on [ShipCountry] in the data window. To view the query Tableau issued, open your Tableau log file and scroll towards the end - you should see a query like: SELECT "Orders"."ShipCountry" AS "no_ShipCo" ... The alias Tableau used to identify this field is "no_ShipCo", which we see has been truncated to ensure it fits (along with an end-of-string termination character) into the allotted space. Close this workbook and revert your change to the saved data source file.
Common Customizations for Improving Functionality
The following customizations can help with data sources which misrepresent their capabilities or are unstable when used with default capabilities.
-
CAP_SUPPRESS_DISCOVERY_QUERIES - A value of 'true' will prevent Tableau from performing any SQL queries at the beginning of the connection to determine the supported capabilities. You will need to consider which other capabilities should be explicitly enabled, since Tableau will not be able to determine those automatically.
-
CAP_SKIP_CONNECT_VALIDATION - A value of 'true' will prevent Tableau from issuing a SQL query to validate a multi-table or custom SQL connection.
-
SQL_SQL_CONFORMANCE - Declare 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 - Declare which aggregate functions are supported, e.g. MIN, MAX, SUM. A value of '127' enables support for all standard SQL aggregates.
Making Customizations Global
The examples above demonstrated how to customize a saved Tableau data source file. You can also make these changes in the <connection> section within a saved Tableau workbook file. In both cases, the changes will only apply to the single data source file or the single workbook which you have edited.
To ensure your changes will apply to all connections for a given ODBC data source you will need to create a standalone Tableau Data-connection Customization file, or TDC. This file will contain only the <connection-customization> section and will be applied to any Tableau connection which matches the database vendor name and driver name described by the TDC file. However any saved workbook or data source file which already has an enabled customization section will only use the customizations which it supplies.
Browse to the 'Datasources' directory of the Tableau repository and create a new file named 'odbc-sqlite.tdc'. The file must end in '.tdc' but otherwise the name does not matter. Copy the entire <connection-customization> section of your saved data source file and paste it into the new .tdc file. For reference, a sample .tdc file for SQLite is in Appendix E - Sample SQLite TDC file.
You will need to quit Tableau and start a new instance to pick up this change. Then create a new connection to SQLite as described in the 'Getting Started' section. Next, open your Tableau log file to verify that this customization file applied to your new connection. You should see a line in the log file like the following: Found matching TDC 'C:\Users\<name>\Documents\My Tableau Repository\Datasources\odbc-sqlite.tdc' for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'.
