Build E-Commerce Analytics with MySQL

Build a MySQL and Power BI dashboard for customer behavior and revenue analysis.

Introduction

30 Second Summary

Every online purchase leaves behind clues about what sells. Those clues can reveal who returns or where revenue comes from.

In this project, you will turn sample store transactions into a dashboard backed by MySQL and built in Power BI Desktop. The finished report presents trustworthy revenue and customer metrics through interactive cards, charts, and filters.

What You'll Build

You select Mobile in the finished report and watch every headline card plus the monthly revenue chart recalculate for that sales channel.

By the end of this project, you'll have:

  • An interactive dashboard where you can filter by sales channel or customer segment to focus every visual on the same slice of the store.
  • A trustworthy revenue model that reports average order value at the correct order grain without double-counting purchases.
  • Clear customer and product insights that reveal repeat buyers, valuable segments, monthly revenue changes, and leading product categories.
  • Secret Mission: Add gross profit and profit-margin analysis by product category.

Do I need anything before starting?

You need a Windows computer with administrator access and the credentials from your existing MySQL setup. The project checks Power BI Desktop plus MySQL Connector/NET before the analysis begins.

Before We Start

Before the hands-on work begins, this is your moment to lock in what you are building. You will connect accurate revenue analysis in MySQL to an interactive Power BI dashboard.

Verify and Connect Your Windows Tools

The dashboard eventually reads local MySQL results through Power BI Desktop. That path depends on the server plus its reporting provider being available on the same Windows computer.

First, you will verify your existing server connection with MySQL Workbench.

Next, you will prepare MySQL Connector/NET for Power BI Desktop. Windows PowerShell then confirms that Windows can find the provider.

In this step, get ready to:
  • Confirm the local MySQL server version through your existing Workbench connection.
  • Prepare the current 64-bit release of Power BI Desktop.
  • Confirm MySQL Data Provider registration through Windows PowerShell.
Verify your local MySQL server

The server version confirms which MySQL release is answering your queries. This check also proves that your existing local connection still works.

  • Press the Windows key to open Windows Search.
  • Type MySQL Workbench into Windows Search.
  • Press Enter to open MySQL Workbench.
  • Select your existing local connection from the Workbench home screen.
  • Enter your existing MySQL credentials if Workbench requests them.
  • Check your server version by running this query in the connected SQL editor:
SELECT VERSION();

What does this query do?

  • The VERSION() function asks the active MySQL server for its version string.
  • A returned value proves that Workbench can communicate with your local server.

You should see one result containing your local MySQL server version. That is your first green light because your existing database connection is responding.

  • Record the displayed version here: your local MySQL server version.

No version result?

  • Confirm that the SQL editor tab belongs to your existing local connection.
  • Reconnect with your existing MySQL credentials if Workbench shows a disconnected session.
  • Select the version query before using the execute control if another statement is present in the editor.
  • Help me troubleshoot my MySQL Workbench connection.
Confirm Power BI Desktop

Power BI Desktop receives monthly product updates. The Microsoft Store provides the current 64-bit release without requiring a numeric build from this project.

  • Press the Windows key to open Windows Search.
  • Type Power BI Desktop into Windows Search.
  • Choose the tab that matches what you find.

✔️ I have the August 2026 release

  • Select Power BI Desktop from the Windows Search results.
  • Select Help from the top menu.
  • Select About.
  • Confirm that the About details identify the August 2026 release.
  • Confirm that the installed application is 64-bit.

Your reporting application is current. It is ready for the MySQL views you will build later.

ⓧ I see an older release

Updating replaces the older desktop build with the current release. Your existing local reports remain available.

The update is complete. Use the About window for the final release check.

  • Return to Power BI Desktop.
  • Select Help from the top menu.
  • Select About.
  • Confirm that the About details identify the August 2026 release.
  • Confirm that the installed application is 64-bit.

ⓧ It is not installed

The Microsoft Store installation may request administrator approval because it adds a Windows application.

  • Open the official Power BI Desktop download page.
  • Choose the Microsoft Store installation route.
  • Complete the installation of the August 2026 release.

Power BI Desktop is now installed. Use its About window to confirm the release.

  • Press the Windows key to open Windows Search.
  • Type Power BI Desktop into Windows Search.
  • Select Power BI Desktop from the search results.
  • Select Help from the top menu.
  • Select About.
  • Confirm that the application is the 64-bit August 2026 release.

That is the reporting side ready. Power BI Desktop is current on the same Windows computer as your MySQL server.

Power BI Desktop not opening?

  • Restart Windows if the Microsoft Store installation has just completed.
  • Run the Microsoft Store installation again if Power BI Desktop does not appear in Windows Search.
  • Confirm that you selected the supported 64-bit application.
  • Help me troubleshoot Power BI Desktop.
Register the MySQL data provider

Power BI Desktop requires Oracle MySQL Connector/NET before it can use the MySQL database connector. Version 26.7.0 supplies the provider that Windows registers for database applications.

Choose the tab that matches the Connector/NET version on your computer. Use the installation path if you are unsure which version is present.

✔️ I have version 26.7.0

Keep the installed 26.7.0 package. The PowerShell check below confirms whether its provider registration is available.

ⓧ I have an older version

An administrator prompt is expected because the local provider installer adds system components.

  • Open the official MySQL Connector/NET download page.
  • Download the Windows MSI named mysql-connector-net-26.7.0.msi.
  • Run the downloaded MSI.
  • Approve the Windows administrator request if it appears.
  • Complete the MSI installation with the default options.

Connector/NET is now updated to version 26.7.0.

ⓧ It is not installed

An administrator prompt is expected because the local provider installer adds system components.

  • Open the official MySQL Connector/NET download page.
  • Download the Windows MSI named mysql-connector-net-26.7.0.msi.
  • Run the downloaded MSI.
  • Approve the Windows administrator request if it appears.
  • Complete the MSI installation with the default options.

Connector/NET version 26.7.0 is now installed on your Windows computer.

  • Press the Windows key to open Windows Search.
  • Type Windows PowerShell into Windows Search.
  • Press Enter to open Windows PowerShell.

Before you run this check, do you expect the MySQL provider to appear in the grid?

  • Confirm the provider registration by running this command:
[System.Data.Common.DbProviderFactories]::GetFactoryClasses()|ogv

What does this command do?

  • The DbProviderFactories registry lists database providers available to .NET applications on your computer.
  • The ogv suffix opens the results in a searchable grid.
  • A MySQL provider entry confirms that Power BI Desktop can discover the installed Connector/NET provider.

You should see a provider grid with MySQL Data Provider included. You now have the full local toolchain ready for the database-to-dashboard workflow.

