In this article, you will learn everything from how to load MySQL data to Power BI Desktop to performing transformations and visualizations.
As for data analysis, It might be your requirement to load MySQL data to Power BI Desktop and analysis all those data on the basis of business needs or requirements. After getting an appropriate result you can visualize those data using various graphs so that your organization can easily understand what decision should take on the basis of data visualization.
Before going into this article, I will explain to you a little about MySQL and Power BI.
What is MySQL?
MySQL is one of the most popular open-source RDBMS ( Relational Database Management System ) which is used to manage the database. It is SQL-based RDBMS where you can manage the database with the help of SQL ( Structure Query language ) Query. It was developed by oracle corporation and it is written in C and C++.
It comes with various features like Various Data Types, Security, High Performance, Connectivity and client and tools, etc.
What is Power BI ( Business Intelligence )?
Power BI is one of the most popular Business intelligence Tool in the market for data analysis and data visualization offered by Microsoft. Power BI is a collection of software services, apps, and connectors that works together to turn your raw data into coherent, visually immersive, and interactive insights. It supports numerous data sources to load the raw data.
Power BI allows you to connect your data sources analyze the data and visualize the data and create reports and share with anyone you want to.
Parts of Power BI
Power BI contains several elements that work together to convert raw data to meaningful insights and visual formats. These are three major components of Power BI.
- A Window desktop application is called Power BI Desktop.
- An Online Saas ( Software as a Services ) is called Power BI services.
- Power BI mobile apps for windows, IOS, and Android devices.
Power BI Desktop is available free of cost, so you can download it from Microsoft’s official website and install it on your machine and start using it.
Why do you need to connect MySQL to Power BI Desktop?
There are various reasons to connect MySQL to a Power BI desktop. Suppose you are working in an organization or working as a freelancer and Your clients have some sales data stored in a MySQL database table and his requirement is to analyze those data, clean unwanted data, and perform some mathematical operations on those data, At the end client want to some meaningful insight from all of those data so that they can take a business decision on the basis of meaningful data.
So as Data Analyst, First of all, You will connect the MySQL database with the Power BI desktop and load all those required tables into the Power BI. To connect Load MySQL table data to Power BI You require some credentials of MySQL database like Hostname, Database name, Username, and Password.
Once the data is successfully loaded into Power BI, Power BI provides a lot of options to analyze the data. You can easily analyze those data as per your or your client’s requirements and at the end, you can create an interactive dashboard in a visual format where you can visualize your analyzed data so that clients make better business decisions.
So this is the major reason to connect MySQL with Power BI and load MySQL table data to the Power BI desktop.
For this article, I have just created a MySQL database called
testing and also created two tables
marks inside the
testing database, As you can see those two tables and their data are in the below image. Throughout this article, we are about to use these two tables.
Now, let’s connect MySQL with Power BI Desktop.
How to Load MySQL data to Power BI Desktop
Make sure you have the required credentials like hostname, database name, database username, and password because all these credentials are very important to establish a connection between MySQL and Power BI Desktop.
- Open Power BI ( Business Intelligence ) Desktop application.
- Click on Get data from another source.
- Choose MySQL Database under the Database section and click on Connect.
- If you are connecting MySQL with Power BI desktop first time, Then you will get the following window.
- Click on Learn more, This will take you to MySQL’s official website to download a connector because the Power BI desktop needs a connector to connect with the MySQL Database engine. Click on Download.
- Click on No thanks, just start my download. Now your downloading will start automatically after a while.
- Once the connector is downloaded successfully, double-click on that installer and click on Next.
- You will get three setup types typical, custom, and Complete. You can go with any one of them. As of now, I will go with the Typical installation type. Choose Typically and click on Install.
- This will ask you for permission, Click on Yes.
- Click on Finish.
- Now, you are ready to load MySQL table data into the Power BI desktop. After Installing the MySQL connector, you need to restart your Power BI desktop and follow steps from 1 to 3.
- Now you will get this window to enter your database hostname and database name. Enter those credentials and click on Ok. If you are not connecting MySQL with the Power BI desktop the first time, Then you will get directly this window after step 3.
- After entering the hostname and database it will ask you to authenticate your database with the user name and password. Enter the database username and password under the Database section and click on Connect.
- Once the connection will successfully be established then it will ask you to choose the tables name that you want to load into Power BI Desktop. For demonstration, I have just created two tables student and marks. I will choose both tables and click Load.
- In the right sidebar, the Inside Fields section, You can see all your loaded tables.
- To see the data of loaded tables, click on the Data icon ( )in the left sidebar. After clicking on the Data icon you can see all your table data.
- To see the table model and their relationship, click on this Model icon ( ) in the left sidebar. After clicking on the Model icon you can see your table model structure and the relationship between them.
So finally we have seen how to load MySQL data to Power BI Desktop. Now it’s time to plot a Stacked Column Chart using loaded data. first of all, let’s take a quick overview of the stacked column chart.
What is Stacked Column Chart?
A stacked Column Chart is a kind of chart that contains several column series stacked vertically. One by one. The length of each column is determined by the value in each data point. A Stacked Colum chart is capable of changing over time on the basis of data change.
let’s create a Stacked Column Chart in Power using MySQL data. When you will show the marks of each subject student.
Creating Stacked Column Chart in Power BI
To plot a Stacked Column chart in Power BI Desktop, You have to choose the stacked column chart icon from the visualizations section and follow the below steps to make a better Stacked Column Chart.
- Drag and drop the subject_name column from the marks table into X-axis.
- Drag and drop the marks column into Y-axis.
- Drag and drop the name column from the student table into Legend.
After following all the above steps correctly, You will get a beautiful Stack column chart as you can see below.
Once you will click on a particular student’s name, you will get marks for each subject assigned to that student. You can see the below image for a better understanding.
So, In this article, we have successfully covered how to load MySQL data to Power BI Desktop with the help of proper steps along with proper screenshots. I hope you don’t have any confusion regarding the connection between MySQL and Power BI Desktop.
Power BI is one of the most popular BI ( Business Intelligence ) tools in the market for data analysis and data visualizations. It supports numerous data sources to load the raw data, It allows data analysis to load the data from sources and transform the data and after that visualize the transformed data with the help of charts provided by Power BI. Power BI supports various types of charts which you can use as per your convenience.
I hope this article will help you, if you like this article, please share and keep visiting for further Power BI tutorials.
Thanks for your valuable time…. 🙏🙏