Introducing initial SQL with parameters in Tableau 9.3

Tableau has supported passing initial SQL for some time, but only for a handful of data sources. And it was missing a critical piece of the pie; you could not dynamically pass parameters like username. With Tableau 9.3, we have added the first round of data sources that support not only initial SQL but also the ability to pass parameters to the database that are evaluated at runtime.

Update: Tableau 9.3 is here! Download now to try out the feature outlined below.

With Tableau 9.3, you can use a parameter to pass an initial SQL statement to your data source.

There are a variety of reasons why you may want to do this. You might want to implement your own logging mechanism on the database, or use your own row-level security on the database.

The parameters

We have included a set of useful parameters that can be substituted when your query is run. Simply include these pararmeters in your SQL statement in brackets. For example, [TableauServerUser] will return the user currently logged into Tableau Server. Here are a few others:

• [TableauServerUser] returns the current Tableau Server user’s username only.
• [TableauServerUserFull] returns the fully-qualified domain name format of username, in domain/user format.
• [TableauApp] returns the Tableau app being used to access.
• [TableauVersion] returns the version of Tableau being used to access.
• [WorkbookName] returns the name of the workbook being accessed.

We are evaluating other tokens that may be useful and will add more in the future.

You can also pass any of these parameters to a stored procedure or arbitrary SQL code. Implementing logging could be as simple as passing an insert statement to a history table every time a user connects.

Row-level security

Database Servers often support row-level security that is applied at the session level. One example of this is Oracle VPD (Virtual Private Database). The Tableau side of this is simple. The database expects you to call a known stored procedure and pass it to the user for the duration of that session.

The implementation on the database side varies by vendor, but the basic approach is to have the stored procedure evaluate the username on some custom logic—often a lookup table that maps users to groups in the database—then act as if the current user had the permissions of the group for the duration of that session.

In these types of cases, the users commonly do not exist as users in the actual database. By creating a lookup table that contains the usernames and implementing logic to assign the context based on the session, you can achieve row-level security without the users being actual objects in the database.

This type of security is implemented entirely on the database side. The signature of the stored procedure, its functionality, etc., must all be implemented. Tableau simply executes the SQL and substitutes any parameters that are used.

Am I logged in?

One important clarification for the [TableauServerUser] token set is that you have to be logged into Tableau Server for this to resolve. The tokens will return no value on Tableau Desktop if it is not logged into Tableau Server. It will always return a value on Tableau Server.

Which databases are supported?

When Tableau 9.3 launches, we will support Oracle, SQL Server, Sybase ASE, Redshift, and Greenplum. We are testing more sources and will enable this functionality for more databases in future releases. Initial SQL supports a larger set of data sources. This list represents data sources that support Initial SQL with parameters.