ResagratiaResagratiaResagratia
+234 806 299 1199
contact@resagratia.com

How to Connect MySQL to Power BI

MySQL is one of the most used legacy database management systems while it is the industry standard for storing and querying data, its forte is not in visualizing and aggregating data from multiple sources that is where Power BI comes in.

So, what does it mean to connect? To connect is to join, link, unite, bind or fasten together. This article is a pro bono for you our beautiful readers and learners, we will be connecting each SQL query with its visual depiction in Power BI.

Sequel to the article on Exploratory Data Analysis Using MySQL, we will take it up a notch by exploring data using the relationships between analytical tools such as Power BI and a Relational Database Management System such as MySQL.

Power BI is a self-service, business analytics tool created by Microsoft. It aims to provide interactive visualizations and business intelligence capabilities with an interface simple enough for end-users to create their own reports and dashboards.

We will be analyzing the Vannimanufacturing Database to create a Performance report.

You can download the database here.

174 Downloads

How to Connect MySQL to Power BI

We will develop this report step-by-step in order to be in tandem with what we are depicting in Power BI.

Before building any report and connecting MySQL to Power BI, it’s important to install the Vanni manufacturing database and start MySQL server.

Follow the steps in this guide to install the Vanni manufacturing database within MySQL server.

After installing the Vanni manufacturing database and starting the server, you’re set to connect MySQL to Power BI.

It’s important to note that the server currently resides on the localhost i.e. your database is on-premise, running on your own computer system and not in the cloud.

Connect MySQL to Power BI

To connect MySQL to Power BI, follow these steps;

Step 1:Power BI Get Data

While ensuring that MySQL server is running, open Power BI and connect to the MySQL data source as shown below.

Connect MySQL to Power BI

Step 2:Insert MySQL Database

To insert the MySQL database, enter the following details within the dialog box that opens;

  1. Enter the server name as localhost. Alternatively, you can enter 127.0.0.1 which is the network IP address of the localhost.
  2. For database, enter vannimanufacturing which is the name of the Vanni manufacturing database.
  3. Ensure that this matches the exact name of the database that was installed in your MySQL server.
Connect MySQL to Power BI

If you click OK, this will connect to the entire database and all the tables contained within the vannimanufacturing database. But what if what we want is only a specific table or a subset of the database?

Step 3: Advanced Option (optional)

If you want to specify a table or a subset of the database, follow these steps;

  1. Expand the Advanced options, we can enter a custom SQL statement to retrieve a subset of the vannimanufacturing database.
  2. Type-in the SQL code
  3. Here, we’re connecting to the customers table within the Vanni manufacturing database and retrieving only customers who reside in Singapore.
  4. Click OK.
Connect MySQL to Power BI

Step 4: Insert Username and password

Follow these guidelines on how to insert your credentials from your MySQL server installation;

  1. Insert your User name
  2. Insert your Password
  3. Ensure to choose database credentials and enter the appropriate username and password of your MySQL server installation.
Connect MySQL to Power BI

Step 5: Click Load

Finally we’re presented with the records of customers from Singapore according to our query. From this interface, we can load the data directly into Power BI for visualization or into Power Query for further manipulation and transformations.

Connect MySQL to Power BI

This is the core of what you need to know about connecting MySQL to Power BI for data visualization and more complex business analysis. After understanding the processes above, you can now move on to writing more SQL code and transferring to Power BI for visualization.

A few things to Note:
  1. Of course, the size of the vannimanufacturing database is relatively small which means you can connect to the entire database directly in Power BI without having to write MySQL code.
  2. However, for larger databases, performance is key and it is advisable to retrieve only specific records instead of connecting to the entire database.

Connect MySQL to Power BI and Build Performance Reports

1. Metrics and Cards

Let’s track and visualize the performance of the Vanni manufacturing company by connecting MySQL to Power BI:

Total sales

In MySQL, to get the total sales, we’ll use the following query:

SELECT
      SUM(quantityordered*priceeach) AS Total_Sales
FROM orderdetails;
SQL query result set
SQL query Result-set
Power BI card
Power BI Card
Step guide

In order to get the value for the total sales, we took the following steps:

  1. Write a SQL query in MySQL to get the Total sales
  2. Import the SQL query into Power BI using the get data tool
  3. Select the Power BI card visual option and edit to your preference

Awesome, awesome stuff…Getting excited? We’re just warming up!

Total Revenue

Let’s evaluate the Total Revenue earned by the company. To get the Total revenue, we’ll use the following query:

SELECT
      SUM(amount) AS Total_Revenue
FROM payments;
SQL result set
SQL result set
Query Visual Display in Power BI using a Card
Power BI card
Power BI Card
Step guide

