Customizing and Tuning ODBC Connections


RELATED ARTICLES
Product(s):  Professional Edition, Tableau Server
Version(s):  8.0, 7.0, 6.1
Last Modified Date:  12 Apr 2013
Tableau 5.2 introduced ODBC connectivity to allow access to any data source which supports the SQL standard and is compliant with the ODBC API. The mechanism supporting this is a rich feature-discovery API in ODBC that Tableau uses to question a database driver about the functionality it supports. Tableau has an experimental feature which allows users to customize parts of their ODBC data connection, which can dramatically improve the connection experience.

Updates

2011-11-03: Added new capabilities associated with the introduction of native ODBC support in Tableau 6.1.4.
2012-08-07: Added new capabilities in Tableau 7.0.7. 
2012-09-20: Added a new capability in Tableau 7.0.8.
2012-03-21: Added new capabilities in Tableau 8.0

Intended Audience

This article is for advanced users who are comfortable with reading technical API specifications, as well as editing basic XML documents and exploring the XML inside Tableau workbooks (.twb) and data source files (.tds). The reader should be familiar with our separate knowledgebase article discussing Tableau's requirements and level of support for ODBC data sources.

Objective and Caveats

Tableau expects a data source to follow the ODBC and SQL standards, but we recognize that most do not. Deviations from these standards may be minor, and this article will discuss how to customize a connection to work around such problems. However some data sources have substantial differences in their SQL dialect while others have very unstable drivers, and for both the best-case scenario is to restrict Tableau to a basic connection and require users to create an Extract. In some extreme cases, no degree of customization will allow Tableau to connect.

Prerequisites

