Create a Tableau Dialect Definition File


A Tableau Dialect Definition file (.tdd) maps Tableau’s query language to a database’s SQL. This is an XML file with a .tdd filename extension, and is one of the main components of a Tableau connector.

You should create a dialect definition file whenever you need to make changes to an existing Tableau dialect or define an entirely new dialect. If your connector uses the same SQL dialect as the connector it’s based on, such as PostgreSQL, then a new TDD file isn’t necessary.

Create a TDD file

To get started quickly, you can copy the sample dialect.tdd file from the postgres_odbc or postgres_jdbc folder and use the copy to make your modifications.

The XML schema for the plugin XML files is validated using XSD files. The dialect(.tdd) file is validated using this XSD file.

Tableau searches for a TDD file in the location specified by the connector manifest XML file. The TDD format breaks down as follows:

Dialect tag

The dialect tag serves as the root for the document. For example:

<dialect
   name='CustomDialect'
   class='postgres'
   version='20.1'>

The dialect tag has several required attributes and a couple of optional ones.

Attribute Required Description
name Y Dialect name. Used for dependency lookup.
class Y Should match the plug-in’s class, as defined in the manifest.
base N Specifies a base dialect to build upon. If a certain property or function isn’t defined in a dialect definition file, the connector will fall back to its base dialect’s behavior (assuming a value for base is defined), and SQL-92 default behavior (if a value for base is not defined). Important: This must be a valid, existing dialect. If the specified base does not exist, the connector will fail to load. For a list of bases, see Dialect base classes.
dialect-version N Indicates the minimum database version applicable to the TDD file. For example, if you’re adding a function definition that wasn’t implemented until FooDB 3.0, then set dialect-version=’3.0’.
version Y Must match the current Tableau version in the format YY.Q; for example, “20.1”.

Supported-aggregations element

The supported-aggregations element contains a list of aggregations and date truncation levels supported by the database, represented by one or more aggregation elements.

Function-map element

The function-map element has no attributes and contains any number of function, date-function, and remove-function elements.

Attribute Required Description
name Y Indicates the name of the function being added or overridden.
group Y Contains one or more (comma-separated) groups that this function belongs to. Allowable types: aggregate, cast, date, logical, numeric, operator, passthru, special, string, system.
return-type Y Indicates the return type of the function. For a list of allowable types, see argument-element below.

Each of the function, date-function, and remove-function elements can contain the following:

Join Support:

Supported Joins
Some database does not support all types of join.<supported-joins> enumerates list of supported join types.

    ...
        <supported-joins>
          <part name='Inner' />
          <part name='Left' />
          <part name='Right' />
          <part name='Full' />
          <part name='Cross' />
        </supported-joins>
      </sql-format>
      </dialect>
    ...

Format is distinct
<format-is-distinct> Defines a strategy for determining whether two values are distinct.
The value for this property can be:

  <format-is-distinct value='Operator' />

Join Capabilities
Join usage is also defined by capabilities in the manifest file. See the Query section for join relation capabilities here.

Disable Join Modification
Tableau can modify join types to make queries faster. If you use OLAP cubes for your database and want the same query as the one that is present in the cube follow this step to get the unoptimized queries:
In the “Data Source” page, “Data” menu, use the option to “Convert to Custom SQL”. This will prevent Tableau from modifying the join type.

Data Blending
When using data blending, Tableau creates a left join between the primary and secondary datasource. Data blending can only be a LEFT join, which means the primary table should contain all possible values.

Boolean Support:

Some databases need to customize boolean support functions. A common case is when a database lacks native boolean support.
format-true and format-false are used in predicate statements.
format-true

   <format-true value='(1=1)' />

format-false

  <format-false value='(1=0)' />

Note: Changes in Tableau 2020.3

