Introduction
There are several ways you can connect to data residing in a server using Tableau.
Create a direct connection to the tables (data set) on the Tableau data connection window – where you can relate, join, and union different tables together (which is not the most recommended way to establish your Tableau data source for server connections).
Connect to a query (custom SQL query) rather than connecting to the entire data set – an excellent way to optimize your queries by reducing the size of data used for analysis among other reasons.
What is a custom SQL query?
SQL (Structured Query Language) is the language of databases (used to communicate with databases) – e.g., performing tasks such as storing, retrieving, manipulating, and accessing data from relational databases.
A Custom SQL query in Tableau allows you to retrieve, manipulate, and access data from relational databases. For example, you can write a simple custom SQL query to union data from across tables, recast fields to perform cross-database joins, restructure or reduce the size of your data for analysis et cetera.
Reason why you should use a custom SQL over a direct connection
There are several reasons why you should use a custom SQL query to connect your data in Tableau. The most undisputed reason is to optimize your data for analysis – because with a custom SQL query you can properly optimize your data far better than Tableau will be able to - as Tableau in most cases will be performing a SELECT ALL of all your data – unlike in the case of custom SQL where you can select specific data for your analysis.
Avoid Custom SQL, use views instead
Rather than using a custom SQL query connection inside the Tableau Desktop. The best recommendation is that you use an SQL statement to create a view inside your database, then connect the view to Tableau. By doing that pushes the workload back to the database and saves Tableau’s analytical processing power for loading and filtering workbooks.
Working with custom SQL query in Tableau
To demonstrate how you can use custom SQL in Tableau, I will be using the East – Superstore and West-Superstore datasets residing in the Google Cloud (BigQuery). You can download the above datasets to help you digest concepts covered in this article.
Connect to custom SQL query
To connect to the data, I’ll use the BigQuery connector under server connections on my Tableau Desktop.
Next, I’ll login to the server using my credentials. (Sign in with Google for that case)
Grant Tableau access to my Google account associated with my Google Cloud project
Next, I’ll choose my Project ‘My Project 93419’ and Dataset ‘Rigor_Trial’ to reveal all the tables in my Dataset, as well as the Custom SQL tab.
Next, I’ll double click on the New Custom SQL tab on the left pane – which opens the Custom SQL editor box, that I can use to retrieve the data that I need for my analysis.
For example, I can use this simple SELECT statement - to retrieve Technology category data in the East – Superstore table.
(This simple query selects all the Technology Category data from the table, ‘East – Superstore’ which resides in the dataset ‘Rigor_Trial’.)
Executing the above query retrieves data where Category is Technology from the East – Superstore table, as you can see in the data grid.
(With all that done - my Tableau data source is now ready for analysis)
More examples of custom SQL queries
1. Combining tables vertically (Data Union)
Data union is a technique of combining data in which data from one table is appended to the other. Note, you can union tables which are of the same structure (same number of columns).
To union the ‘East – Superstore’ and ‘West – Superstore’ tables (which are of the same structure), I can use the following Custom SQL query for Tableau + BigQuery connections.
(A query which appends all the data from one table to the other, creating a Tableau data source which contains all the data from East – Superstore and West – Superstore tables)
2. Combine (Join) and aggregate your data
Data join is a technique of combining tables related by a common field. The results of data joining, is a virtual table that extends horizontally by adding some columns of data.
For example, in this case you might be interested in computing the Sub_Category Sales for both East and West Superstore datasets using the following Custom SQL query for – Tableau + BigQuery connections.
A query that combines and aggregates data from both the tables as you can see in the view below.
3. Reduce the size of your data
Sometimes you can use Custom SQL query to optimize your queries by reducing the size of your data. For example, let’s say using the East – Superstore table, you’re interested in the fields ‘Order_ID’, ‘Order_Date’, ‘Sales’, and ‘Profit’ for the data collected from January 1st, 2021 – for Sub_Category ‘Phones’.
Rather than connecting the entire East – Superstore table, you can write a simple Custom SQL query to retrieve ONLY data needed for your analysis – as shown below for Tableau + BigQuery connections.
Executing the above query returns only the data needed for the analysis.
4. Restructure data (Pivot data)
Pivoting data is a technique of data shaping that rotates data from the state of rows to the state of columns. For example, in this case using the East – Superstore table, I may decide to restructure the data to return a virtual table, containing the Order_ID, Indicator = (Sales, Profit, Discount, Quantity) and Metric (which contains the values for the respective indicators for each Order_ID).
(In other words, pivot the measures Sales, Profit, Quantity, and Discount for each Order_ID)
This can be achieved using the following Custom SQL query – for Tableau + BigQuery Connections.
Which pivots the data as shown below.
5. Change the data type of a field
Sometimes you can change the data type of a field to enable you perform joins (as joins require the data type of fields that you join be of the same data type).
For example, in this case using East – Superstore table, I can convert the Order_Date field (which contain dates) to return two additional fields i.e., Weekday and Month (as string fields - which can be used when performing joins or other computations).
This can be achieved using the following Custom SQL query for Tableau + BigQuery connections.
Executing the above query returns
Creating a view in BigQuery
Rather than using the custom SQL query in Tableau Desktop, you can create a view in BigQuery, in-order to push the workload to the data warehouse. Then connect to the view in Tableau.
For example, using the case on data union described above, rather than using the custom SQL within Tableau, you can create a view in BigQuery that unions the two tables first – then connect to that view in Tableau.
The sample query below shows you - how you can create a view named ‘Superstore – Union’ which combines the East – Superstore and West – Superstore tables within the BigQuery.
Note you can connect to this view ‘Superstore – Union’ directly in Tableau, by simply dragging it to the canvas.
Conclusion
Next time you’re working with server connections, try using Custom SQL query to optimize your queries using some of the ideas covered in this article. If possible, create views instead, so you can push the workload back to the database or data warehouse.
I hope this article was helpful to you. To receive more of the Tableau tips and tricks, kindly join our mailing list by subscribing below.
If you like the work we do and would like to work with us, drop us an email on our Contacts page and we’ll reach out!
Thank you for reading!
