Create a Tableau Dialect Definition File

In this section

• Overview
• Create a TDD file
  • Dialect tag
  • Supported-aggregations element
  • Function-map element
  • Join Support:
  • Boolean Support:
  • String Literal Support
  • Date Literal Support
  • Temporary Table Support:
  • Identifier Mangling:

Overview

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.

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.

• aggregation element An aggregation element has one required attribute, value, which specifies a single aggregation or truncation. For a list, see supported aggregations.

Function-map element

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

• function element Each function element defines a single function. It has a few required attributes, contains a formula element (and in some cases, an unagg-formula element) and any number of argument elements. For a list of supported functions, see the FullFunctionMap.tdd file sample.

• date-function element The date-function element is a specialized variant of function. In addition to the base formula, you can specify one or more datepart formulas, which are used instead of the generic formula when available. The function name must be one of these: DATEADD, DATEDIFF, DATENAME, DATEPARSE, DATEPART, DATETRUNC. Like function, date-function requires name and return-type, but unlike function, group is not required.

  Example: DATEPART without custom start of week

        ...
        <date-function name='DATEPART' return-type='int'>
          <formula>CAST(TRUNC(EXTRACT(%1 FROM %2)) AS INTEGER)</formula>
          <formula part='weekday'>(1 + CAST(EXTRACT(DOW FROM %2) AS INTEGER))</formula>
          <formula part='week'>CAST(FLOOR((7 + EXTRACT(DOY FROM %2) - 1 + EXTRACT(DOW FROM DATE_TRUNC(&apos;YEAR&apos;, %2))) / 7) AS INTEGER)</formula>
          <argument type='localstr' />
          <argument type='datetime' />
        </date-function>
        ...
  

  Here the first argument (%1) is the value from the <date-part-group> in the dialect file. A date-part-group can apply to one or more date functions, denoted by date-function child elements. If none are specified, the group acts as the default. The name attribute specifies a Tableau date part, while the value attribute contains the date part string literal to use in corresponding date functions.

        ...
          <date-part-group>
            <date-function name='DATEPART' />
            <part name='year' value='YEAR' />
            <part name='quarter' value='QUARTER' />
            <part name='month' value='MONTH' />
            <part name='week' value='WEEK' />
            <part name='weekday' value='DOW' />
            <part name='dayofyear' value='DOY' />
            <part name='day' value='DAY' />
            <part name='hour' value='HOUR' />
            <part name='minute' value='MINUTE' />
            <part name='second' value='SECOND' />
          </date-part-group>
        ...
  

  A single date function can have multiple overloaded functions with different parameters.
  To support Tableau’s Custom Start of Week functionality each of the following: DATEDIFF, DATENAME, DATEPART, DATETRUNC need to also have an overloaded form with an additional <argument type='localstr' />.
  Example: DATEPART for Custom Start of Week

        ...
          <date-function name='DATEPART' return-type='int'>
          <formula part='week'>CAST(FLOOR((7 + EXTRACT(DOY FROM %2) - 1 + (CAST(7 + EXTRACT(DOW FROM DATE_TRUNC(&apos;YEAR&apos;, %2)) - %3 AS BIGINT) % 7)) / 7) AS INTEGER)</formula>
          <argument type='localstr' />
          <argument type='datetime' />
          <argument type='localstr' />
        </date-function>
        ...
  

  DATEPARSE function The DATEPARSE function is used to define which parts of your field are which parts of a date. It uses the icu-date-token-map instead of the date-part-group formula used primarily by the other date function. As a modifier for the date string to be converted into a date, it uses the date-literal-escape. As an example, for the DATEPARSE function with arguments %1 and %2, the string values for %1 are defined by icu-token-map and the string values for %2 are defined by date-literal-escape.

  Datetime considerations When writing datetime functions, use the default date 1900-01-01 where appropriate to standardize with other Tableau connectors.

• remove-function element The remove-function is used to remove existing functions in a function map without overriding them. It requires only a name attribute and doesn’t require you to specify any formula.

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

• formula element The formula element is a required child of function and date-function elements and specifies the function’s formula using standard Tableau string substitution syntax for arguments (%1, %2, and so on). You can use the optional part attribute to specify a formula for a specific date part.

• unagg-formula element The unagg-formula (unaggregated formula) element is an optional child of function elements. It should be specified only if the function is part of the aggregate group. Unaggregated formulas should represent a reasonable way of expressing the aggregate of a single value. For example, for average, it should be the value itself. For variance, it should be 0.

• argument element The argument element is an optional child of all three types of function elements. It contains a single attribute, type, which specifies the abbreviated argument type. Arguments must be listed in the correct order. Allowable types: none, bool, real, int, str, datetime, date, localstr, null, error, any, tuple, spatial, localreal, localint. Allowable date parts: year, quarter, month, dayofyear, day, weekday, week, hour, minute, second.

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:

• Keyword Uses DISTINCT keyword for comparison. Logic: (lhs IS [NOT ]DISTINCT FROM rhs).
• Operator Uses <=> for comparisons. Logic:([NOT] (lhs <=> rhs))
• Formula By default formula is used. In this case format is distinct checks that the value for LHS and RHS is not null. Logic :((lhs [!]= rhs) OR[AND] (lhs IS [NOT] NULL AND[OR] rhs IS [NOT] NULL))
• NoNullCheck Does not check that the value of LHS and RHS is null. Logic: (lhs [!]= rhs) . Note: A known limitation is that using this value will cause Tableau to give the wrong answer in some scenarios if the column contains null. This is not recommended unless required by the underlying database.

  <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 data source. 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 are used when a boolean is used as a value. The default values are 1 and 0.
Example usage: SELECT 1 AS literalBool
Predicates are 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 capability is set to yes. The value for this property can be:

• TrueFirst This is the default case. Logic: CASE WHEN %1 THEN 1 WHEN NOT %1 THEN 0 ELSE NULL END
• FalseFirst False case is used first. Logic: CASE WHEN NOT %1 THEN 0 WHEN %1 THEN 1 ELSE NULL END

    <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 correspond to a certain type of table. Predicates:

• GlobalTemp
• LocalTemp
• AnyTemp
• NoTemp
  You should also be able to use the following string substitution tokens along with the predicate:
  • %n - table name
  • %f - formatted column list This function is available when the CAP_CREATE_TEMP_TABLES capability is set to yes.

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

Identifier Mangling:

The identifier generation can include restrictions by character, casing and length. This is primarily used in table name mangling.

id-allowed-characters
Comprehensive list of characters which can be used in identifiers. Any character that isn’t allowed is replaced with _. There is no restriction by default.

id-case
Normalize the identifier casing. If this element is omitted the identifier casing is not changed. The value for this property can be:

• Upper Database expects uppercase identifiers
• Lower Database expects lowercase identifiers

id-max-length
Restrict the maximum identifier length. There is no limit by default.

Example:

    <id-allowed-characters value='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_' />
    <id-case value='Lower' />
    <id-max-length value='64' />