In order to get the value for the Total Revenue, we took the following steps:

  1. Write a SQL query in MySQL to get the Total revenue
  2. Import the SQL query into Power BI using the get data command tool
  3. Select the Power BI card visual option and edit to your preference

Total Quantity

Let’s evaluate the Total quantity of all the products manufactured by the company. To get the Total quantity, we’ll use the following query:

SELECT
      SUM(quantityordered) AS Total_Quantity
FROM orderdetails;
SQL query result-set
Power BI card
Power BI Card
Step guide

In order to get the value for the Total quantity, we took the following steps:

  1. Write a SQL query in MySQL to get the Total quantity
  2. Import the SQL query into Power BI using the get data command tool
  3. Select the Power BI card visual option and edit to your preference

Alright, the report is picking up pace…Let’s dive into the next category…

2. Comparisons and Charts

Let’s explore the Vanni manufacturing database even further.

Yearly Operating Revenues

Let’s compare the revenue stream of Vanni Manufacturing company across the 3 years of operations. To get the Operating Revenues, we’ll use the following query:

SELECT orderdate,c.customernumber,
       c.customername,sum(amount) AS payment
FROM customers c
JOIN payments p
     USING(customernumber)
JOIN orders o
     USING(customernumber)
GROUP BY orderdate
HAVING payment > 100000
ORDER BY orderdate ASC;
Query Visual Display in Power BI using a Line Chart
Connect MySQL to Power BI
Quarterly Revenues
Analysis:

In the above chart, you can view the quarterly revenues realized by Vanni manufacturing company. If we aggregate the data to show yearly revenues, we can see the yearly trend of revenue performance.

Aggregate data in Power BI
Yearly Revenues
Step guide

In order to get the values for the yearly operating revenues, we took the following steps:

  1. Write a SQL query in MySQL to get the yearly operating revenues
  2. Import the SQL query into Power BI using the get data command tool
  3. Select the Power BI Line chart visual option and edit to your preference

Total performing products

Let’s compare the Total sales of all the products manufactured by the company to identify the top performing products. To get the Total sales by productline, we’ll use the following query:

SELECT
     productline,
     sum(quantityordered*priceeach) AS Total_Sales
FROM products
JOIN orderdetails
      USING (productcode)
GROUP BY productline
ORDER BY Total_Sales DESC;
SQL Join, SQL Group By, SQL Order By
SQL query result set
Query Visual Display in Power BI using a Bar Chart
Power BI Bar Chart
Power BI Bar chart
Step guide

In order to get the values for the top performing products, we took the following steps:

  1. Write a SQL query in MySQL to get the values for the products
  2. Import the SQL query into Power BI using the get data command tool
  3. Select the Power BI horizontal clustered bar chart visual option and edit to your preference

Customers Credit limit

Let’s get the top 10 customers with the highest credit limit. To get the Credit limit by customers, we’ll use the following query:

SELECT 
      customername,
      creditlimit
FROM customers
GROUP BY customername
HAVING creditlimit > 50000
ORDER BY creditlimit DESC
LIMIT 10;
MySQL result-set
Query Visual Display in Power BI using a Column Chart
Power BI Column Chart
Power BI Column chart
Step guide

In order to get the values for the Customers credit limit, we took the following steps:

  1. Write a SQL query in MySQL to get the values for the products
  2. Import the SQL query into Power BI using the get data command tool
  3. Select the Power BI Column chart visual option and edit to your preference

Total performing Customers

Let’s evaluate the top 10 profitable customers of Vanni Manufacturing company. To get the Top 10 profitable customers, we’ll use the following query:

SELECT 
      customername, 
      sum(amount) AS Payment
FROM customers 
JOIN payments 
	USING (customernumber)
GROUP BY customername
HAVING payment > 100000
ORDER BY payment DESC
LIMIT 10;
Query Visual Display in Power BI using a Bar Chart
power BI Bar chart
Power BI Bar Chart

We have reached the climax for this article on connecting MySQL to Power BI. So far we have learnt the intricacies of how to connect MySQL Database to Power BI, how to build a report, what it entails, how to access and retrieve from MySQL database with a few examples to get you started.

I hope you enjoyed this brief article and if you enjoyed it, don’t forget to like, share and subscribe to Resagratia for more exciting content.

Author

Geovanni Ubah. You can find me on LinkedIn.


Do you want to learn how to automate tasks with Excel?

YouTube video

Receive Fresh, Weekly Analytics Content

Join 1K+ subscribers to receive original, freshly curated content every week.

Video series – Building an Expenses Management Dashboard in Excel

Related article – Visualizing WhatsApp Data using Python and Power BI