Tableau - Using Initial SQL for Temp Tables and Variables

What does it do?

This guide will help you to use temp tables and variables in your custom SQL scripts for Tableau data sources.

Why is this done?

Ordinarily, when using Custom SQL, Tableau doesn't support common features like temp tables and variables despite them being powerful tools. Don't despair! There is a little-known area where you can actually use temp tables and variables, among other things. Tableau refers to this as Initial SQL -- that is, it only runs when a connection is made to the database. So what does that mean? It simply means that the Initial SQL code will only execute at specific times:

• Opening a workbook
• Refreshing an extract
• Signing into Tableau Server
• Publishing to Tableau Server

Unfortunately, this also means that Initial SQL will not run when you refresh the view. So, in order to use it, your dashboard needs to be setup to have an extract that refreshes regularly or be republished on an as-needed basis.

How is this done?

Initial SQL can be setup in two ways:

First way

• On the SQL Server login, click "Initial SQL..."
• 

Second Way

• Under the connection for the database
• 

Inserting Temp Tables and Variables into Initial SQL

• Once the dialog box for Initial SQL opens, you can begin inserting your code into it that contains variables, temp tables, stored procedures, etc. The box is very similar to the Custom SQL box:
• 

How to Reference Initial SQL

• Now you have your temp tables and variables created. How do you actually bring them over to the Custom SQL area and display them as fields in Tableau? Firstly, it's important to point out that you can only reference the temp tables in the Custom SQL area. You cannot reference variables or other features that Custom SQL does not support. 
  • If you are using a variable, its use/manipulation needs to be limited to the Initial SQL area OR you can store the value in a temp table
    to be used later in the Custom SQL area
  • If you are only using temp tables in the Initial SQL area, then you can freely reference them in the Custom SQL area
• How you actually pull the data into the Custom SQL area depends on how your data is organized in the Initial SQL area. If you created a final temp table with all of the data that you need, then you could simply write "SELECT * FROM #Temp". If you created multiple temp tables in the Initial SQL area and need to bring them together (possibly with another new table) on the Custom SQL side, then you will need to write a large SELECT statement and JOIN them together. Here's an example below:

• If you look at the JOIN near the end, you can see where multiple temp tables are referenced along with their corresponding columns above them. One column, TotalDonors, is even created on the fly by referencing a temp table that is not even in the JOIN.
• That's it! Please reach out to IDS if you have any questions! Additionally, check out Tableau's own documentation on Initial SQL.