Custom SQL in Tableau

Introduction to Custom SQL in Tableau

In a database, multiple tables exist, and often to get desired data, multiple tables have to be joined. Any visualization tool working on a large database employs JOINs in order to get access to data from the database. Tableau supports connectivity with various types of data sources the most common SQL server. When Tableau is connected with the SQL server, requisite data gets loaded into Tableau’s environment. Automatically, through SQL queries, the joins are performed by Tableau. However, in complex situations, these SQL queries may not be in an optimized form. Using the Custom SQL option in Tableau, we can streamline data extraction operations.

Working with Custom SQL in Tableau

We will connect the “Employees” database in MySQL. It contains various details about employees in an organization. Various tables present in the database include employees, departments, salaries, dept_manager, etc. Using this database will work on and demonstrate the concept of Custom SQL in Tableau.

Step 1: Let’s first connect to the data source. In this case, it is MySQL. So click on MySQL as highlighted in the below screenshot.

Step 2: When we click on MySQL, the MySQL Connection dialogue box pops up. In this dialogue box, we need to provide requisite credentials to get access to the data.

Step 3: As can be seen below, we supplied requisite credentials in the MySQL Connection dialogue box in order to connect with the database. When done, click on the OK button.

Step 4: If correct credentials are provided then a successful connection with MySQL happens as shown by the following screenshot. We have got a “Connected to MySQL” message. Now, in the Database section, we can select the desired database from the drop-down.

Step 5: We selected the “test” database from the drop-down menu. As we can see, there are multiple tables present in this database. We will use the required table to serve our purpose.

Step 6: When the database is loaded, the layout in Data Source looks like as the one shown by the following screenshot. We can directly drag the requisite tables into “Drag tables here” section.

Step 7: We brought tables employees and salaries into “Drag tables here” section, and automatically Tableau created a join between the two tables. Note, if Tableau identifies that common field exists between them then it creates the join.

Step 8: Just have a look at the data obtained after Tableau joined the employee’s table with the salary table.

Carefully study each of the fields in the above table. We can find that the Emp No. field has been repeated as highlighted by the red boxes. This is because, Tableau, by default, considers fields from all the tables after the join is performed. Similarly, the Hire Date and From Date fields are nothing but one and the same. Both the fields essentially represent the date from which the employee started working for the organization. However, the date has been referred to as Hire Date in employees table and From Date in Salaries table. The two fields have been highlighted by the green boxes.

Step 9: We can overcome the redundancy witnessed in the above step by using Custom SQL functionality in Tableau. When two tables are joined, Tableau writes its own SQL query. Tableau does allow us to work on it. Under the Data tab Click on the “Convert to custom SQL” option as shown by the following screenshot.

Step 10: Following the above step navigates us to “Convert to Custom SQL” section as shown by the screenshot below. Observe the SQL query carefully. We can see that the emp_no field has been considered for both the tables as highlighted. Also, hire_date and from_date are two same fields but as they are present in the two tables with two different names so they have been considered as separate fields. We will edit this SQL to obtain overcome redundancy.

Step 11: As we can see in the below screenshot, we edited the SQL query. Go through it once, and observe carefully the changes we did. Here, we removed those fields which were earlier repeated. So we removed emp_no, from_date and to_date from the salaries table. Click on the OK button once done with the writing of the desired query.

Step 12: When the SQL query is updated, we get options to update the table viz. “Update Now” and “Automatically Update”. Click on any of them to proceed. The table shall be updated based on the new SQL query.

Step 13: When the above steps are correctly followed in order, we get the updated table as shown below. Go through the table and study its fields. Now, we can see that the table looks more meaningful and it contains no repeated fields. The fields don’t give the impression that they have been brought from different tables. The Custom SQL functionality in Tableau enabled us to modify the default SQL query and update the table in the required manner. So, the functionality is useful in cases, when the default SQL query, bringing data from multiple tables, is not optimized one.

Step 14: The Custom SQL query that gets created as a result of the updating of the default SQL query can be seen in the tables section. Now, in place of all the tables, “Custom SQL query” appears. It essentially means that the SQL query is user created. The query can be said to acting as a table. We can edit it as many times as required. To edit the Custom SQL query, click on the “Edit Custom SQL Query” option in the drop-down menu as shown by the following screenshot. If we click on the Duplicate option, then duplicate of the query will be created by Tableau.

Step 15: We can remove the Custom SQL query from the environment by clicking on the “Remove” option as shown by the following screenshot.

Step 16: Clicking on remove the custom SQL query from the Data Source tab permanently as shown below.

Conclusion

Tableau along with allowing connectivity with data sources of various types, also allows users to manipulate SQL query(s) when connecting with the SQL server. The Custom SQL functionality is crucial, especially, in the case of complex databases, so as to optimize the SQL where each line of code determines the processing performance.

This has been a guide to Custom SQL in Tableau. Here we discuss the introduction and working with Custom SQL in Tableau. You may also have a look at the following articles to learn more –

1. Tableau Action Filter
2. LOD Expressions in Tableau
3. Tableau Workbook
4. Concatenate in Tableau 
5. Date in HTML | Features | Examples