MySQL Data Provider missing?

  • Close Windows PowerShell after installing Connector/NET.
  • Open a new Windows PowerShell window before repeating the provider check.
  • Run mysql-connector-net-26.7.0.msi again if the provider remains absent.
  • Help me restore the MySQL provider registration.

✔️ Awesome, I've got everything!

All three checks are complete. Keep MySQL Workbench plus Windows PowerShell available for the next step.

ⓧ I'd like to double check the full code

Your existing local MySQL connection is active in MySQL Workbench. The version query returned your local server version.

MySQL Connector/NET version 26.7.0 is installed. MySQL Data Provider appears in the Windows PowerShell provider grid.

Power BI Desktop is installed as the 64-bit August 2026 release.

Your Windows tools can now support one local analytics workflow. Next, you will load the store data into MySQL and see how row grain can distort a business metric.

Load the Store Data and Explore Data Grain

Your MySQL Workbench connection can now reach the local server. The next risk sits inside the data itself.

An order can contain several products. The joined rows therefore have a different data grain from the orders they describe.

In this step, you will load a small store dataset. You will then test what happens when an order-level metric is calculated from product-line rows.

In this step, get ready to:
  • Create the project SQL files inside your Documents folder.
  • Build four related tables with sample store transactions.
  • Run a joined-row average to expose the data-grain problem.
Create the database and table structure

A relational database keeps customers, products, orders, and purchased items in separate tables. Their keys preserve the connections between those records.

  • Switch back to the existing local connection in MySQL Workbench.
  • Create a new SQL tab using the blank-page control in the SQL editor toolbar.
  • Use the save control in the toolbar to open the file dialog.
  • Navigate to your Documents folder.
  • Create a folder named ecommerce-analytics.
  • Save the script as 01_load_data.sql inside Documents\ecommerce-analytics.
  • Add the database setup statements at the top of 01_load_data.sql by pasting this code:
DROP DATABASE IF EXISTS ecommerce_analytics;
CREATE DATABASE ecommerce_analytics;
USE ecommerce_analytics;

What does this code do?

  • The first statement removes an earlier copy of the project database so the sample can be rebuilt cleanly.
  • The second statement creates ecommerce_analytics.
  • The final statement selects that database for the table statements that follow.
  • Save 01_load_data.sql.
  • Highlight these three statements.
  • Run the highlighted statements with the lightning-bolt control in the SQL editor toolbar.

You should see successful results for all three statements. The ecommerce_analytics database is now selected for this SQL tab.

Database setup failing?

  • Confirm that the local connection from the previous step is still active.
  • Highlight all three statements so the database is created before it is selected.
  • Use the SQL editor toolbar to reconnect if Workbench reports that the connection has closed.
  • Help me diagnose the database setup in MySQL Workbench.
  • Append the customer table definition below the USE ecommerce_analytics; statement:
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    signup_date DATE NOT NULL,
    region VARCHAR(20) NOT NULL
);

How is each customer identified?

The customer_id column is the table's primary key. Each customer must have one unique identifier.

The remaining columns store the customer's name, signup date, and region. Their NOT NULL constraints require a value in every row.

  • Save 01_load_data.sql.
  • Highlight the new CREATE TABLE customers statement.
  • Run the highlighted statement with the lightning-bolt control.
  • Refresh the ecommerce_analytics schema in the left sidebar.

You should see customers under the database's tables.

Customers table missing?

  • Confirm that the SQL tab still uses ecommerce_analytics.
  • Check that the closing parenthesis appears before the final semicolon in the table definition.
  • Help me fix the customers table definition.
  • Append the product table definition below the customers table:
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    unit_cost DECIMAL(10, 2) NOT NULL
);

What does the product table store?

Each product has a unique product_id. The other columns describe its name, category, and cost.

The DECIMAL(10, 2) type stores monetary values with two decimal places. That keeps product costs suitable for later calculations.

  • Save 01_load_data.sql.
  • Highlight the new CREATE TABLE products statement.
  • Run the highlighted statement with the lightning-bolt control.
  • Refresh the schema in the left sidebar.

You should now see products beside customers.

Products table missing?

  • Confirm that you highlighted the complete table statement.
  • Check that unit_cost uses matching parentheses around its decimal precision.
  • Help me troubleshoot the products table.
  • Append the order table definition below the products table:
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    sales_channel VARCHAR(20) NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

How do orders connect to customers?

The customer_id column is a foreign key that points to the matching customer. This prevents an order from referring to a customer that does not exist.

Each order also records its date, sales channel, and status. Those fields support later filtering and trend analysis.

  • Save 01_load_data.sql.
  • Highlight the new CREATE TABLE orders statement.
  • Run the highlighted statement with the lightning-bolt control.
  • Refresh the schema in the left sidebar.

You should see orders as the third table.

Order relationship failing?

  • Confirm that the customers table was created first.
  • Check that both occurrences of customer_id use the same spelling.
  • Help me fix the customer foreign key.
  • Append the order-item table definition below the orders table:
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Why does each item need two relationships?

Each row represents one product line within an order. The order_id connects that line to its order.

The product_id identifies the purchased product. The quantity and selling price complete the transaction line.

  • Save 01_load_data.sql.
  • Highlight the new CREATE TABLE order_items statement.
  • Run the highlighted statement with the lightning-bolt control.
  • Refresh the schema in the left sidebar.

You should see all four tables under ecommerce_analytics.

Order items table failing?

  • Confirm that both orders and products already exist.
  • Check the comma between the two constraint definitions.
  • Help me fix the order-item relationships.
Load the sample store records

The empty tables define the structure of the store. The next statements add a fixed sample so every later metric has a predictable result.

  • Append the customer records below the four table definitions:
INSERT INTO customers (customer_id, customer_name, signup_date, region) VALUES
(1, 'Ava Patel', '2025-12-15', 'North'),
(2, 'Ben Carter', '2026-01-03', 'South'),
(3, 'Chloe Martin', '2026-01-10', 'East'),
(4, 'Daniel Kim', '2026-02-01', 'West'),
(5, 'Elena Garcia', '2026-02-11', 'North'),
(6, 'Farah Ali', '2026-03-04', 'South'),
(7, 'George Brown', '2026-03-20', 'East'),
(8, 'Hana Suzuki', '2026-04-08', 'West'),
(9, 'Isaac Moore', '2026-04-20', 'North'),
(10, 'Julia Rossi', '2026-05-02', 'South');

What does this insert add?

This statement adds ten customers from four regions. Their identifiers provide the parent records required by the orders table.

  • Save 01_load_data.sql.
  • Highlight the customer insert statement.
  • Run the highlighted statement with the lightning-bolt control.

You should see a successful insert for 10 customer rows.

Customer insert failing?

  • Confirm that the customers table is present in the selected database.
  • Check that every customer row has four values.
  • Help me diagnose the customer insert.
  • Append the product records below the customer insert:
