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

When connecting to some databases, you can specify an initial SQL command that will run 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. Initial SQL is not run when your refresh your view. Note that this initial SQL is different from 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 does not examine the statement for errors. This SQL statement is simply 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 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 will open, but the initial SQL commands will not be 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. There are several reasons why this is useful:

• 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 are 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 keep track of 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 is 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

If you use the TableauServerUser or TableauServerUserFull parameter in an initial SQL statement, you will create a dedicated connection that can’t be shared with other users. This will 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 are not listed after using initial SQL to create table

When you connect to MySQL, if you run an initial SQL statement like the following, 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 table causes Tableau to stall

When you connect to Oracle and run an initial SQL statement like the following, 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;