Connect Tableau to MySQL -Easy way

The core part of data analysis is data. Tableau provides many ways to take your raw data and bring it to life through visualizations. The process of bringing data into Tableau is called a data connection, and it is the first thing that Tableau will ask of you when you start developing your visualizations.

There are so many ways to bring data into a workbook. There are mainly three main categories: Tableau Server, File, and Server.

The File category include text, CSV, JSON, Excel, etc.

Under the Server category, data can be acquired from a host of different relational and NoSQL Data sources.

Most of the time learners connect either Text file are Excel file. But in real time, in companies we need to pull data from server. It is not possible to connect to server data base from Tableau public. We must have Tableau Desktop which is paid version. Tableau offers 15 days free trial of Tableau Desktop.

We need Tableau desktop, MySQL workbench, and data to load . These are basic prerequisites.

When you open a new workbook, Tableau will display a blue pane on the left side of the window with the categories and the data source types therein.

As we are connecting to MySQL , select MySQL and one window will open, Fill the information. server, host and username are same for all MySQL user unless you explicitly changed. Give your MySQL password and sign in.

you will land on this page

MySQL Set-up

1.Now we need to setup our MySQL workbench. Open the MySQL workbench

2.It will show home page.

3.Click the Plus sign to create new connection.

4. Write your dataset name or project name as connection Name. Keep the rest setting as it is.

5. Enter your password if needed. Hit ok

6 .Now you can see new connection on home screen.

7. As I was having HR name already so created HR1.

8. Now click on HR connection

9 .You will see screen like this

10. Look at left side bar and if you are on Administration pane, click on schemas.

11. Now we need to Load data

12. Go to top menu bar and select server

13. Import Data

14. As you click Import data , one window appear similar to screenshot below.

15. Select Radio button " Import from dump Project folder" , since my data is dump file.

16 . Browse data from your local computer.

17. fill the field "Default Target Schema" jobms (this will be different for you)

18. You can see schema in two white rectangles.

19 . select "start import"

20. Refresh the data pane

21 .It is beside "SCHEMAS" look like two round arrows

22. Run the query

show tables

23. It will display all the tables from dataset

24. you can run Bunch of queries to see tables.

25. If you run the query

select * from consolidated_search_ds <<table name

It will show whole data, you can send this data as csv file as well.

SELECT count(*) from consolidated_search_se

It will show total records.

26. click on Export from output pane.

27. Now you can explore data using queries .

28. If you observe the Years of experience is -1, we certainly need to change it to 0 or 1.

29. As of now we loaded data to MySQL server and saw data by running basic queries.

30. Now we will connect MySQL server to tableau.

31. We were on this page. Right now my Table is connected to " sales" data from Mysql server.

32. Now If I want to connect data which is "jobms", I need to dropdown from sales data and connect my desired dataset.

33. we successfully connected to "jobms" data.

Tableau EDA (Exploratory Data Analysis)

1. Drag data science table

2.We can perform EDA Exploratory Data analysis in tableau .

Exploratory Data Analysis: An approach to data analysis focused on identifying general patterns in data, including outliers and features of the data that are not anticipated by the experimenter’s current knowledge or preconceptions. EDA aims to uncover underlying structure, test assumptions, detect mistakes and understand relationships between variables.

Basically exploring data and if we come up with some mistakes, clean up data.

2. when you load data you can see two radio buttons for connections live and Extract.

3. select extract for now.

4. And select update now or Update automatically from below window beside schema.

What is a Live Connection and an Extract?

Tableau Data Extracts are snapshots of data optimized for aggregation and loaded into system memory to be quickly recalled for visualization.

Example: Hospitals that monitor incoming patient data need to make real-time decisions.

Live connections offer the convenience of real-time updates, with any changes in the data source reflected in Tableau.

Example: Hospitals need to monitor the patient’s weekly or monthly trends that require data extracts.

Did You Know?

When you create an extract of the data, Tableau doesn't need access to the database to build the visualization, so processing is faster.

If you have a Tableau server, the extract option can be set to a refresh schedule to be updated.

5. Now we will see how to clean data in tableau. It is always good practice to clean data in tableau than SQL, because it will serve our purpose . SQL database team might not want to change anything by analytical team.

6. Actually we are not cleaning data , we are filtering data .

7. Go to Top menu bar , select Data, select "Edit Data source Filter"

8. " years of experience" column has negative values, which we need to replace with 1.

9 . Small window will appear, select " Add"

10. Select "Years of Experience". Hit ok

1. 11. Select At least and set the value as 1. click ok

2. 12.Check in dataset, it will replace -1 with 1

13. Sort "Year of Experience " ascending and check the data, -1 is replaced with 1.

14. Do all necessary cleaning and start Analyzing data, get insights.

That's it for now.

Thank you....