INSERT INTO products (product_id, product_name, category, unit_cost) VALUES
(101, 'Wireless Headphones', 'Electronics', 45.00),
(102, 'Mechanical Keyboard', 'Electronics', 35.00),
(103, 'Everyday Hoodie', 'Apparel', 22.00),
(104, 'Running Sneakers', 'Apparel', 40.00),
(105, 'Countertop Blender', 'Home', 30.00),
(106, 'Desk Lamp', 'Home', 18.00),
(107, 'Travel Backpack', 'Accessories', 20.00),
(108, 'Steel Water Bottle', 'Accessories', 8.00);

What does this insert add?

This statement adds eight products across Electronics, Apparel, Home, and Accessories. The cost values become useful when profitability is analyzed later.

  • Save 01_load_data.sql.
  • Highlight the product insert statement.
  • Run the highlighted statement with the lightning-bolt control.

You should see a successful insert for 8 product rows.

Product insert failing?

  • Confirm that each product identifier is unique.
  • Check that every product row has four values.
  • Help me diagnose the product insert.
  • Append the order records below the product insert:
INSERT INTO orders (order_id, customer_id, order_date, sales_channel, order_status) VALUES
(1001, 1, '2026-01-05', 'Web', 'Completed'),
(1002, 2, '2026-01-12', 'Mobile', 'Completed'),
(1003, 3, '2026-01-25', 'Web', 'Completed'),
(1004, 1, '2026-02-10', 'Mobile', 'Completed'),
(1005, 4, '2026-02-18', 'Web', 'Completed'),
(1006, 5, '2026-03-03', 'Marketplace', 'Completed'),
(1007, 2, '2026-03-15', 'Mobile', 'Completed'),
(1008, 6, '2026-03-28', 'Web', 'Completed'),
(1009, 3, '2026-04-07', 'Marketplace', 'Completed'),
(1010, 7, '2026-04-19', 'Web', 'Completed'),
(1011, 1, '2026-05-02', 'Mobile', 'Completed'),
(1012, 8, '2026-05-15', 'Web', 'Completed'),
(1013, 5, '2026-06-01', 'Marketplace', 'Completed'),
(1014, 9, '2026-06-11', 'Mobile', 'Completed'),
(1015, 10, '2026-06-21', 'Web', 'Completed');

What does this insert add?

This statement adds 15 completed orders across Web, Mobile, and Marketplace channels. Several customers place more than one order.

  • Save 01_load_data.sql.
  • Highlight the order insert statement.
  • Run the highlighted statement with the lightning-bolt control.

You should see a successful insert for 15 order rows.

Order insert failing?

  • Confirm that the customer insert completed before this statement.
  • Check that every customer_id matches a customer already in the database.
  • Help me diagnose the order insert.
  • Append the order-item records below the order insert:
INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1001, 101, 1, 90.00),
(2, 1001, 108, 2, 18.00),
(3, 1002, 102, 1, 70.00),
(4, 1003, 103, 2, 50.00),
(5, 1004, 104, 1, 85.00),
(6, 1004, 108, 1, 18.00),
(7, 1005, 105, 1, 65.00),
(8, 1006, 106, 2, 42.00),
(9, 1007, 107, 1, 48.00),
(10, 1007, 108, 2, 18.00),
(11, 1008, 101, 1, 90.00),
(12, 1009, 105, 1, 65.00),
(13, 1009, 106, 1, 42.00),
(14, 1010, 104, 1, 85.00),
(15, 1011, 102, 1, 70.00),
(16, 1011, 107, 1, 48.00),
(17, 1012, 103, 1, 50.00),
(18, 1013, 105, 2, 65.00),
(19, 1014, 101, 1, 90.00),
(20, 1015, 107, 1, 48.00),
(21, 1015, 108, 1, 18.00);

What does this insert add?

This statement adds 21 purchased product lines across the 15 orders. Orders with multiple products receive multiple rows in order_items.

  • Save 01_load_data.sql.
  • Highlight the order-item insert statement.
  • Run the highlighted statement with the lightning-bolt control.

You should see a successful insert for 21 order-item rows. Good progress. Your local store database now has the complete sample dataset.

Order-item insert failing?

  • Confirm that the order insert completed before this statement.
  • Confirm that the product insert completed before this statement.
  • Check that every row has an order identifier and a product identifier.
  • Help me diagnose the order-item insert.

✔️ Awesome, I've got everything!

Great. Confirm that 01_load_data.sql is saved inside Documents\ecommerce-analytics.

ⓧ I'd like to double check the full code

DROP DATABASE IF EXISTS ecommerce_analytics;
CREATE DATABASE ecommerce_analytics;
USE ecommerce_analytics;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    signup_date DATE NOT NULL,
    region VARCHAR(20) NOT NULL
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    unit_cost DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    sales_channel VARCHAR(20) NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO customers (customer_id, customer_name, signup_date, region) VALUES
(1, 'Ava Patel', '2025-12-15', 'North'),
(2, 'Ben Carter', '2026-01-03', 'South'),
(3, 'Chloe Martin', '2026-01-10', 'East'),
(4, 'Daniel Kim', '2026-02-01', 'West'),
(5, 'Elena Garcia', '2026-02-11', 'North'),
(6, 'Farah Ali', '2026-03-04', 'South'),
(7, 'George Brown', '2026-03-20', 'East'),
(8, 'Hana Suzuki', '2026-04-08', 'West'),
(9, 'Isaac Moore', '2026-04-20', 'North'),
(10, 'Julia Rossi', '2026-05-02', 'South');

INSERT INTO products (product_id, product_name, category, unit_cost) VALUES
(101, 'Wireless Headphones', 'Electronics', 45.00),
(102, 'Mechanical Keyboard', 'Electronics', 35.00),
(103, 'Everyday Hoodie', 'Apparel', 22.00),
(104, 'Running Sneakers', 'Apparel', 40.00),
(105, 'Countertop Blender', 'Home', 30.00),
(106, 'Desk Lamp', 'Home', 18.00),
(107, 'Travel Backpack', 'Accessories', 20.00),
(108, 'Steel Water Bottle', 'Accessories', 8.00);

INSERT INTO orders (order_id, customer_id, order_date, sales_channel, order_status) VALUES
(1001, 1, '2026-01-05', 'Web', 'Completed'),
(1002, 2, '2026-01-12', 'Mobile', 'Completed'),
(1003, 3, '2026-01-25', 'Web', 'Completed'),
(1004, 1, '2026-02-10', 'Mobile', 'Completed'),
(1005, 4, '2026-02-18', 'Web', 'Completed'),
(1006, 5, '2026-03-03', 'Marketplace', 'Completed'),
(1007, 2, '2026-03-15', 'Mobile', 'Completed'),
(1008, 6, '2026-03-28', 'Web', 'Completed'),
(1009, 3, '2026-04-07', 'Marketplace', 'Completed'),
(1010, 7, '2026-04-19', 'Web', 'Completed'),
(1011, 1, '2026-05-02', 'Mobile', 'Completed'),
(1012, 8, '2026-05-15', 'Web', 'Completed'),
(1013, 5, '2026-06-01', 'Marketplace', 'Completed'),
(1014, 9, '2026-06-11', 'Mobile', 'Completed'),
(1015, 10, '2026-06-21', 'Web', 'Completed');

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1001, 101, 1, 90.00),
(2, 1001, 108, 2, 18.00),
(3, 1002, 102, 1, 70.00),
(4, 1003, 103, 2, 50.00),
(5, 1004, 104, 1, 85.00),
(6, 1004, 108, 1, 18.00),
(7, 1005, 105, 1, 65.00),
(8, 1006, 106, 2, 42.00),
(9, 1007, 107, 1, 48.00),
(10, 1007, 108, 2, 18.00),
(11, 1008, 101, 1, 90.00),
(12, 1009, 105, 1, 65.00),
(13, 1009, 106, 1, 42.00),
(14, 1010, 104, 1, 85.00),
(15, 1011, 102, 1, 70.00),
(16, 1011, 107, 1, 48.00),
(17, 1012, 103, 1, 50.00),
(18, 1013, 105, 2, 65.00),
(19, 1014, 101, 1, 90.00),
(20, 1015, 107, 1, 48.00),
(21, 1015, 108, 1, 18.00);

How should I use this reference?

Compare the statements with your saved file from top to bottom. The table creation order keeps every referenced parent table available before its related child table.

Run the grain trap

A joined row describes one purchased product line. An order with two products therefore appears twice in the result.

  • Create another SQL tab using the blank-page control in the SQL editor toolbar.
  • Use the save control in the toolbar to open the file dialog.
  • Save the new script as 02_grain_trap.sql inside Documents\ecommerce-analytics.
  • Add the joined-row query by pasting this code:
USE ecommerce_analytics;

SELECT
    o.order_id,
    oi.order_item_id,
    oi.quantity,
    oi.unit_price,
    ROUND(oi.quantity * oi.unit_price, 2) AS line_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.order_item_id;

What does this query reveal?

  • The join matches every order to its purchased product lines.
  • The line_revenue expression multiplies quantity by the selling price for one line.
  • The ordering keeps repeated order identifiers next to each other so the grain is easy to inspect.
  • Save 02_grain_trap.sql.
  • Highlight the joined-row query.
  • Run the highlighted query with the lightning-bolt control.

You should see 21 result rows. Order identifiers such as 1001 appear more than once because those orders contain multiple product lines.

Joined rows not appearing?

  • Confirm that ecommerce_analytics is selected.
  • Confirm that the four inserts in 01_load_data.sql completed successfully.
  • Check that the join uses order_id on both tables.
  • Help me troubleshoot the joined-row query.
  • Append the summary query below the joined-row query:
SELECT
    COUNT(*) AS joined_rows,
    COUNT(DISTINCT o.order_id) AS actual_orders,
    ROUND(AVG(oi.quantity * oi.unit_price), 2) AS misleading_average_line_value
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed';

What does the summary compare?

  • The first count measures joined product-line rows.
  • The distinct count measures unique orders.
  • The average uses the revenue expression from each joined product line.
  • Save 02_grain_trap.sql.

Before you run the summary, predict whether counting joined rows and counting orders will produce the same number.

  • Highlight the summary query.
  • Run the highlighted query with the lightning-bolt control.

You should see joined_rows as 21. You should see actual_orders as 15.

You should see misleading_average_line_value as 65.14. This result averages product lines. Average order value needs one row per order.

Why is this the grain trap?

The query has 21 observations because its grain is one row per order item. Orders containing several products contribute several values to the average.

The business question asks about 15 orders. The grain must be changed before that metric becomes trustworthy.

Different summary values?

  • Confirm that all four inserts from 01_load_data.sql ran exactly once.
  • Rerun all of 01_load_data.sql if duplicate or incomplete records changed the fixture.
  • Run the complete summary query after the database reload finishes.
  • Help me reconcile my grain-trap results.

✔️ Awesome, I've got everything!

That is the designed shortfall confirmed. Your result exposes the gap between line-item grain and order grain.

ⓧ I'd like to double check the full code

USE ecommerce_analytics;

SELECT
    o.order_id,
    oi.order_item_id,
    oi.quantity,
    oi.unit_price,
    ROUND(oi.quantity * oi.unit_price, 2) AS line_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.order_item_id;

SELECT
    COUNT(*) AS joined_rows,
    COUNT(DISTINCT o.order_id) AS actual_orders,
    ROUND(AVG(oi.quantity * oi.unit_price), 2) AS misleading_average_line_value
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed';

How should I use this reference?

Compare both queries with your saved file. The first query exposes the row grain. The second query summarizes the resulting mismatch.

You found the metric trap before it reached a dashboard. Next, you will collapse those 21 product lines into 15 order-level rows and calculate the true average order value.

Create Order-Level Revenue Metrics

The previous grain-trap query returned 21 joined rows for 15 completed orders. That result showed how data grain controls the meaning of an average.

In the MySQL Workbench connection from earlier, you will create a reusable SQL view. The view turns order-item rows into one revenue total per completed order.

In this step, get ready to:
  • Review how grouping changes the dataset from line-item grain to order grain.
  • Create vw_order_summary with one row per completed order.
  • Validate the corrected order-level KPIs.
Review the order-level grouping

One order can contain several order items. A join repeats its order_id across those items.

How does grouping fix the metric?

The expression quantity * unit_price calculates revenue for one line. SUM() combines those line values within each order.

The GROUP BY clause centers the result on order_id. The descriptive order fields keep every output row useful for reporting.

  • In MySQL Workbench from earlier, use the query editor's new-tab control to create a blank SQL tab.
  • Save the blank query as Documents\ecommerce-analytics\03_order_metrics.sql.

The Workbench tab now shows 03_order_metrics.sql. Your order-level analysis has its own saved script.

Can't save the SQL file?

  • Confirm that you selected the existing Documents\ecommerce-analytics folder.
  • Confirm that the filename ends with .sql.
  • Help me save a SQL query from MySQL Workbench into my Documents folder.
Create the order summary view

A SQL view stores a query as a reusable result set. This view fixes the reporting grain at one completed order per row.

  • In 03_order_metrics.sql, paste this statement at the top of the blank editor:
USE ecommerce_analytics;

CREATE OR REPLACE VIEW vw_order_summary AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
    o.sales_channel,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS order_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY
    o.order_id,
    o.customer_id,
    o.order_date,
    DATE_FORMAT(o.order_date, '%Y-%m'),
    o.sales_channel;

What does this view do?

  • The USE ecommerce_analytics statement selects the project database.
  • The CREATE OR REPLACE VIEW statement makes the order summary reusable.
  • The WHERE o.order_status = 'Completed' condition keeps completed orders in the metric.
  • The GROUP BY clause produces one revenue total for each order.
  • Save 03_order_metrics.sql.
  • Highlight the view statement in the query editor.
  • Run the highlighted statement with Workbench's query execution control.

You'll see a successful query status in the output area. The vw_order_summary view now exists in ecommerce_analytics.

View statement not succeeding?

  • Confirm that the earlier customers, products, orders, and order_items tables remain in ecommerce_analytics.
  • Confirm that your highlighted selection includes the complete view statement through o.sales_channel;.
  • Help me troubleshoot my vw_order_summary view statement.

The view now exists as a database object. A result query lets you inspect its grain directly.

  • Append this query below the view statement in 03_order_metrics.sql.
SELECT *
FROM vw_order_summary
ORDER BY order_id;

What does this query check?

  • The SELECT * statement returns every column from the view.
  • The ORDER BY order_id clause places completed orders in numeric order for inspection.
  • Save 03_order_metrics.sql.
  • Highlight the new result query.
  • Run the highlighted query with Workbench's query execution control.

Good progress. You'll see 15 rows with order IDs from 1001 through 1015.

Each order_id appears once. Orders with several items now have one combined order_revenue value.

Don't see 15 order rows?

  • Rerun the complete CREATE OR REPLACE VIEW statement if Workbench reports that the view is missing.
  • Confirm that the view query groups by o.order_id.
  • Help me find why vw_order_summary does not return 15 unique orders.
Validate the corrected KPIs

Every row in the view now represents a completed order. Aggregating this view produces order-level business metrics.

  • Append this validation query at the bottom of 03_order_metrics.sql.
SELECT
    COUNT(*) AS total_orders,
    ROUND(SUM(order_revenue), 2) AS total_revenue,
    ROUND(AVG(order_revenue), 2) AS average_order_value
FROM vw_order_summary;

What does this validation measure?

  • The COUNT(*) expression counts the order-level rows.
  • The SUM(order_revenue) expression calculates revenue across all completed orders.
  • The AVG(order_revenue) expression calculates the average completed-order total.
  • Save 03_order_metrics.sql.

Before you run this check, do you expect the average to stay at 65.14 or rise once each order contributes one row?

  • Highlight the final validation query.
  • Run the highlighted query with Workbench's query execution control.

The total_orders result is 15.

The total_revenue result is 1368.00.

The average_order_value result is 91.20.

That's the grain trap fixed: your KPI now represents complete orders.

Why did the average change?

The earlier query averaged 21 line values. The view first collapses those lines into 15 order totals.

Averaging those 15 totals produces 91.20. The metric now answers the business question about complete orders.

Metrics don't match?

  • Confirm that the validation query reads from vw_order_summary.
  • Confirm that order_revenue uses SUM(oi.quantity * oi.unit_price).
  • Help me diagnose unexpected totals from vw_order_summary.

✔️ Awesome, I've got everything!

Your saved script creates the order-level view. It also includes both validation queries.

ⓧ I'd like to double check the full code

  • Compare your saved 03_order_metrics.sql file with this complete version:
USE ecommerce_analytics;

CREATE OR REPLACE VIEW vw_order_summary AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
    o.sales_channel,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS order_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY
    o.order_id,
    o.customer_id,
    o.order_date,
    DATE_FORMAT(o.order_date, '%Y-%m'),
    o.sales_channel;

SELECT *
FROM vw_order_summary
ORDER BY order_id;

SELECT
    COUNT(*) AS total_orders,
    ROUND(SUM(order_revenue), 2) AS total_revenue,
    ROUND(AVG(order_revenue), 2) AS average_order_value
FROM vw_order_summary;

Your revenue metrics now use the correct order grain. Next, you can use this view to uncover customer behavior and product performance.

Build Customer and Product Analytics Views

Your order-level SQL view now gives every completed order one trustworthy revenue row. That fixed the average order value calculation.

That single view cannot reveal which customers return. It also cannot show which product categories drive sales.

Now you will build reusable customer and product views in MySQL Workbench. These compact result sets feed the Power BI report in the next step.

In this step, get ready to:
  • Segment customers using order counts and lifetime revenue.
  • Prepare enriched order and product performance views.
  • Validate the customer segments and headline business totals.
Segment customer behavior

Customer segmentation groups shoppers by their purchasing behavior. This view uses lifetime revenue to identify VIP customers.

  • In MySQL Workbench, use the new SQL tab control above the editor to create a blank query tab.
  • Save the blank file as 04_customer_product_analysis.sql inside your existing Documents\ecommerce-analytics folder.
  • Add the customer behavior view by pasting this code into the blank file:
USE ecommerce_analytics;

CREATE OR REPLACE VIEW vw_customer_behavior AS
SELECT
    c.customer_id,
    c.customer_name,
    c.region,
    COUNT(os.order_id) AS order_count,
    ROUND(SUM(os.order_revenue), 2) AS lifetime_revenue,
    ROUND(AVG(os.order_revenue), 2) AS customer_aov,
    CASE
        WHEN COUNT(os.order_id) > 1 THEN 1
        ELSE 0
    END AS is_repeat_customer,
    CASE
        WHEN SUM(os.order_revenue) >= 300 THEN 'VIP'
        WHEN COUNT(os.order_id) > 1 THEN 'Repeat'
        ELSE 'One-Time'
    END AS customer_segment
FROM customers AS c
JOIN vw_order_summary AS os
    ON c.customer_id = os.customer_id
GROUP BY
    c.customer_id,
    c.customer_name,
    c.region;

What does this view calculate?

  • The join connects each customer to the order-level revenue rows in vw_order_summary.
  • The COUNT(os.order_id) expression measures how many completed orders each customer placed.
  • The SUM(os.order_revenue) expression calculates each customer's lifetime revenue.
  • The AVG(os.order_revenue) expression calculates each customer's average order value.
  • The first CASE expression stores a numeric repeat-customer flag for reporting.
  • The second CASE expression assigns VIP at a lifetime revenue of 300 or more. Multiple orders produce Repeat. All remaining customers become One-Time.
  • Save 04_customer_product_analysis.sql.
  • Select the code from USE ecommerce_analytics through the final GROUP BY clause.
  • Execute the selected code with the lightning bolt control above the editor.

