Background: I went through a lot of challenges to connect Tableau Public with MySQL. Later I found that Tableau Desktop (Which is a paid or licensed version of Tableau) has an interconnectivity option with MySQL. Using Tableau Public you can’t directly connect with MySQL. Then I went through a lot of research and finally conclude that it can be done, just by using one more additional step.
Introduction: When you combine programming with the software application, then administration and manipulation of a given amount of data gives an output that helps businesses in decision making.
Advantages of Software Integration:
MySQL and Tableau Public can be integrated which means they can serve a common goal by answering such business questions.
You can store your data in a database, and then you can manipulate it using SQL to create your analysis. But again it will give results in the form of thousands of rows which is not easy to interpret. Here Tableau comes in to picture. Its main function includes quickly connecting to a server such as a MySQL server and extracting data, applying relevant calculations, and visualizing the information in the form of charts, graphs, reports, dashboards, and stories. Importantly reports and dashboards allow end-users to understand the core of a business and extract the insights which help in decision making.
WHY combining MySQL with Tableau Public Is Useful?
In contrast to Tableau's in-memory engine, SQL's stored procedures will allow you to perform complex calculations with much better performance on the MySQL server. To do any sort of analysis, you must reorganize your data in the proper format. By pre-processing data at the database level, performance is greatly improved.
Here we will use My SQL workbench (open source SQL database) with Tableau public (The free version of Tableau). We know that Tableau public lacks the Interconnectivity options which we will overcome by executing one additional step. Storing the SQL output in a .csv file. Then import that file into Tableau public. Here all the manipulation and pre-processing is done by MySQL whereas extracting the data and visualization is done by Tableau public.
Steps to Connect MySQL with Tableau public and create visualization:
If you are using MySQL workbench then you can load the employees' table. Below is the query on the employees' table.
Figure 1. SQL query in MySQL Workbench
In figure.1 when you run the SQL query, it will show the result in the form of a table. In the above SQL query it will retrieve hire date as calendar year, gender, count of emp_no as , from employees table by using group by clause on calendar year, gender then it will sort calendar year in ascending order.
Figure 2. Creating .CSV File from MySQL
In figure.2 click on the Export symbol, to create a .csv file (comma-separated values file). Then save that file to the desired location.
Figure 3. Connect to MySQL workbench
In figure3. Open Tableau public. Click on the text file highlighted above. Then import the .csv file to tableau public by clicking on the “open” button.
Figure 4. Visualization in Tableau using .csv file
In figure 4. a visualization is created using Tableau public. The above visualization is represented using a vertical bar graph. In the above graph, the number of employees is segregated based on gender and the calendar year, they joined the company.
Conclusion: So MySQL can be integrated with Tableau public just by doing another additional step which is creating the CSV file and connecting that file with Tableau public. Using Tableau public you can create visualizations, dashboards, and stories which helps businesses with decision making.
Thanks for reading!