With the release of Tableau 2020.3, format-true and format-false will have two parameters, literal and predicate.
Literals is used when a boolean is used as a value. The default values are 1 and 0.
Example usage: SELECT 1 AS literalBool
Predicates is used when a boolean is used as a predicate. The default values are (1=1) and (1=0).
Example usage: SELECT something AS test WHERE (1=1)
value attribute is used as predicate for backwards compatability
Example:

    <format-true literal='TRUE' predicate='TRUE' />
    <format-false literal='FALSE' predicate='FALSE' />

format-bool-as-value
Used in CASE statements. Determines whether the true or false case is used first. The function is only used when the CAP_QUERY_BOOLEXPR_TO_INTEXPR capiblity is set to yes. The value for this property can be:

    <format-bool-as-value value='TrueFirst' />

Boolean Capabilities

Boolean usage is also defined by capabilities in the manifest file. <br> See the Query section for boolean capabilities here for more details.

TDVT Coverage
The following TDVT tests check that the boolean functionality is working as expected for a connector. logical.bool exprtests\standard\setup.logical.bool.txt and logical exprtests\standard\setup.logical.txt. See this for more details.

String Literal Support

You have some control over what characters are escaped in string literals by changing the value of format-string-literal. Standard escapes only single quotes and is the default, Extended also escapes \', \\, \x0A, \x0D, \t, \b, and \f

Example:

    <format-string-literal value='Standard'/>

Unicode Prefix
Some databases require a prefix for unicode literals. Starting in 2020.3, you can define a prefix for string literals takes another parameter called unicode-prefix that defines such a prefix.

Example:

   <format-string-literal value='Standard' unicode-prefix='_utf16' />

Date Literal Support

Date and Datetime formatting elements take a formula, typically used to cast the string literal to a date type, and a date string format.

The formula attribute uses the token ‘%1’ to insert the formatted date string. The format attribute describes the database’s accepted string format for date literals, using ICU Date/Time format syntax.

Example:

    <format-date-literal formula="(DATE '%1')"  format='yyyy-MM-dd' />
    <format-datetime-literal formula="(TIMESTAMP '%1')" format='yyyy-MM-dd HH:mm:ss.SSS' />

Temporary Table Support:

format-create-table
This function uses piece-by-piece formulas for creating a table. Predicates can be used with tokens that only corresponds to a certain type of table. Predicates:

    <format-create-table>
        <formula>CREATE </formula>
        <formula predicate='GlobalTemp'>GLOBAL </formula>
        <formula predicate='LocalTemp'>LOCAL </formula>
        <formula predicate='AnyTemp'>TEMPORARY </formula>
        <formula>TABLE %n (</formula>
        <formula>%f</formula>
        <formula>)</formula>
        <formula predicate='AnyTemp'> ON COMMIT PRESERVE ROWS</formula>
      </format-create-table>

format-select
This function uses a piece-by-piece formula for defining a SELECT statement. Here, we can define the clause used in SELECT statement.
the Into clauses in the SELECT statement creates a new table. <format-select> will help you define how your TEMP table is created when using an INTO clause. INTO clause is only available when the CAP_SELECT_INTO capability is set to yes.

      <format-select>
        <part name='Into' value='CREATE GLOBAL TEMPORARY TABLE %1 ON COMMIT PRESERVE ROWS AS' />
        <part name='Top' value='SELECT * FROM (' />
        <part name='Select' value='SELECT %1' />
        <part name='From' value='FROM %1' />
        <part name='Where' value='WHERE %1' />
        ...
      </format-select> />

format-drop-table
This function defines the format for dropping a table. %1 is the table name. Each formula is executed as a separate statement.
This function is only used when CAP_TEMP_TABLES_NOT_SESSION_SCOPED capability is set to yes.

    <format-drop-table>
      <formula>TRUNCATE TABLE %1</formula>
      <formula>DROP TABLE %1</formula>
    </format-drop-table>

Temporary Table Capabilities

Temporary table usage is also defined by capabilities in the manifest file. See the temp table capabilities here for more details.