All of these examples will be based on SQLite (http://www.sqlite.org/), an open-source database. You will need to download the following two items:

  1. The SQLite 32-bit ODBC driver (required for 32-bit or 64-bit Windows). Download and install the following:
    http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe
  2. A sample SQLite database created from the Microsoft example ‘Northwind database'. Download and unzip the following:
    http://download.vive.net/Northwind.zip

Terms Used

This article will reference the Tableau repository, which is located in the user's Documents folder under one of the following locations:

  • My Tableau Repository
  • My Tableau Repository (Beta)

External References

The reader will be directed to the following external sites for reference material.

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.

  1. Launch Tableau and open the"Connect to Data" dialog.
  2. Select"Other Databases (ODBC)" to bring up the ODBC connection dialog.
  3. Switch the connection mode to"Driver" and choose"SQLite3 ODBC Driver".
  4. Click"Connect", and browse to the location of your Northwind.sl3 file.
  5. Choose"Orders" as your table, and complete the connection.
  6. 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 dialog boxes to enter the Tableau visualization interface.
  7. Save the data source by selecting the data source from the Data menu, and selecting  Add to Saved Data Sources.
  8. 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'.



                                      
Advanced Customizations

	

		Native ODBC

	

		Tableau 6.1.4 introduces a new connection technology which work directly with the ODBC API instead of relying on the OLE DB translation bridge to ODBC. This provides fine-tuned control over how Tableau uses the ODBC API through the CAP_ODBC_* connection customization options. Additionally, the direct ODBC connection layer avoids some of the complexity and overhead caused by the OLE DB bridge, which can reduce stability and performance.

	

		You can enable this new native ODBC protocol in Tableau in two ways:

	
  • 
			You can set the CAP_ODBC_USE_NATIVE_PROTOCOL capability (either globally or inline) for a given data source.
  • 
			You can use the generic ODBC connection dialog to edit the connection attribute named 'odbc-native-protocol', as shown in the screenshot below.

	

		Enabling the native ODBC protocol:
		

	

		Handling 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. A global TDC file can be used to prevent Tableau from issuing queries or checking for capabilities which may be associated with the instability. To create this file you will need to know the database vendor name and ODBC driver name. After attempting to create a new connection within Tableau, open your log file and 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. Consider using options such as CAP_SUPPRESS_DISCOVERY_QUERIES to constrain Tableau's interaction with a troublesome data source. Enabling CAP_ODBC_USE_NATIVE_PROTOCOL will cause Tableau to use the native ODBC protocol for this data source, which may improve stability and enables additional tuning options.

	

		Configuring ODBC Driver Settings

	

		ODBC drivers provide interactive dialogs for supplying connection details such as the server, username 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 below, as these have been the cause of past issues with ODBC connections in Tableau:

	
  • 
			Transaction Isolation - Prefer READ COMMITTED or SERIALIZABLE to ensure 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 5.2.4 introduced special customizations for SQLite which are built-in to the product. These customizations take precedence over any inline or global connection customizations for SQLite. In order to make advanced changes to SQLite via connection customizations, you will need to trick Tableau into ignoring its own SQLite dialect. You can do so by changing the reported name of the database vendor, tracked as SQL_DBMS_NAME, to a different value such as 'SQLite-Tableau'.







	Appendix



	

		Appendix A - Useful Resources

	

		Microsoft's MSDN has a tremendous amount of documentation for the ODBC standard. The ODBC Appendixes are the most applicable to this article, specifically:

	




                                    
Appendix B - List of Tableau Capability Customizations
                                    

	The following customizations will define which Tableau capabilities are supported by the data source. Many of these customizations will influence the type of SQL queries which Tableau issues. Unless explicitly defined, Tableau will attempt to determine the proper values for each capability by issuing various forms of SQL queries to experimentally verify which forms are supported.

 

				CAP_CREATE_TEMP_TABLES
				Set to 'yes' if Tableau can create temporary tables needed for certain complex or optimized queries. See also: CAP_SELECT_INTO.

				CAP_ISOLATION_LEVEL_READ_COMMITTED
				Set to 'yes' to force the transaction isolation level to Read Committed if the data source supports it. Only one of the four transaction isolation levels should be set to ‘yes’. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API.

				CAP_ISOLATION_LEVEL_READ_UNCOMMITTED
				Set to 'yes' to force the transaction isolation level to Read Uncommitted if the data source supports it. Only one of the four transaction isolation levels should be set to ‘yes’. This capability can improve speed by reducing lock contention, but may result in partial or inconsistent data in query results. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API.

				CAP_ISOLATION_LEVEL_REPEATABLE_READS
				Set to 'yes' to force the transaction isolation level to Repeatable Reads if the data source supports it. Only one of the four transaction isolation levels should be set to ‘yes’. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API.

				CAP_ISOLATION_LEVEL_SERIALIZABLE
				Set to 'yes' to force the transaction isolation level to Serializable if the data source supports it. Only one of the four transaction isolation levels should be set to ‘yes’. This is a very conservative setting that may improve stability at the expense of performance. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API.

				CAP_ISOLATION_LEVEL_VIA_ODBC_API
				Set to 'yes' to force Tableau to set the transaction isolation level for the data source using the ODBC API. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API must be set to ‘yes’ when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to ‘yes’ . This capability is available in Tableau 8.0.

				CAP_ISOLATION_LEVEL_VIA_SQL
				Set to 'yes' to force Tableau to set the transaction isolation level for the data source using a SQL query. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API must be set to ‘yes’ when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to ‘yes’ . This capability is available in Tableau 8.0.

				CAP_MULTIPLE_CONNECTIONS_FROM_SAME_IP
				Set to 'no'  to prevent Tableau from creating more than one active connection to the database. This is a conservative setting that may increase stability at the expense of performance. This capability is available in Tableau 8.0.

				CAP_ODBC_BIND_DETECT_ALIAS_CASE_FOLDING
				Set to ‘yes’ to allow Tableau to detect and recover from an ODBC data source that reports the field names in a result set using only upper-case or lower-case characters, instead of the expected field names. This capability is available in Tableau 7.0.7.

				CAP_ODBC_BIND_FORCE_DATE_AS_CHAR
				Set to 'yes' to force the Tableau native ODBC protocol to bind date values as CHAR. This capability is available in Tableau 6.1.4.

				CAP_ODBC_BIND_FORCE_DATETIME_AS_CHAR
				Set to 'yes' to force the Tableau native ODBC protocol to bind datetime values as CHAR. This capability is available in Tableau 6.1.4.

				CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS
				Set to 'yes' to force the Tableau native ODBC protocol to use maximum-sized buffers (1K) for strings instead of the size described by metadata. This capability is available in Tableau 8.0.

				CAP_ODBC_BIND_FORCE_MEDIUM_STRING_BUFFERS
				Set to 'yes' to force the Tableau native ODBC protocol to use medium-sized buffers (1K) for strings instead of the size described by metadata. This capability is available in Tableau 8.0.

				CAP_ODBC_BIND_FORCE_SMALL_STRING_BUFFERS
				Set to 'yes' to force the Tableau native ODBC protocol to use small buffers for strings instead of the size described by metadata. This capability is available in Tableau 6.1.4.

				CAP_ODBC_BIND_SUPPRESS_COERCE_TO_STRING
				Set to 'yes' to prevent the Tableau native ODBC protocol from binding non-string data as strings (i.e. requesting driver conversion). This capability is available in Tableau 8.0.

				CAP_ODBC_BIND_SUPPRESS_INT64
				Set to 'yes' to prevent the Tableau native ODBC protocol from using 64-bit integers for large numeric data. This capability is available in Tableau 6.1.4.

				CAP_ODBC_BIND_SUPPRESS_PREFERRED_CHAR
				Set to 'yes' to prevent the Tableau native ODBC protocol from preferring a character type that differs from the driver default. This capability is available in Tableau 8.0.

				CAP_ODBC_BIND_SUPPRESS_PREFERRED_TYPES
				Set to 'yes' to prevent the Tableau native ODBC protocol from binding any data according to its preferred wire types. With this capability set, Tableau will only bind according to the data types described by the ODBC driver via metadata. This capability is available in Tableau 6.1.4.

				CAP_ODBC_BIND_SUPPRESS_WIDE_CHAR
				Set to 'yes' to prevent the Tableau native ODBC protocol from binding strings a WCHAR. Instead they will be bound as single-byte CHAR arrays, and processed locally for any UTF-8 characters contained within. This capability is available in Tableau 6.1.4.

				CAP_ODBC_CURSOR_DYNAMIC
				Set to 'yes' to force the Tableau native ODBC protocol to set the cursor type for all statements to Dynamic (scrollable, detects added/removed/modified rows). This capability is available in Tableau 6.1.4.

				CAP_ODBC_CURSOR_FORWARD_ONLY
				Set to 'yes' to force the Tableau native ODBC protocol to set the cursor type for all statements to Forward-only (non-scrollable). This capability is available in Tableau 6.1.4.

				CAP_ODBC_CURSOR_KEYSET_DRIVEN
				Set to 'yes' to force the Tableau native ODBC protocol to set the cursor type for all statements to Keyset-driven (scrollable, detects changes to values within a row). This capability is available in Tableau 6.1.4.

				CAP_ODBC_CURSOR_STATIC
				Set to 'yes' to force Tableau to set the cursor type for all statements to Static (scrollable, does not detect changes). This capability is available in Tableau 6.1.4.

				CAP_ODBC_ERROR_IGNORE_FALSE_ALARM
				Set to 'yes' to allow the Tableau native ODBC protocol to ignore SQL_ERROR conditions where SQLSTATE is '00000' (meaning "no error"). This capability is available in Tableau 6.1.4.

				CAP_ODBC_FETCH_BUFFERS_RESIZABLE
				Set to 'yes' to allow buffers to be reallocated after fetch to improve performance or handle data truncation. This capability is available in Tableau 8.0.

				CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED
				Set to 'yes' to ignore the width of a single row when computing the total rows to fetch. This capability is available in Tableau 8.0.

				CAP_ODBC_FETCH_BUFFERS_SIZE_MASSIVE
				Set to 'yes' to force the use of large buffers. If CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED is enabled, a fixed row count is used. This capability is available in Tableau 8.0.

				CAP_ODBC_FETCH_BUFFERS_SIZE_MEDIUM
				Set to 'yes' to force the use of medium-sized buffers. If CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED is enabled, a fixed row count is used. This capability is available in Tableau 8.0.

				CAP_ODBC_FETCH_BUFFERS_SIZE_SMALL
				Set to 'yes' to force the use of small buffers. If CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED is enabled, a fixed row count is used. This capability is available in Tableau 8.0.

				CAP_ODBC_FETCH_CONTINUE_ON_ERROR
				Set to 'yes' to allow the Tableau native ODBC protocol to continue resultset fetch despite errors (some data sources report warnings as errors). This capability is available in Tableau 6.1.4.

				CAP_ODBC_FETCH_ESTIMATE_ROW_COUNT
				Set to 'yes' to allow querying for an estimated or exact row count, which may be used to compute buffer sizes. This capability is available in Tableau 8.0.

				CAP_ODBC_FETCH_IGNORE_FRACTIONAL_SECONDS
				Set to 'yes' to allow the Tableau native ODBC protocol to ignore the fractional seconds component of a time value when fetching query result set data. This is useful when working with data sources that do not follow the ODBC specification for fractional seconds, which must be represented as billionths of a second. This capability is available in Tableau 7.0.8.

				CAP_ODBC_FETCH_MASSIVE_BUFFERS
				Set to 'yes' to force the Tableau native ODBC protocol to use large buffers instead of adapting to the resultset size. This capability is available in Tableau 6.1.4 through Tableau 7.0 only. 

				CAP_ODBC_FETCH_RESIZE_BUFFERS
				Set to 'yes' to allow the Tableau native ODBC protocol to automatically resize buffers and fetch again if data truncation occurred. This capability is available in Tableau 6.1.4 through 7.0 only.

				CAP_ODBC_FORCE_SINGLE_ROW_BINDING
				Set to 'yes' to force the Tableau native ODBC protocol to use a single row for result set transfers instead of the more efficient bulk-fetch. This capability is available in Tableau 6.1.4.

				CAP_ODBC_IMPORT_ERASE_BUFFERS
				Set to 'yes' to reset the contents of data buffers before fetching each block. This capability is available in Tableau 8.0.

				CAP_ODBC_IMPORT_TRUST_METADATA_CONTAINSNULL
				Set to 'yes' to use efficient import if Tableau can trust the result set metadata representing the absence of NULLs. This capability is available in Tableau 8.0.

				CAP_ODBC_METADATA_FORCE_LENGTH_AS_PRECISION
				Set to 'yes' to force the Tableau native ODBC protocol to use the column "length" as the numeric precision. This is an uncommon setting. This capability is available in Tableau 8.0.

				CAP_ODBC_METADATA_FORCE_NUM_PREC_RADIX_10
				Set to 'yes' to force the Tableau native ODBC protocol to assume the numeric precision is reported in base-10 digits. This is an uncommon setting. This capability is available in Tableau 8.0.

				CAP_ODBC_METADATA_FORCE_UTF8_IDENTIFIERS
				Set to 'yes' to force the protocol to treat identifiers as UTF-8 when communicating with the driver. This capability is available in Tableau 8.0.

				CAP_ODBC_METADATA_STRING_LENGTH_UNKNOWN
				Set to ‘yes’ to prevent Tableau from allocating memory based on the driver-reported string length, which may not be known or reported properly. Instead, Tableau will use a fixed-sized string length, and will reallocate as needed to handle string data that is too large for the fixed-size buffer. This capability is available in Tableau 7.0.7.

				CAP_ODBC_METADATA_STRING_TRUST_OCTET_LENGTH
				Set to 'yes' to use the octet length reported by the driver for strings instead of computing it from the number of characters. This capability is available in Tableau 8.0.

				CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY
				Set to ‘yes’ to prevent Tableau from executing a query as a means of reading metadata. While Tableau typically includes a row-limiting clause in such metadata queries (e.g., ‘LIMIT 0’, or ‘WHERE 1=0’), this may not help when used with a Custom SQL connection for database systems with poor query optimizers. Note that this capability may prevent Tableau from determining the connection metadata properly.  This capability is available in Tableau 7.0.7.

				CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY
				Set to ‘yes’ to prevent Tableau from using a prepared query as a means of reading metadata. A prepared query is often the fastest way to accurately read metadata. However, not all database systems are capable of reporting metadata for a prepared query without actually executing the query. Note that certain metadata -- for example from connections using Custom SQL-- cannot be retrieved if this capability and CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY are both set. This capability is available in Tableau 7.0.7.

				CAP_ODBC_METADATA_SUPPRESS_SELECT_STAR
				Set to 'yes' to prevent reading metadata using a 'select *' query. This capability is available in Tableau 8.0.

				CAP_ODBC_METADATA_SUPPRESS_SQLCOLUMNS_API
				Set to ‘yes’ to prevent Tableau from using older, less accurate API for reading metadata from ODBC data sources. Setting this capability allows Tableau to read metadata by issuing a full ‘select *’ query, which is expensive but may enable connectivity for extremely limited or unstable data sources. This capability is available in Tableau 7.0.7.

				CAP_ODBC_METADATA_SUPPRESS_SQLFOREIGNKEYS_API
				Set to ‘yes’ to prevent Tableau from attempting to read metadata describing foreign key constraints. Despite the simple nature of this ODBC API, some drivers may have unstable behavior or produce inaccurate results. Setting this capability may force Tableau to generate less efficient queries involving multi-table joins. This capability is available in Tableau 7.0.7.

				CAP_ODBC_REBIND_SKIP_UNBIND
				Set to 'yes' to force the Tableau native ODBC protocol to rebind a column directly and skip unbinding, which reduces ODBC API calls when resizing buffers to refetch truncated data. T This capability is available in Tableau 6.1.4.

				CAP_ODBC_TRIM_VARCHAR_PADDING
				Set to 'yes' to force the Tableau native ODBC protocol to trim trailing whitespace from VARCHAR columns which the driver has erroneously padded. This capability is available in Tableau 6.1.4.

				CAP_ODBC_UNBIND_AUTO
				Set to 'yes' to force the Tableau native ODBC protocol to unbind and deallocate columns automatically, which can reduce ODBC API calls. This capability is available in Tableau 6.1.4.

				CAP_ODBC_UNBIND_BATCH
				Set to 'yes' to force the Tableau native ODBC protocol to unbind and deallocate columns in a single batch operation, which can reduce ODBC API calls. This capability is available in Tableau 6.1.4.

				CAP_ODBC_UNBIND_EACH
				Set to 'yes' to force the Tableau native ODBC protocol to unbind and deallocate columns individually, which may improve stability. This is the default behavior for unbinding columns. This capability is available in Tableau 6.1.4.

				CAP_ODBC_USE_NATIVE_PROTOCOL
				Set to 'yes' to force Tableau to use its native ODBC protocol instead of the OLE DB bridge to ODBC. This must be enabled to use any other CAP_ODBC_* capabilities. This capability is available in Tableau 6.1.4.

				CAP_QUERY_BOOLEXPR_TO_INTEXPR
				Set to 'yes' if Tableau must coerce any boolean expressions to an integer value in order include in a resultset.

				CAP_QUERY_FROM_REQUIRES_ALIAS
				Set to 'yes' if the FROM clause must provide an alias for the given table. This capability is available in Tableau 6.1.4.

				CAP_QUERY_GROUP_ALLOW_DUPLICATES
				Set to 'no' if SQL queries cannot contain duplicate expressions in the GROUP BY clause (this is uncommon). This capability is available in Tableau 6.1.4.

				CAP_QUERY_GROUP_BY_ALIAS
				Set to 'yes' if SQL queries with aggregations can reference the grouping columns by their corresponding alias in the SELECT list, e.g. GROUP BY "none_ShipCountry_nk".

				CAP_QUERY_GROUP_BY_DEGREE
				Set to 'yes' if SQL queries with aggregations can reference the grouping columns by the ordinal position of each column, e.g. GROUP BY 2, 5. See also: CAP_QUERY_SORT_BY_DEGREE

				CAP_QUERY_HAVING_REQUIRES_GROUP_BY
				Set to 'yes' if Tableau must use an artificial grouping field for any query which has a HAVING clause but no grouping columns.

				CAP_QUERY_HAVING_UNSUPPORTED
				Set to 'yes' if the SQL syntax for HAVING is unsupported. Tableau may be able to work around this using subqueries. See also: CAP_QUERY_SUBQUERIES. This capability is available in Tableau 6.1.4.

				CAP_QUERY_INCLUDE_GROUP_BY_COLUMNS_IN_SELECT
				Set to 'yes' to require all GROUP BY expressions to also appear in the SELECT expression list. This capability is available in Tableau 8.0.

				CAP_QUERY_JOIN_ACROSS_SCHEMAS
				Set to 'yes' if SQL queries can express joins between tables located in different schemas.

				CAP_QUERY_JOIN_REQUIRES_SCOPE
				Set to 'yes' if SQL queries must scope each join clause within parentheses to ensure a proper order of evaluation.

				CAP_QUERY_NULL_REQUIRES_CAST
				Set to 'yes' if the data source requires that all NULL literals are cast to an explicit data type. This capability is available in Tableau 6.0.6.

				CAP_QUERY_SELECT_ALIASES_SORTED
				Set to 'yes' if Tableau must impose a deterministic order on the SELECT expressions (sorted by alias) to ensure that query results can be properly matched with each field in the Tableau visualization. This is only required for data sources which do not preserve the aliases of the SELECT expressions when returning metadata with the query results. This capability is available in Tableau 6.1.4.

				CAP_QUERY_SORT_BY_DEGREE
				Set to 'yes' if SQL queries can reference the sorting columns by the ordinal position of each column, e.g. ORDER BY 2, 5. See also: CAP_QUERY_GROUP_BY_DEGREE. This capability is available in Tableau 6.0.6.

				CAP_QUERY_SUBQUERIES
				Set to 'yes' if the data source supports subqueries.

				CAP_QUERY_SUBQUERIES_WITH_TOP
				Set to 'yes' if the data source supports a TOP or LIMIT row-limiting clause within a subquery.

				CAP_QUERY_SUBQUERY_QUERY_CONTEXT
				Set to 'yes' to force Tableau to use a subquery for context filters instead of a temporary table or locally cached results. This capability is available in Tableau 6.1.4.

				CAP_QUERY_TOP_N
				Set to 'yes' if the data source supports any form of row-limiting clause. The exact forms supported are described below.

				CAP_QUERY_TOPSTYLE_LIMIT
				Set to 'yes' if the data source uses LIMIT as the row-limiting clause.

				CAP_QUERY_TOPSTYLE_ROWNUM
				Set to 'yes' if the data source supports an Oracle-style filter on ROWNUM as the row-limiting clause.

				CAP_QUERY_TOPSTYLE_TOP
				Set to 'yes' if the data source uses TOP as the row-limiting clause.

				CAP_SELECT_INTO
				Set to 'yes' if Tableau can create a table on the fly from the resultset of another query. See also: CAP_CREATE_TEMP_TABLES.

				CAP_SELECT_TOP_INTO
				Set to 'yes' if Tableau can use a TOP or LIMIT row-limiting clause when creating a table from a query resultset.

				CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API
				Set to 'yes' to allow Tableau to set the transaction isolation level using the ODBC API. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API must be set to ‘yes’ when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to ‘yes’. This capability is available in Tableau 8.0.

				CAP_SET_ISOLATION_LEVEL_VIA_SQL  
				 Set to 'yes' to allow Tableau to set the transaction isolation level using standard SQL syntax. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API must be set to ‘yes’ when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to ‘yes’. This capability is available in Tableau 8.0.

				CAP_SKIP_CONNECT_VALIDATION
				Set to 'yes' to prevent Tableau from validating the structure of a user-defined multi-table join or Custom SQL connection. This capability is available through Tableau 7.0 only.

				CAP_SUPPRESS_DISCOVERY_QUERIES
				Set to 'yes' to prevent Tableau from detecting the supported SQL syntax for a variety of clauses.

				CAP_SUPPRESS_DISPLAY_LIMITATIONS
				Set to 'yes' to suppress displaying any warnings about limitations for this data source.

 



	Appendix C – List of ODBC/SQL Customizations



	These customizations represent the portions of the ODBC and SQL standards which the driver claims to support. The names of these customizations come from the identifiers used as parameters to SQLGetInfo. Refer to the MSDN documentation and the source code header file sqlext.h for the numeric and bit-mask values associated with each customization.



	SQLGetInfo Long-Integer Values



				SQL_ODBC_INTERFACE_CONFORMANCE
				Currently unused.

				SQL_SQL_CONFORMANCE
				Integer bitmask. Defines the level which the data source conforms to the SQL standard: '1' for entry-level SQL-92 conformance, '2' for FIPS 127-2 transitional, '4' for Intermediate and '8' for Full conformance.

				SQL_CATALOG_USAGE
				Integer bitmask. Defines the SQL statements in which a catalog identifier can be used.

				SQL_SCHEMA_USAGE
				Integer bitmask. Defines the SQL statements in which a schema identifier can be used.

				SQL_AGGREGATE_FUNCTIONS
				Integer bitmask. Defines which standard SQL aggregation forms are supported.

				SQL_NUMERIC_FUNCTIONS
				Integer bitmask. Defines which SQL scalar numeric functions are supported.

				SQL_STRING_FUNCTIONS
				Integer bitmask. Defines which SQL scalar string functions are supported.

				SQL_TIMEDATE_FUNCTIONS
				Integer bitmask. Defines which SQL scalar date / time functions are supported.

				SQL_TIMEDATE_ADD_INTERVALS
				Integer bitmask. Defines which date / time intervals are supported with the TIMESTAMPADD scalar function.

				SQL_TIMEDATE_DIFF_INTERVALS
				Integer bitmask. Defines which date / time intervals are supported with the TIMESTAMPDIFF scalar function.

				SQL_DATETIME_LITERALS
				Integer bitmask. Defines which SQL-92 literals are supported for representing DATE / TIME constants and INTERVALs.

				SQL_SYSTEM_FUNCTIONS
				Integer bitmask. Defines support for special SQL system scalar functions: IFNULL, DBNAME and USERNAME.

				SQL_SQL92_VALUE_EXPRESSIONS
				Integer bitmask. Defines which logical functions are supported for testing and manipulating values: CASE, CAST and NULLIF.

				SQL_SQL92_NUMERIC_VALUE_FUNCTIONS
				Integer bitmask. Defines which functions can produce a numeric value from non-numeric data, including: EXTRACT (for date / time part extraction), CHAR_LENGTH, CHARACTER_LENGTH and POSITION(.. IN ..).

				SQL_SQL92_STRING_FUNCTIONS
				Integer bitmask. Defines which string manipulation functions are supported.

				SQL_SQL92_DATETIME_FUNCTIONS
				Integer bitmask. Defines which date / time manipulation functions are supported for determining the current date, time or timestamp.

				SQL_OJ_CAPABILITIES
				Integer bitmask. Defines which type of outer joins are supported.

				SQL_SQL92_RELATIONAL_JOIN_OPERATORS
				Integer bitmask. Defines which types of JOIN operators are supported, e.g. INNER, OUTER.

				SQL_SQL92_PREDICATES
				Integer bitmask. Defines which predicates are supported for logical tests of values, e.g. IS NULL, LIKE, IN.

				SQL_CONVERT_FUNCTIONS
				Integer bitmask. Defines which ODBC scalar functions are supported for CASTing or CONVERTing one data type to another.

				SQL_CONVERT_TINYINT
				Integer bitmask. Determines which other data types that this named type can be converted to using the ODBC scalar function CONVERT.

				SQL_CONVERT_SMALLINT
				Integer bitmask. Same as above.

				SQL_CONVERT_INTEGER
				Integer bitmask. Same as above.

				SQL_CONVERT_BIGINT
				Integer bitmask. Same as above.

				SQL_CONVERT_REAL
				Integer bitmask. Same as above.

				SQL_CONVERT_FLOAT
				Integer bitmask. Same as above.

				SQL_CONVERT_DOUBLE
				Integer bitmask. Same as above.

				SQL_CONVERT_CHAR
				Integer bitmask. Same as above.

				SQL_CONVERT_VARCHAR
				Integer bitmask. Same as above.

				SQL_CONVERT_LONGVARCHAR
				Integer bitmask. Same as above.

				SQL_CONVERT_DECIMAL
				Integer bitmask. Same as above.

				SQL_CONVERT_NUMERIC
				Integer bitmask. Same as above.

				SQL_CONVERT_BIT
				Integer bitmask. Same as above.

				SQL_CONVERT_GUID
				Integer bitmask. Same as above.

				SQL_CONVERT_BINARY
				Integer bitmask. Same as above.

				SQL_CONVERT_VARBINARY
				Integer bitmask. Same as above.

				SQL_CONVERT_LONGVARBINARY
				Integer bitmask. Same as above.

				SQL_CONVERT_DATE
				Integer bitmask. Same as above.

				SQL_CONVERT_TIME
				Integer bitmask. Same as above.

				SQL_CONVERT_TIMESTAMP
				Integer bitmask. Same as above.

				SQL_CONVERT_INTERVAL_DAY_TIME
				Integer bitmask. Same as above.





	SQLGetInfo Short-Integer Values



				SQL_MAX_IDENTIFIER_LEN
				Integer value. Defines the maximum number of characters that can be used in an identifier. Tableau leaves room for one extra character as the string terminator.

				SQL_QUOTED_IDENTIFIER_CASE
				Integer bitmask. Currently unused.



	SQLGetInfo String Values



				SQL_COLUMN_ALIAS
				Boolean value. "Y" if the data source supports using aliases for columns listed in the SELECT clause.

				SQL_IDENTIFIER_QUOTE_CHAR
				String value. Indicates the character which can be used for quoting identifiers. Because the connection customization is an XML document, any entities must properly be encoded. For example, double-quotes will be '&quot;'. Additionally this character is assumed to work as the opening and closing character around identifiers, so some data sources which require '[]' to enclose identifiers will not be supported.

				SQL_CATALOG_NAME_SEPARATOR
				Character value. Indicates the separator character to use between identifiers when qualifying them with a catalog, schema or table name. This is typically the period character.

				SQL_SPECIAL_CHARACTERS
				String value. Indicates the special characters which are allowed in identifier strings.

				SQL_CATALOG_TERM
				String value. This is the descriptive term for a database catalog, which appears in the Tableau connection dialog for this ODBC data source.

				SQL_SCHEMA_TERM
				String value. This is the descriptive term for a database schema, which appears in the Tableau connection dialog for this ODBC data source.

				SQL_TABLE_TERM
				String value. This is the descriptive term for a database table, which appears in the Tableau connection dialog for this ODBC data source.

				SQL_DRIVER_NAME
				String value. This is the name of the database ODBC driver.

				SQL_DRIVER_VER
				String value. This is the version number of the ODBC driver.

				SQL_DRIVER_ODBC_VER
				String value. This is the version of the ODBC API which the driver supports.

				SQL_ODBC_VER
				String value. This is the version of ODBC which the Windows ODBC Driver Manager supports. This should not need to be customized.

				SQL_DBMS_NAME
				String value. This is the name of the database vendor.

				SQL_DBMS_VER
				String value. This is the version of the database system.

				SQL_SERVER_NAME
				String value. This is the named network address of the database server.

				SQL_USER_NAME
				String value. This is the name of the currently authenticated user.



	
	Appendix D – Sample SQLite Customizations



	From the examples in this article, the fully customized SQLite data source file will look like the following:


<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='genericodbc.40571.475277974532' inline='true' version='7.10' xmlns:user='http://www.tableausoftware.com/xml/user' >
  <!-- build 6000.0.0000.0000                 -->
  <connection class='genericodbc' dbname='C:\Northwind.sl3' odbc-connect-string-extras=
'StepAPI=0;NoTXN=0;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0'
 odbc-dbms-name='SQLite' odbc-driver='SQLite3 ODBC Driver' >
    <relation name='Orders' table='[Orders]' type='table' />
        <connection-customization class='genericodbc' enabled='true' version='7.10'>
      <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_HAVING_REQUIRES_GROUP_BY' 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_QUERY_TOPSTYLE_LIMIT' value='yes' />
        <customization name='CAP_QUERY_TOPSTYLE_ROWNUM' value='no' />
        <customization name='CAP_QUERY_TOPSTYLE_TOP' value='no' />
        <customization name='CAP_QUERY_TOP_N' value='yes' />
        <customization name='CAP_SELECT_INTO' value='no' />
        <customization name='CAP_SELECT_TOP_INTO' value='yes' />
        <customization name='CAP_SKIP_CONNECT_VALIDATION' value='no' />
        <customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='no' />
        <customization name='SQL_CATALOG_NAME_SEPARATOR' value='.' />
        <customization name='SQL_CATALOG_TERM' value='' />
        <customization name='SQL_CATALOG_USAGE' value='0' />
        <customization name='SQL_COLUMN_ALIAS' value='Y' />
        <customization name='SQL_CONVERT_FUNCTIONS' value='0' />
        <customization name='SQL_DBMS_NAME' value='SQLite' />
        <customization name='SQL_DBMS_VER' value='3.7.2' />
        <customization name='SQL_DRIVER_ODBC_VER' value='03.00' />
        <customization name='SQL_DRIVER_VER' value='0.87' />
        <customization name='SQL_IDENTIFIER_QUOTE_CHAR' value='&quot;' />
        <customization name='SQL_MAX_IDENTIFIER_LEN' value='255' />
        <customization name='SQL_NUMERIC_FUNCTIONS' value='0' />
        <customization name='SQL_ODBC_INTERFACE_CONFORMANCE' value='1' />
        <customization name='SQL_ODBC_VER' value='03.80.0000' />
        <customization name='SQL_OJ_CAPABILITIES' value='0' />
        <customization name='SQL_QUOTED_IDENTIFIER_CASE' value='3' />
        <customization name='SQL_SCHEMA_TERM' value='' />
        <customization name='SQL_SCHEMA_USAGE' value='0' />
        <customization name='SQL_SPECIAL_CHARACTERS' value='' />
        <customization name='SQL_STRING_FUNCTIONS' value='0' />
        <customization name='SQL_SYSTEM_FUNCTIONS' value='0' />
        <customization name='SQL_TABLE_TERM' value='TABLE' />
        <customization name='SQL_TIMEDATE_ADD_INTERVALS' value='0' />
        <customization name='SQL_TIMEDATE_DIFF_INTERVALS' value='0' />
        <customization name='SQL_TIMEDATE_FUNCTIONS' value='0' />
      </customizations>
    </connection-customization>
  </connection>
  <column datatype='integer' name='[Number of Records]' role='measure' type='quantitative' user:auto-column='numrec' >
    <calculation class='tableau' formula='1' />
  </column>
  <column datatype='string' name='[ShipPostalCode]' role='dimension' semantic-role='[ZipCode].[Name]' type='nominal' >
  </column>
  <layout dim-ordering='alphabetic' dim-percentage='0.5' measure-ordering='alphabetic' measure-percentage='0.4' show-structure='true' />
  <semantic-values>
    <semantic-value key='[Country].[Name]' value='&quot;UNITED STATES&quot;' />
  </semantic-values>
</datasource>



	Appendix E – 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> 




                                    Alternate Search Terms: odbc, data connection, data source, sql
                                





                                        
 Did this article resolve the issue?




                            

                        		
                        
                            
 
             
                                



 
                      
                                    
                               
                                    

                                        

                                        

                                        

                                            

                                                
ATTACHMENTS

                                                

                                            


 
                                        


 
                                
         
                                


                                    

                                        
                                        

                                            
 
                                                 
                                                    

                                                    
 
                                                    
 Search Knowledge Base