In this blog post, I will be going through what writing Custom SQL is in Tableau and the potential performance issues that might arise with best practises on how to deal with them.
What is Custom SQL in Tableau
Using Custom SQL in Tableau essentially refers to importing, shaping and accessing data from a relational database with using SQL (Structured Query Language). Rather than dragging tables into the canvas the more traditional way for Tableau. Depending on the database, the SQL syntax changes. Therefore meaning that the same statement cannot be universal used across different databases.
How to Access Custom SQL
When you are connected to a relational database like Oracle, PostgreSQL or Microsoft SQL Server, in your Data Source window, there will be a “New Custom SQL” pill below the available tables on the left side:
Double click or drag that into the canvas. A pop up window will appear where you can write your SQL query as shown below:
The Preview Results button in the window opens up the view data window showing the executed query. This improves the ease of data exploration through Custom SQL. You also have the option to Insert Parameter. With this option, you can create a new parameter or use an existing one in your statement. Depending on the parameter selection, the SQL statement will query the result according to what is being selected. However, using parameters in custom SQL might bring some issues regarding performance.
More on Connecting to Custom SQL Query here.
Performance Problems and How to Avoid Them
As mentioned above, using parameters might slow down performance of your dashboards. There are important things to consider when it comes to using Custom SQL in relation to workbook performance. This example is just one of them. In this section, I will be going over certain work arounds when using Custom SQL brings performance issues.
First of all, it is no doubt that using Custom SQL is a powerful tool for transforming, executing complicated logic or leverage from existing queries. It proves to be quite useful when performing ad-hoc analysis or for prototyping. However, in certain production cases, it can have some performance problems. The reason for this is that Tableau leverages Custom SQL as a subquery. Meaning that each query created by Tableau will contain the Custom SQL statements you created surrounded by Tableau’s specific elements. This results in long and complicated queries where the result is worse for some databases. It is best practice to avoid using Custom SQL in production cases. However, there are some work-arounds you can try to improve performance while using Custom SQL if that is an issue.
Simplify as much as Possible
It is best practice that if there is no other choice than using Custom SQL to import data for various reasons, import only what you need. Avoid SELECT * for example. In addition, make your joins, relationships, unions and filtering through Tableau if possible. And finally, avoid using the ORDER BY clause since Tableau handles that based on the structure of the visualisations.
Use Initial SQL
Initial SQL query prevents a Custom SQL query to be run multiple times every time you change something in the worksheets. It might not be always available however. Initial SQL only runs when the workbook is opened and creates a temporary table. One drawback of this method is that the temporary table remains unchanged during the course of the Tableau session. Updates to the data will not be reflected until a new session is created. All in all, using temporary tables with initial SQL can significantly improve performance.
Using Extracts rather than Live Connections
Using extracts (Hyper Extracts) do prove useful to improving performance issues as well. Creating extracts pulls data into Tableau’s data store. Resulting in the queries only running on each refresh. You can create an extract for each Custom SQL query that you have. However, the drawback is that this might not be the smartest way for long-term maintenance. It can be better for your organisation to keep your SQL queries in one place. Which leads me to my final point.
Create a Custom View or Table in the Database
This last point is not a trick you can perform in Tableau, but rather in the source itself. It can be possible to create your query as a custom table or view in your database and store it. Then, Tableau can connect to that specific query the same way as connecting to a table. This method can also help improve performance as well. However, it can be a way out of your control. The database administrator or developers could be the ones that have to make this in your organisation and they might not like the fact that custom views should be created that will take up server space. But it could be useful to discuss about it.
Visit our site The Information Lab NL to see more blog posts, training and consultancy services regarding Tableau, Alteryx and Snowflake.