In this section
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.
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:
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.
|name||Y||Dialect name. Used for dependency lookup.|
|class||Y||Should match the plugin’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”.|
The supported-aggregations element contains a list of aggregations and date truncation levels supported by the database, represented by one or more aggregation elements.
The function-map element has no attributes and contains any number of function, date-function, and remove-function elements.
|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.|
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('YEAR', %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('YEAR', %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.
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:
DISTINCTkeyword for comparison. Logic:
(lhs IS [NOT ]DISTINCT FROM rhs).
<=>for comparisons. Logic:
([NOT] (lhs <=> rhs))
((lhs [!]= rhs) OR[AND] (lhs IS [NOT] NULL AND[OR] rhs IS [NOT] NULL))
(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 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.
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.
Some databases need to customize boolean support functions. A common case is when a database lacks native boolean support.
format-false are used in predicate statements.
<format-true value='(1=1)' />
<format-false value='(1=0)' />
Note: Changes in Tableau 2020.3
With the release of Tableau 2020.3,
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.
SELECT 1 AS literalBool
Predicates are used when a boolean is used as a predicate. The default values are
SELECT something AS test WHERE (1=1)
value attribute is used as
predicate for backwards compatability
<format-true literal='TRUE' predicate='TRUE' />
<format-false literal='FALSE' predicate='FALSE' />
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:
CASE WHEN %1 THEN 1 WHEN NOT %1 THEN 0 ELSE NULL END
CASE WHEN NOT %1 THEN 0 WHEN %1 THEN 1 ELSE NULL END
<format-bool-as-value value='TrueFirst' />
Boolean usage is also defined by capabilities in the manifest file. <br> See the Query section for boolean capabilities here for more details.
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.
You have some control over what characters are escaped in string literals by changing the value of
Standard escapes only single quotes and is the default,
Extended also escapes
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.
<format-string-literal value='Standard' unicode-prefix='_utf16' />
Date and Datetime formatting elements take a formula, typically used to cast the string literal to a date type, and a date string format.
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.
<format-date-literal formula="(DATE '%1')" format='yyyy-MM-dd' /> <format-datetime-literal formula="(TIMESTAMP '%1')" format='yyyy-MM-dd HH:mm:ss.SSS' />
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:
CAP_CREATE_TEMP_TABLEScapability 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>
This function uses a piece-by-piece formula for defining a SELECT statement. Here, we can define the clause used in
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 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> />
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.
The identifier generation can include restrictions by character, casing and length. This is primarily used in table name mangling.
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.
Normalize the identifier casing. If this element is omitted the identifier casing is not changed. The value for this property can be:
Restrict the maximum identifier length. There is no limit by default.
<id-allowed-characters value='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_' /> <id-case value='Lower' /> <id-max-length value='64' />