In my previous post, I wrote about one of the ways that SQL Workbench can be indirectly connected to Tableau Public. This method was to export the results of a query to a CSV file, which could then be opened through a text file connection in Tableau Public. The query example used in that post was one that would return the number of employees hired in specific departments over the years the company was open. We created a query that had those results for each department. In Tableau Public, we can use Filters to narrow the results down to one or more departments.
SQL Code Example
Before we enter Tableau Pubic, let’s quickly go over the SQL code we created for our query.
First, we are using the employees example database that you can download for free from the MySQL documentation website. Second, there are three main points of information we want: the year, the name of the department, and the number of employees in the department hired that year.
A table is created of the company’s years of operation through the use of a subquery. This table is cross joined with the departments table so that each department has one row for each year the company was in operation. This table is joined with all the employee data from the dept_emp table. Due to some employees possibly transferring to other departments over the course of their career we want to make sure we are only returning the data where the employees start date matches the calendar year. We then group this information by year and department name and order the resulting table by the same columns.
Once we run the query, we can export the data table to a CSV file.
Now we have our CSV file, we connect it to Tableau Public.
Creating a Tableau Visualization
As mentioned in previous posts, the first step once opening the Tableau Public App is to connect Tableau to the CSV file that you just exported.
To start our visualization, the calendar year should be in the x-axis. So, we want to drag and drop the Calendar Year measure in the columns section of our sheet.
The next step is to add the number of employees for each year into the rows section of our Sheet. Tableau will automatically change the visualization into a line graph.
Now that we have our visualization, we want to be able to filter our graph based on the different departments. There are two different ways to do this. The first method is to drag and drop the department names measure from the Tables column to the filters tab.
By using this method, once the measure is dragged and dropped, it will open a window where you can determine which values you want to see in the visualization. To begin, we will use All and then click OK.
At first, this will not show any difference.
However, you can click the Department Name in the Filters section and click the show filter.
This will pop up a table on the right side of the sheet where you can change what is filtered.
The second method of adding a filter is to click the measure that you want to use as a filter in the Table section, and click the Show Filter option.
One benefit to this method is that the measure will automatically be entered into the Filters section AND it will show the filter table on the right side of the visualization right away.
With our filter in place, we not only have a visualization that tells us the total new employees in all departments, but we can also see the changes over the years in only one department like Marketing.
Amazing, right?! This is just a basic example, but you can see you how this tool in Tableau would be extremely useful for analyzing data, especially by those that may only be able to see and change the filter in a dashboard.
I hope this post helps expand your knowledge and capabilities of using Tableau for your data analysis needs. Thanks for reading!
For more Documentation on the Employees dataset:
For more Documentation on the License for the Employees dataset: