Setting Up Power BI with MySQL Database

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.

1. Download MySQL ODBC Connector from MySQL’s official site.
2. Install the connector by following the on-screen instructions.
3. Download Blinkit Dataset Click here to Download

Step 2: Connect Power BI to MySQL Database

1. Open Power BI Desktop.
2. Click on Get Data in the Home tab and select MySQL Database.
3. 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.
   • Credentials: Provide your MySQL credentials (username and password).
4. 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

1. After creating the views in MySQL, return to Power BI Desktop.
2. Click Transform Data to open the Power Query Editor.
3. In the Navigator window, you should now see the blinkit_database and inventory_database.
4. 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.

1. In Power BI Desktop, click Home > Get Data > MySQL Database.
2. In the MySQL Database window, click Advanced Options.
3. 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;

4. 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:

   1. 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.
   2. 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.

Click Here To Download Entire Dashboard