Connecting SQL to Tableau Public
In one of my previous posts about Tableau, I wrote about some of the differences between the full version of Tableau and Tableau Public. One of the major differences is the capability to connect to larger databases. The full version of Tableau can do this, however the Public version can only connect to certain files. You may want to try to use SQL with Tableau, but those who are just learning about these tools might not want to pay for the full version.
If you are in the midst of learning Tableau and want to use SQL to filter your data, there is a slightly less efficient way to connect the two.
To use this method you will need to have downloaded two main programs: SQL Workbench and Tableau Public. To show this method we will use the employees database mentioned in my SQL Workbench post.
The first step is to open your SQL Workbench app and click one of your connections.
We can then connect to our employees database using the following SQL code:
USE employees;The next step is to create a query for the information we want to create a visualization for. For this example, let’s create a query to find the number of employees hired in a specific department over the years.
The following could be considered review or extra practice using SQL. You can skip ahead if you are only interested in the shortcut.
SQL Code Example
The first step in creating our SQL code is understanding what facts we want to know from our query. There are three major points we need to gather if we are to answer our business question: the year, the department name, and the number of employees.
Now that we know the three major pieces of data we want returned from our query, we need to understand which tables we will need to join and use to find those pieces. Our departments table will give us the names of the different departments. The dept_emp table gives us which employees are in which department. For the year, we can create a new table using a subquery about the hire dates in the employee table.
Now on to the coding. Let first create the subquery we need for the year data.
SELECT
YEAR(hire_date) AS calendar_year
FROM
employees
GROUP BY calendar_yearThe next step is to add it to a FROM statement with the rest of the data we need.
To be able to query the rest of the data, we need to JOIN it with two other tables. Due to our first table only having the years and thus no Primary or Foreign Keys, we need to use CROSS JOIN to join the two tables. By using CROSS JOIN with our departments table, every department will have a row for each year.
The last table we need to add is the dept_emp table. This can be joined on the dept_no column. We will also use the Beautify tool in SQL Workbench to clean up our work.
The next step is to make sure that we are only counting the employees that start working in that department during that specific calendar year.
The final step is find the number of employees for each year and department, which can be solved by using the GROUP BY and ORDER BY statements.
Now, all we need to do is run the query.
Connecting SQL to Tableau Public
Even though we cannot connect our SQL database to Tableau Public, we are able to create a file that CAN be used in Tableau Public. When we run a query in SQL Workbench, there is an option to export the results of the query as a file.
One of the options is a CSV File, which is a type of Text file.
This file can then be connected to in Tableau Public.
Even though this process adds a couple of extra steps, which means it is less efficient, it allows for a wider range of practice and types of projects you can create using Tableau.
I hope this post helps expand the type of projects you work on using Tableau Public and allows you practice with combining SQL and Tableau. Thanks for reading!
For more documentation on installing the Employees database:
For more documentation on Licensing for the Employees database:
