Tableau – Using SQL queries to get data

Tableau allows to connect to a wide variety of database systems and most of the operations like joins, filtering and ordering can be done without writing a single line of SQL. But what can be done  if the data required is not readily available in the database tables? There are a couple of ways to use SQL queries within Tableau.

1. Custom SQL

You can use a custom SQL query as your data source. It can be accessed using the ‘New Custom SQL’ option which is displayed below the tables by default. Please note that only a single SQL statement is allowed and no semicolon required at the end of statement.Parameters can be used in the query  and they can be added to the dashboard later.  Please take a look at this Tableau online help article for more details.

2. Initial SQL

When connecting to some databases, you can specify an initial SQL command to run when you open the workbook, refresh an extract, sign in to Tableau Server, or publish to Tableau Server. This initial SQL is different than a custom SQL connection, which defines a relation (table) to issue queries against.

Above lines from Tableau Online help summarizes this feature very well. This can be defined during the initial connection configuration. A common use case is to get data in to a temporary tables. You can use certain tableau related parameters in your query as well.

 Since I am using SQL Server, the temp tables are created in tempdb. We need to change the database to tempdb and then this table can be used like any normal database table.

3. Stored procedures

Another option to use custom SQL in Tableau is to store the query as a stored procedure in database server.   Please note that Tableau accepts only one result set per stored procedure. There are other restrictions on stored procedures  and a full list can be found in  documentation . By default, stored procedures are listed below the tables as a separate section.