Tableau Desktop and Web Authoring Help

Note: Tableau Prep Builder version 2019.2.2 and later supports using Initial SQL, but doesn't yet support all of the same options supported by Tableau Desktop For information about using Initial SQL with Tableau Prep Builder, see Use Initial SQL to query your connections(Link opens in a new window) in the Tableau Prep Builder Salesforce Help.

When connecting to some databases, you can specify an initial SQL command that will run or use a cached value when a connection is made to the database, for example, when you open the workbook, refresh an extract, sign in to Tableau Server or publish to Tableau Server.

Note: Initial SQL is different than a custom SQL connection. A custom SQL connection defines a relation (or table) to issue queries against. For more information, see Connect to a Custom SQL Query.

You can use this command to:

• Set up temporary tables to use during the session.

• Set up a custom data environment.

You have the option to add an initial SQL command in the Server Connection dialog box or on the Data Source page.

Note: If your data source supports running an initial SQL statement, an Initial SQL link will appear in the lower left corner of the Server Connection dialog box. For information about your data source, see Supported Connectors.

To use initial SQL

1. In the Server Connection dialog box, click Initial SQL. Or, on the Data Source page, select Data > Initial SQL or Data > Query Banding and Initial SQL depending on the database you are connecting to.

2. Enter the SQL command into the Initial SQL dialog box. You can use the Insert drop-down menu to pass parameters to your data source.

   

Note: Tableau doesn't examine the statement for errors. This SQL statement is sent to the database when you connect.

Your software licence may restrict you from using initial SQL with your connection. If you publish to Tableau Server, the server must be configured to allow Initial SQL statements. By default, the server software is configured to allow these statements to run when the workbook is loaded in a web browser.

Administrators can configure the server to ignore initial SQL statements by using the tsm configuration set command:

tsm configuration set -k vizqlserver.initialsql.disabled -v true

If the server doesn't allow initial SQL statements, the workbook opens, but the initial SQL commands aren’t sent.

For more information about the tsm configuration set command, see the Tableau Server Help(Link opens in a new window).

Parameters in an initial SQL statement

You can pass parameters to your data source in an initial SQL statement. The following list has several benefits of using parameters in a initial SQL statement.

• You can configure impersonation using the TableauServerUser or TableauServerUserFull parameters.

• If your data source supports it, you can set up row-level security (for example, for Oracle VPD or SAP Sybase ASE) to make sure that users see only the data that they’re authorised to see.

• You can provide more details in logging, for example, the Tableau version or the workbook name.

The following parameters are supported in an initial SQL statement:

Warning: Tableau Desktop doesn't include domain. You can include it if you aren't using delegation and you set tsm configuration set -k DelegationUseFullDomainName=-v true--force-keys

The following examples show different ways you can use parameters in an initial SQL statement.

• This example sets the security context on Microsoft SQL Server:

  EXECUTE AS USER = [TableauServerUser] WITH NO REVERT;

• This example shows how, on a DataStax data source, you can use parameters to add detail to logging or to set up a session variable to track the data:

  SET TABLEAUVERSION [TableauVersion];

• This example can be used to help set up row-level security for Oracle VPD:

  begin

   DBMS_SESSION.SET_IDENTIFIER([TableauServerUser]);

  end;

  Note:Oracle PL/SQL blocks require a trailing semicolon to terminate the block. Consult Oracle documentation for the proper syntax.

Defer execution to the server

You can defer an initial SQL statement so that it’s executed only on the server. One reason to defer execution to the server is if you don’t have permission to execute the commands that set up impersonation. Use <ServerOnly></ServerOnly> tags to enclose the commands to be executed only on the server.

Example:

CREATE TEMP TABLE TempTable(x varchar(25));
INSERT INTO TempTable VALUES (1);
<ServerOnly>INSERT INTO TempTable Values(2);</ServerOnly>

Security and impersonation

When you use the TableauServerUser or TableauServerUserFull parameter in an initial SQL statement, you’ll create a dedicated connection that can’t be shared with other users. This can also restrict cache sharing, which can enhance security, but may also slow performance.

Troubleshoot 'create table' for MySQL and Oracle connections

For MySQL connections, tables aren’t listed after using initial SQL to create a table

After you connect to MySQL and run an initial SQL statement, the tables might not show because of the way Tableau constructs the query.

CREATE TABLE TestV1.testtable77(testID int);

To resolve this issue, add IF NOT EXISTS to the SQL statement:

CREATE TABLE IF NOT EXISTS TestV1.TestTable(testID int);

For Oracle connections, using initial SQL to create a table causes Tableau to stall

After you connect to Oracle and run an initial SQL statement, Tableau is stalled with a spinning wheel because of the way Tableau constructs the query.

CREATE TABLE TEST_TABLE (TESTid int)

To resolve this issue, use the following SQL statement:

BEGIN
EXECUTE IMMEDIATE 'create table test_table(testID int)';
EXCEPTION
WHEN OTHERS THEN NULL;
END;