How I Built an Ecommerce Sales Dashboard Using MySQL and Power BI

Building an Ecommerce Database and Power BI Dashboard from Scratch

A step-by-step walkthrough of designing a relational database in MySQL, writing analytical queries, and turning the data into an interactive Power BI dashboard.

As part of building my portfolio in Business Analytics, I wanted a project that covered the full data pipeline: designing a database, populating it with realistic data, writing SQL to extract insights, and finally presenting those insights visually. Here is how I built it.

Step 1: Designing the Database

I started with three tables that represent a typical ecommerce business: customers, products, and orders. The orders table connects to both customers and products through foreign keys, which is what makes relational analysis possible later on.

USE Ecommerce_db;
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(20),
    email VARCHAR(30),
    gender VARCHAR(10)
);
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT
);
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    status VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

USE Ecommerce_db;
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(20),
    email VARCHAR(30),
    gender VARCHAR(10)
);
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT
);
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    status VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

One lesson I learned here: foreign keys only work when the column names and data types match exactly between the referencing and referenced tables. A small naming mismatch between customer_id and customers_id cost me a fair bit of debugging time before I caught it.

Step 2: Populating the Tables

With the schema in place, I inserted sample data: 10 customers across different cities, 8 products spanning categories like Electronics, Fashion, Grocery, and Furniture, and 12 orders linking the two together with varying quantities, dates, and statuses.

INSERT INTO customers VALUES
(1,'Priya Sharma','Delhi','priya@gmail.com','Female'),
(2,'Priti Dubey','Delhi','priti@gmail.com','Female');
-- and so on

INSERT INTO customers VALUES
(1,'Priya Sharma','Delhi','priya@gmail.com','Female'),
(2,'Priti Dubey','Delhi','priti@gmail.com','Female');
-- and so on

Step 3: Writing Analytical Queries

This is where the database starts answering business questions. A few examples:

Total sales across all delivered orders:

SELECT SUM(price * quantity) AS total_sales
FROM orders
JOIN products USING(product_id);

SELECT SUM(price * quantity) AS total_sales
FROM orders
JOIN products USING(product_id);

Sales broken down by category:

SELECT category, SUM(price * quantity) AS sales
FROM orders
JOIN products USING(product_id)
GROUP BY category;

SELECT category, SUM(price * quantity) AS sales
FROM orders
JOIN products USING(product_id)
GROUP BY category;

Delivered versus cancelled order counts:

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

These three queries alone surface most of the headline numbers a business stakeholder would ask for first.

Step 4: Connecting MySQL to Power BI

To visualize the data, I connected MySQL to Power BI using the MySQL Connector ODBC. After installing the connector, the connection in Power BI itself takes just a few steps: Get Data, select MySQL Database, enter the server and database name, then choose which tables to load.

Step 5: Building the Dashboard

With the data loaded, I built out a set of DAX measures to power the visuals, for example:

Total Sales = SUMX('Ecommerce_db orders', RELATED('Ecommerce_db products'[price]) * 'Ecommerce_db orders'[quantity])

Total Sales = SUMX('Ecommerce_db orders', RELATED('Ecommerce_db products'[price]) * 'Ecommerce_db orders'[quantity])

The final dashboard includes summary cards for total sales, total orders, delivered orders, total customers, and total products, alongside a bar chart for category-wise sales, a donut chart for gender distribution, a pie chart for order status, a line chart for yearly order trends, a map for city-wise customer distribution, and a table ranking top customers by sales. Slicers for city, year, and gender make the whole dashboard interactive.

What I Learned

Beyond the technical steps, this project reinforced a few things: keep column naming consistent across tables from the start, foreign key errors are almost always a schema mismatch rather than a logic error, and a dashboard is only as good as the measures behind it, so it is worth spending time getting the DAX right before worrying about layout and colors.

The full SQL script and dashboard file are available on my GitHub: Ecommerce-SQL-PowerBI-dashboard.