Power BI is a powerful data visualization tool that allows you to connect to various data sources, including MySQL databases. In this blog, we’ll walk you through the process of connecting Power BI to a MySQL database, creating views in MySQL, and importing the data into Power BI for analysis.
Step 1: Install MySQL Connector for Power BI
Before you can connect Power BI to MySQL, you need to install the MySQL ODBC connector, which acts as a bridge between Power BI and MySQL.
- Download MySQL ODBC Connector from MySQL’s official site.
- Install the connector by following the on-screen instructions.
- Download Blinkit Dataset Click here to Download
Step 2: Connect Power BI to MySQL Database
- Open Power BI Desktop.
- Click on Get Data in the Home tab and select MySQL Database.
- In the MySQL Database connection window, enter the following details:
- Server Name: Enter the name of your MySQL server.
- Database Name: Enter the database names:
- For Â
blinkit_database
. - For
inventory_database
.
- For Â
- Credentials: Provide your MySQL credentials (username and password).
- Click Connect to establish the connection.
Step 3: Create Views in MySQL Using MySQL Workbench
You will create views in MySQL Workbench to simplify your queries and enhance performance.
3.1 Create View for blinkit_database
In MySQL Workbench, run the following query to create the  blinkit_database_view
:
CREATE VIEW 'blinkit_database' AS SELECT orders.order_id, customer.customer_id, orders.delivery_partner_id, order_item.product_id, customer_feedbacks.feedback_id, orders.order_date AS order_datetime, customer.area, customer.customer_name, customer.customer_segment, products.product_name, products.category, products.price, order_item.quantity, ROUND(products.price * order_item.quantity, 2) AS Value, orders.payment_method, delivery.promised_time, delivery.actual_time, delivery.delivery_time_minutes, delivery.reasons_if_delayed, customer_feedbacks.rating, customer_feedbacks.feedback_category, customer_feedbacks.feedback_text, customer_feedbacks.sentiment AS feedback_segment, rating.Emoji, rating.star, category.img, DATE(orders.order_date) AS Date FROM orders INNER JOIN customer ON orders.customer_id = customer.customer_id INNER JOIN order_item ON ROUND(order_item.order_id) = orders.order_id INNER JOIN products ON order_item.product_id = ROUND(products.product_id) INNER JOIN customer_feedbacks ON customer_feedbacks.customer_id = orders.customer_id AND customer_feedbacks.order_id = orders.order_id INNER JOIN delivery ON delivery.delivery_partner_id = orders.delivery_partner_id AND delivery.order_id = orders.order_id INNER JOIN rating ON customer_feedbacks.rating = rating.Rating INNER JOIN category ON products.category = category.category;
This query will create the order_feedback_view
in the blinkit_database
.
3.2 Create View for inventory_database
For the second dataset related to inventory, we will create a view in the inventory_database
:
CREATE VIEW 'inventory_database' AS SELECT i.product_id, p.category, p.product_name, p.price, i.date, i.stock_received, i.damaged_stock, ROUND(p.price * i.stock_received, 2) AS Price FROM inventory i INNER JOIN products p ON i.product_id = p.product_id;
This query will create the inventory_database
.
Step 4: Connect to the Views from Power BI
- After creating the views in MySQL, return to Power BI Desktop.
- Click Transform Data to open the Power Query Editor.
- In the Navigator window, you should now see the
blinkit_database
andinventory_database.
- Select the views you need and click Load to load the data into Power BI.
Step 5: Create the Query Directly in Power BI (Alternative)
Instead of creating a view in MySQL, you can write the SQL query directly in Power BI to pull data.
- In Power BI Desktop, click Home > Get Data > MySQL Database.
- In the MySQL Database window, click Advanced Options.
- Paste the following query for
blinkit_database
into the SQL Statement box:
SELECT orders.order_id, customer.customer_id, orders.delivery_partner_id, order_item.product_id, customer_feedbacks.feedback_id, orders.order_date AS order_datetime, customer.area, customer.customer_name, customer.customer_segment, products.product_name, products.category, products.price, order_item.quantity, ROUND(products.price * order_item.quantity, 2) AS Value, orders.payment_method, delivery.promised_time, delivery.actual_time, delivery.delivery_time_minutes, delivery.reasons_if_delayed, customer_feedbacks.rating, customer_feedbacks.feedback_category, customer_feedbacks.feedback_text, customer_feedbacks.sentiment AS feedback_segment, rating.Emoji, rating.star, category.img, DATE(orders.order_date) AS Date FROM orders INNER JOIN customer ON orders.customer_id = customer.customer_id INNER JOIN order_item ON ROUND(order_item.order_id) = orders.order_id INNER JOIN products ON order_item.product_id = ROUND(products.product_id) INNER JOIN customer_feedbacks ON customer_feedbacks.customer_id = orders.customer_id AND customer_feedbacks.order_id = orders.order_id INNER JOIN delivery ON delivery.delivery_partner_id = orders.delivery_partner_id AND delivery.order_id = orders.order_id INNER JOIN rating ON customer_feedbacks.rating = rating.Rating INNER JOIN category ON products.category = category.category;
For the inventory_database
, use the following query:
SELECT i.product_id, p.category, p.product_name, p.price, i.date, i.stock_received, i.damaged_stock, ROUND(p.price * i.stock_received, 2) AS Price FROM inventory i INNER JOIN products p ON i.product_id = p.product_id;
SELECT i.product_id, p.category, p.product_name, p.price, i.date, i.stock_received, i.damaged_stock, ROUND(p.price * i.stock_received, 2) AS Price FROM inventory i INNER JOIN products p ON i.product_id = p.product_id;
- Click OK and then Load to load the data into Power BI.
Step 6: Import Data from MySQL Dump
To import data from a MySQL dump file (
test.db
), you need to import the dump into your MySQL database first. Here’s how:- Use MySQL Workbench to import the dump:
- Go to Server > Data Import.
- Select the Import from Self-Contained File option and choose the
test.db
file. - Select your target database and click Start Import.
- After the data is imported, connect Power BI to your MySQL database as described earlier to load the data.
Difference Between Using Views and Direct Queries
- Using the
CREATE VIEW
Method:- A view is a stored query in the database, which can be easily referenced later without having to re-write the query. It improves performance for complex or repetitive queries.
- Pros: More efficient for long-term use, easier to maintain, and the database handles the query optimization.
- Using Direct SQL Queries in Power BI:
- Power BI directly sends the query to the database, which can be useful for one-time or ad-hoc reports.
- Pros: Quick and easy setup, but might be less efficient for complex queries or if the query is reused often.
- Use MySQL Workbench to import the dump:
Very Nice.