You will see a successful execution in the output panel.

  • Refresh the ecommerce_analytics schema in the schema sidebar.

You will see vw_customer_behavior in the list of views.

Customer View Creation Failing?

  • Confirm that vw_order_summary still appears under the ecommerce_analytics schema.
  • Check that your selected code includes the complete CREATE OR REPLACE VIEW vw_customer_behavior statement.
  • Verify that the selected statement includes its final semicolon.
  • Ask for help with the specific output from your failed view statement.
Prepare dashboard-friendly result sets

The dashboard needs order details beside customer attributes. An enriched order view adds those reporting fields without changing the one-row-per-order grain.

  • Place your cursor below the existing vw_customer_behavior statement.
  • Add the enriched order view by pasting this code:
CREATE OR REPLACE VIEW vw_order_summary_enriched AS
SELECT
    os.order_id,
    os.customer_id,
    cb.customer_name,
    cb.region,
    cb.customer_segment,
    cb.is_repeat_customer,
    os.order_date,
    os.order_month,
    os.sales_channel,
    os.order_revenue
FROM vw_order_summary AS os
JOIN vw_customer_behavior AS cb
    ON os.customer_id = cb.customer_id;

What does this view add?

  • The view preserves the order identifiers and revenue fields from vw_order_summary.
  • The join adds each customer's name and region.
  • The same join adds the customer segment and repeat-customer flag.
  • Each completed order remains a single row for accurate dashboard measures.
  • Save 04_customer_product_analysis.sql.
  • Select the complete CREATE OR REPLACE VIEW vw_order_summary_enriched statement.
  • Execute the selected statement with the lightning bolt control.

You will see a successful execution in the output panel.

  • Refresh the ecommerce_analytics schema in the schema sidebar.

You will see vw_order_summary_enriched beside your existing views.

Enriched View Creation Failing?

  • Confirm that both vw_order_summary and vw_customer_behavior appear under the same database.
  • Check that the join uses customer_id on both sides.
  • Ask for help checking the enriched view statement and its dependencies.

Orders explain customer behavior. Product performance needs a separate result set at one row per product.

  • Place your cursor below the vw_order_summary_enriched statement.
  • Add the product performance view by pasting this code:
CREATE OR REPLACE VIEW vw_product_performance AS
SELECT
    p.product_id,
    p.product_name,
    p.category,
    SUM(oi.quantity) AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM products AS p
JOIN order_items AS oi
    ON p.product_id = oi.product_id
JOIN orders AS o
    ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY
    p.product_id,
    p.product_name,
    p.category;

How does product grain work?

  • The joins connect each product to its order-item transactions and completed orders.
  • The SUM(oi.quantity) expression calculates units sold for each product.
  • The revenue expression multiplies quantity by selling price before adding the values.
  • The grouping columns keep one result row per product.
  • Save 04_customer_product_analysis.sql.
  • Select the complete CREATE OR REPLACE VIEW vw_product_performance statement.
  • Execute the selected statement with the lightning bolt control.

You will see a successful execution in the output panel.

  • Refresh the ecommerce_analytics schema in the schema sidebar.

You will see vw_product_performance alongside the customer and order views.

Product View Creation Failing?

  • Confirm that the products and order_items tables still contain their sample rows.
  • Check that the two joins use product_id and order_id in the matching clauses.
  • Ask for help reviewing the product joins and grouping columns.
Inspect the business story

The three views are ready. These inspection queries reveal the leading customer and summarize the dashboard categories.

  • Place your cursor below the vw_product_performance statement.
  • Add the customer and category inspection queries by pasting this code:
SELECT *
FROM vw_customer_behavior
ORDER BY lifetime_revenue DESC;

SELECT
    customer_segment,
    COUNT(*) AS customer_count,
    ROUND(SUM(lifetime_revenue), 2) AS segment_revenue
FROM vw_customer_behavior
GROUP BY customer_segment
ORDER BY segment_revenue DESC;

SELECT
    category,
    ROUND(SUM(revenue), 2) AS category_revenue
FROM vw_product_performance
GROUP BY category
ORDER BY category_revenue DESC;

What do these queries reveal?

  • The first query ranks customers by lifetime_revenue.
  • The second query counts customers inside each segment.
  • The second query also calculates the revenue contributed by each segment.
  • The third query rolls product revenue into category totals.
  • Save 04_customer_product_analysis.sql.

Before you run these queries, which customer do you expect to lead the lifetime revenue ranking?

  • Select the three inspection queries you just added.
  • Execute the selected queries with the lightning bolt control.

You will see Ava Patel first with 347.00 in lifetime revenue and a VIP segment.

The segment summary shows one VIP customer, three Repeat customers, and six One-Time customers. The category summary shows Electronics, Apparel, Home, and Accessories.

Inspection Results Look Incomplete?

  • Confirm that you executed all three inspection queries.
  • Check that the customer query sorts lifetime_revenue in descending order.
  • Verify that the product query groups by category.
  • Ask for help comparing your result grids with the expected customer and category summaries.

The final query reconciles customer-level metrics with the revenue total from the order-level view.

  • Append the headline validation query by pasting this code at the bottom of 04_customer_product_analysis.sql:
SELECT
    ROUND(SUM(lifetime_revenue), 2) AS total_revenue,
    COUNT(*) AS total_customers,
    ROUND(100 * SUM(is_repeat_customer) / COUNT(*), 1) AS repeat_customer_rate_pct
FROM vw_customer_behavior;

What does this validation check?

  • The revenue calculation adds each customer's lifetime revenue.
  • The customer count measures the number of rows in the customer-level view.
  • The repeat-customer calculation converts the numeric flags into a percentage.
  • Save 04_customer_product_analysis.sql.

Before you run this final check, do you expect the customer-level revenue to reconcile with the order-level total?

  • Select the final validation query.
  • Execute the selected query with the lightning bolt control.

You will see 1368.00 for total_revenue. You will also see 10 for total_customers.

The repeat_customer_rate_pct value is 40.0. That is the reporting layer complete: your customer and product views now reconcile to the trusted order total.

Headline Totals Do Not Match?

  • Confirm that vw_customer_behavior joins customers to vw_order_summary using customer_id.
  • Check that is_repeat_customer returns 1 only when the order count is greater than 1.
  • Ask for help tracing a mismatch between your customer view and the expected headline totals.

✔️ Awesome, I've got everything!

Great. Double-check that you saved 04_customer_product_analysis.sql inside Documents\ecommerce-analytics.

ⓧ I'd like to double check the full code

USE ecommerce_analytics;

CREATE OR REPLACE VIEW vw_customer_behavior AS
SELECT
    c.customer_id,
    c.customer_name,
    c.region,
    COUNT(os.order_id) AS order_count,
    ROUND(SUM(os.order_revenue), 2) AS lifetime_revenue,
    ROUND(AVG(os.order_revenue), 2) AS customer_aov,
    CASE
        WHEN COUNT(os.order_id) > 1 THEN 1
        ELSE 0
    END AS is_repeat_customer,
    CASE
        WHEN SUM(os.order_revenue) >= 300 THEN 'VIP'
        WHEN COUNT(os.order_id) > 1 THEN 'Repeat'
        ELSE 'One-Time'
    END AS customer_segment
FROM customers AS c
JOIN vw_order_summary AS os
    ON c.customer_id = os.customer_id
GROUP BY
    c.customer_id,
    c.customer_name,
    c.region;

CREATE OR REPLACE VIEW vw_order_summary_enriched AS
SELECT
    os.order_id,
    os.customer_id,
    cb.customer_name,
    cb.region,
    cb.customer_segment,
    cb.is_repeat_customer,
    os.order_date,
    os.order_month,
    os.sales_channel,
    os.order_revenue
FROM vw_order_summary AS os
JOIN vw_customer_behavior AS cb
    ON os.customer_id = cb.customer_id;

CREATE OR REPLACE VIEW vw_product_performance AS
SELECT
    p.product_id,
    p.product_name,
    p.category,
    SUM(oi.quantity) AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM products AS p
JOIN order_items AS oi
    ON p.product_id = oi.product_id
JOIN orders AS o
    ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY
    p.product_id,
    p.product_name,
    p.category;

SELECT *
FROM vw_customer_behavior
ORDER BY lifetime_revenue DESC;

SELECT
    customer_segment,
    COUNT(*) AS customer_count,
    ROUND(SUM(lifetime_revenue), 2) AS segment_revenue
FROM vw_customer_behavior
GROUP BY customer_segment
ORDER BY segment_revenue DESC;

SELECT
    category,
    ROUND(SUM(revenue), 2) AS category_revenue
FROM vw_product_performance
GROUP BY category
ORDER BY category_revenue DESC;

SELECT
    ROUND(SUM(lifetime_revenue), 2) AS total_revenue,
    COUNT(*) AS total_customers,
    ROUND(100 * SUM(is_repeat_customer) / COUNT(*), 1) AS repeat_customer_rate_pct
FROM vw_customer_behavior;

Your reusable analytics views now tell the customer and product story behind the revenue total. Next, you will turn these MySQL results into an interactive Power BI dashboard.

Build the Power BI Dashboard

Your MySQL views now hold trustworthy order metrics. A stakeholder still has to interpret those results from query grids.

Power BI Desktop turns those views into a report a stakeholder can scan in seconds. You will shape the imported data into an interactive dashboard with five measures.

In this step, get ready to:
  • Import the three MySQL analytics views into Power BI Desktop.
  • Create five formatted report measures.
  • Build an interactive report page that responds to slicer selections.
Import the MySQL analytics views

The MySQL connector uses Import mode. Power BI copies the view results into the report for analysis.

  • Press the Windows key to open the Windows search bar.
  • Type Power BI Desktop into the search bar.
  • Press Enter to open Power BI Desktop.

You will see a blank report canvas where the dashboard takes shape.

  • Click Get data in the top ribbon.
  • Select MySQL database from the available connectors.
  • Enter localhost in the Server field.
  • Enter ecommerce_analytics in the Database field.
  • Click OK to continue to authentication.

Keep Your Credentials Private

Database credentials deserve care. Power BI uses the sign-in from your existing local MySQL setup.

  • Keep your MySQL password out of screenshots.
  • Select the Database tab in the authentication dialog.
  • Enter your existing MySQL user name in the User name field.
  • Enter your existing MySQL password in the Password field.
  • Click OK to connect.

Power BI now reads the available objects from ecommerce_analytics. The import can pause briefly while the local rows are loaded.

  • Select vw_order_summary_enriched in Navigator.
  • Select vw_customer_behavior in Navigator.
  • Select vw_product_performance in Navigator.
  • Click Load to import the selected views.

Good progress. The field list now contains the three imported analytics views.

Why Use Import Mode?

Import mode stores the view results inside the Power BI report. The report can use those rows immediately for measures or visuals.

Can't See the MySQL Views?

  • Confirm that the server is localhost.
  • Confirm that the database is ecommerce_analytics.
  • Check that MySQL Data Provider still appears in the provider grid you verified earlier.
  • Ask for help with the MySQL connection.
Create the DAX measures

A DAX measure calculates a result from the report's current filter context. These five measures turn the imported rows into dashboard KPIs.

  • Click Modeling in the top ribbon.
  • Click New measure.
  • Define Total Revenue by entering this formula:
Total Revenue = SUM(vw_order_summary_enriched[order_revenue])

How Total Revenue Works

The measure adds the order_revenue values from the order-level reporting view. Each completed order contributes one revenue value.

  • Press Enter to create Total Revenue.

You will see Total Revenue listed as a measure in the field list.

  • Click New measure.
  • Define Total Orders by entering this formula:
Total Orders = DISTINCTCOUNT(vw_order_summary_enriched[order_id])

How Total Orders Works

The measure counts distinct order_id values. This protects the order count from duplicate rows.

  • Press Enter to create Total Orders.

You will see Total Orders listed with the first measure.

  • Click New measure.
  • Define Total Customers by entering this formula:
Total Customers = DISTINCTCOUNT(vw_order_summary_enriched[customer_id])

How Total Customers Works

The measure counts each customer_id once within the current filter context.

  • Press Enter to create Total Customers.

You will see Total Customers added to the measure list.

  • Click New measure.
  • Define Average Order Value by entering this formula:
Average Order Value = DIVIDE([Total Revenue], [Total Orders], 0)

How Average Order Value Works

The measure divides Total Revenue by Total Orders. The final argument returns zero when no orders match the current filters.

  • Press Enter to create Average Order Value.

You will see Average Order Value added to the measure list.

  • Click New measure.
  • Define Repeat Customer Rate by entering this formula:
Repeat Customer Rate = DIVIDE(SUM(vw_customer_behavior[is_repeat_customer]), DISTINCTCOUNT(vw_customer_behavior[customer_id]), 0)

How Repeat Customer Rate Works

The measure totals the numeric repeat-customer flags. It divides that result by the distinct customer count.

  • Press Enter to create Repeat Customer Rate.

All five measures now appear in the field list.

  • Select Total Revenue in the field list.
  • Choose a currency number format for the selected measure.
  • Select Average Order Value in the field list.
  • Choose a currency number format for the selected measure.
  • Select Repeat Customer Rate in the field list.
  • Choose a percentage number format for the selected measure.

The measures are ready to display revenue as currency. The repeat-customer result is ready to display as a percentage.

✔️ Awesome, I've got everything!

Your five formatted measures are ready for the report canvas.

ⓧ I'd like to double check the full code

Compare your five measures with this complete set:

Total Revenue = SUM(vw_order_summary_enriched[order_revenue])

Total Orders = DISTINCTCOUNT(vw_order_summary_enriched[order_id])

Total Customers = DISTINCTCOUNT(vw_order_summary_enriched[customer_id])

Average Order Value = DIVIDE([Total Revenue], [Total Orders], 0)

Repeat Customer Rate = DIVIDE(SUM(vw_customer_behavior[is_repeat_customer]), DISTINCTCOUNT(vw_customer_behavior[customer_id]), 0)

How to Compare the Measure Set

  • Confirm that every measure name matches the reference exactly.
  • Confirm that the first three measures use the columns shown in the reference.
  • Confirm that both division measures use 0 as the alternate result.
Build and test the report page

Cards surface headline results. Charts reveal how those results change across time or business groups.

  • Select Card visual in the Visualizations pane.
  • Drag Total Revenue into the Values field well.
  • Drag Total Orders into the Values field well.
  • Drag Total Customers into the Values field well.
  • Drag Average Order Value into the Values field well.
  • Drag Repeat Customer Rate into the Values field well.

You will see five KPI values: $1,368.00, 15, 10, $91.20, and 40%.

Your Headline KPIs Are Live

The cards reproduce the validated MySQL results on the report canvas. Your reporting layer now agrees with the source calculations.

  • Click a blank area of the report canvas.
  • Select Line chart in the Visualizations pane.
  • Drag vw_order_summary_enriched[order_month] into X-axis.
  • Drag Total Revenue into Y-axis.

You will see monthly revenue plotted from January through June.

  • Click a blank area of the report canvas.
  • Select Clustered column chart in the Visualizations pane.
  • Drag vw_order_summary_enriched[customer_segment] into X-axis.
  • Drag Total Revenue into Y-axis.

You will see revenue split across the VIP, Repeat, and One-Time customer segments.

  • Click a blank area of the report canvas.
  • Select Clustered column chart in the Visualizations pane.
  • Drag vw_product_performance[category] into X-axis.
  • Drag vw_product_performance[revenue] into Y-axis.

You will see Electronics, Apparel, Home, and Accessories compared by revenue.

What Story Do the Charts Tell?

The line chart answers when revenue changes. The customer chart shows which segments contribute revenue.

The category chart answers what customers buy. Together, the visuals turn separate SQL results into a business story.

  • Click a blank area of the report canvas.
  • Select Slicer in the Visualizations pane.
  • Drag vw_order_summary_enriched[sales_channel] into the slicer field.
  • Click another blank area of the report canvas.
  • Select Slicer in the Visualizations pane.
  • Drag vw_order_summary_enriched[customer_segment] into the slicer field.

The report now has controls for sales channel and customer segment.

  • Click File in Power BI Desktop.
  • Select Save.
  • Choose your existing Documents\ecommerce-analytics folder.
  • Enter ecommerce_customer_analytics.pbix as the report filename.
  • Click Save to store the report.

Before you select a channel, predict whether the four order-based cards stay fixed or recalculate.

  • Select Mobile in the sales-channel slicer.

You will see the revenue, order, customer, and average-order-value cards recalculate. The monthly revenue trend also updates to show Mobile orders.

Dashboard Not Recalculating?

  • Confirm that the sales-channel slicer uses vw_order_summary_enriched[sales_channel].
  • Confirm that the four order-based measures use columns from vw_order_summary_enriched.
  • Select another sales channel to test the filter again.
  • Ask for help with the Power BI slicer.

That's the reporting layer working. Your saved Power BI dashboard now turns the MySQL analysis into interactive business insights.

Secret mission

Add Category Profitability

Revenue can make a category look successful while high product costs reduce what the store keeps. Build a profitability view in MySQL. Add a margin chart to your Power BI dashboard to reveal the strongest category.

Clean Up Your Resources

Clean Up Your Resources

Everything you created stays on your Windows computer, so there are no cloud charges or ongoing project costs. Choose whether to keep the dashboard ready, pause your work, or remove the project entirely.

Resources you used:

  • Local MySQL database named ecommerce_analytics, including four tables and five reporting views.
  • Local Documents\ecommerce-analytics folder containing five SQL files and ecommerce_customer_analytics.pbix.

Keep everything running

No action is needed. Choose this option if you want to refresh the dashboard or demonstrate it again later.

  • Keep the ecommerce_analytics database so the report can continue importing data from localhost.
  • Keep the Documents\ecommerce-analytics folder so your scripts and saved report remain together.
  • Leave Power BI Desktop and MySQL Connector/NET installed for future local reporting projects.

Pause - I'll come back to this later

Close the open desktop applications while preserving the database and project files. You can resume later without rebuilding your analysis.

  • Close Power BI Desktop.
  • Close MySQL Workbench.

The local database and every file remain available. Pausing creates no cloud charge.

Delete - I don't want to use this again

Remove the sample database and local project folder if you no longer need this dashboard. This permanently deletes the project data and report.

The database command targets only ecommerce_analytics. Your MySQL installation and any other databases stay in place.

  • Close Power BI Desktop.
  • Switch back to the MySQL Workbench query tab connected to ecommerce_analytics.
  • Delete only the sample database by running this statement:
DROP DATABASE ecommerce_analytics;

What does this command do?

This statement deletes the ecommerce_analytics database. Its tables and views are removed with it.

  • Refresh the database list in MySQL Workbench.
  • Confirm that ecommerce_analytics no longer appears.

The database cleanup is complete. Only the local project folder remains.

  • Close MySQL Workbench.
  • Delete Documents\ecommerce-analytics with File Explorer.
  • Permanently remove the deleted folder from the Windows Recycle Bin.
  • Return to Documents.
  • Confirm that ecommerce-analytics is no longer listed.

Cleanup complete. The reusable desktop tools stay installed for future projects.

Nice Work!

Nice Work!

You did it! Your MySQL-backed Power BI dashboard now turns normalized e-commerce transactions into interactive revenue insights.

You've learned how to:

  • Build a normalized e-commerce dataset that keeps customers, products, orders, and order items connected.
  • Diagnose a data grain mismatch that treats 21 line-item rows as the unit of analysis. Correct average order value by collapsing them into 15 order rows. The corrected result is 91.20.
  • Create reusable SQL views for customer behavior, product performance, and monthly revenue. Use DAX measures to power KPI cards, charts, and slicers in an interactive report.
  • Complete the optional Secret Mission by adding category profitability analysis to the dashboard. Use gross profit plus profit margin to identify Accessories as the highest-margin category at 57.14 percent.

Ready to quiz yourself?