Retail Sales Analytics Dashboard

Build a star schema in SQL Server and connect it to a Power BI dashboard.

Introduction

30 Second Summary

Every retail company collects thousands of sales transactions, but raw data sitting in a spreadsheet doesn't tell you which products are bleeding money or how this quarter compares to last year.

In this project, you will transform a flat retail dataset into a structured star schema in SQL Server, write analytical queries that surface real business insights, and build an interactive Power BI dashboard that ties it all together.

What You'll Build

You can open your finished dashboard, click a region slicer, and watch KPI cards, trend lines, and profitability charts all update in real time to tell the story of what's selling, what's losing money, and how this year stacks up against last.

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

  • A live dashboard where you open it up, see total sales, profit, profit margin, and order count as KPI cards, then filter by year, region, or category and watch every visual respond instantly.
  • A year-over-year comparison powered by a single DAX time intelligence measure that calculates last year's sales and growth percentage for you.
  • A profit-by-subcategory bar chart that instantly reveals which product lines are losing money, so you can point to Tables and Bookcases as the culprits in a stakeholder meeting.
  • Secret Mission: Classify your customers into behavioral segments like "Champions" and "At Risk" using RFM analysis powered by DAX calculated columns.

Are there any prerequisites?

This project assumes you have SQL Server and SQL Server Management Studio (SSMS) already installed on Windows, along with Power BI Desktop.

You should also be comfortable writing basic SQL (SELECT, WHERE, GROUP BY). The project teaches the advanced patterns like CTEs and window functions from scratch.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. This step is your chance to commit to the goal before any hands-on work begins.

Set Up Your Database and Load Raw Data

You are building a retail analytics dashboard that starts with raw data and ends in an interactive Power BI report. Before you can model, query, or visualize anything, you need that raw data sitting in a database.

In this step, you will create a fresh SQL Server database, import the Superstore retail dataset into it, and confirm the data loaded correctly.

In this step, get ready to:
  • Create the RetailAnalytics database in SQL Server.
  • Import the Superstore CSV into a table using the Import Flat File wizard.
  • Verify that 9,994 rows and 3 product categories loaded successfully.
Create the database and import data

Every analytics project needs a home for its data. You will create a dedicated database in SSMS, then use a built-in wizard to load the CSV without writing any import scripts.

  • Press the Windows key to open your search bar.
  • Type SSMS and press Enter to open SQL Server Management Studio.
  • In the Connect to Server dialog, confirm your server name is set to localhost (or localhost\SQLEXPRESS if you are running SQL Server Express).
  • Leave Authentication set to Windows Authentication and click Connect.

You should see the Object Explorer panel on the left with your server name at the top.

  • Create the project database by running this command in a new query window (click New Query in the toolbar):
-- Create a dedicated database for our retail analytics project
CREATE DATABASE RetailAnalytics;
  • Click Execute in the toolbar (or press F5) to run the statement.

What does this command do?

CREATE DATABASE RetailAnalytics tells SQL Server to create a new empty database called RetailAnalytics. This is where all your fact and dimension tables will live.

  • In the Object Explorer, right-click Databases and select Refresh.
  • You should see RetailAnalytics appear in the list of databases.

Don't see the new database?

Make sure you clicked Execute and the Messages tab at the bottom shows a success confirmation. If you see a permissions error, confirm you are connected with Windows Authentication to your local instance.

Help me troubleshoot my CREATE DATABASE issue

Now you will import the Superstore CSV into this database using the Import Flat File wizard.

  • In the Object Explorer, expand Databases, then right-click RetailAnalytics.
  • Select Tasks, then click Import Flat File.
  • On the Specify Input File page, click Browse and navigate to the Superstore CSV you downloaded from Kaggle.
  • In the New Table Name field, type superstore_orders.
  • Click Next to advance to the Preview Data page. Confirm you can see rows with columns like Order ID, Customer Name, and Sales.
  • Click Next to reach the Modify Columns page. The wizard auto-detects column names and data types. Leave the defaults and click Next.
  • On the Summary page, review the settings and click Finish.
  • Wait for the import to complete. A green check mark on the Results page means the import succeeded.

What did the wizard just do?

The Import Flat File wizard read the CSV, inferred column names and data types, created a table called dbo.superstore_orders inside your RetailAnalytics database, and bulk-loaded all 9,994 rows into it.

The dbo prefix is the default schema that SQL Server assigns to new tables.

Seeing an error on the Results page?

If the wizard reports a data type mismatch, go back to the Modify Columns step and check that numeric columns (Sales, Quantity, Profit, Discount) are set to float or decimal, not int. Also confirm the CSV file is not open in another program like Excel.

Help me fix my CSV import error

Explore the raw data

Before diving into modeling, take a look at what the flat table actually contains. This helps you understand why a star schema will be useful later.

  • Open a new query window by clicking New Query in the toolbar.
  • Make sure the database dropdown (top-left of the query window) is set to RetailAnalytics.
  • Preview the first 10 rows of data by running this query:
-- Preview the raw flat table structure
SELECT TOP 10 *
FROM dbo.superstore_orders;
  • Click Execute to run the query.

You should see a results grid with columns spanning from Order ID all the way to Discount. Notice that customer info (Customer Name, Segment), product info (Category, Sub-Category), and location info (City, State, Region) are all crammed into every single row.

Why is this a problem?

This is a flat table. The same customer name and segment appear in hundreds of rows. The same product name and category repeat across every order that includes them.

This redundancy wastes storage and makes complex time-based analysis awkward. In the next step, you will see these limitations in action before fixing them with a star schema.

Verify the import

Time to confirm the full dataset loaded correctly. Before you run the next query, predict: how many rows do you expect to see?

  • Count the total rows in your table by running this query:
-- Confirm the total number of imported rows
SELECT COUNT(*) FROM dbo.superstore_orders;
  • Click Execute.

You should see a single value: 9994. This confirms all rows from the Superstore CSV loaded into your database.

  • Confirm the product categories loaded correctly by running this query:
-- Check the distinct product categories
SELECT DISTINCT Category
FROM dbo.superstore_orders;
  • Click Execute.

You should see exactly 3 rows: Furniture, Office Supplies, and Technology. These are the three product categories in the Superstore dataset.

Seeing 0 rows or a different count?

Check the database dropdown at the top of your query window. If it says master instead of RetailAnalytics, your query is running against the wrong database. Select RetailAnalytics from the dropdown and re-run.

If you still see 0 rows, the import may have failed silently. Re-run the Import Flat File wizard and check for errors on the Results page.

Help me diagnose my row count issue

Your database is loaded and verified. Next up, you will write queries against this flat table and discover exactly where it falls short for real analytics work.

Query the Flat Table and Hit Its Limits

Your database is loaded and ready for analysis. The natural next move is to start writing queries that answer business questions: which categories make the most money, who your top customers are, and how sales trend over time.

Simple aggregations will work fine on this flat table. But the moment you try more sophisticated analysis, like comparing this year's performance to last year's, you'll hit a wall. That wall is exactly why data analysts build star schemas.

In this step, get ready to:
  • Write aggregation queries for category profitability and top customers.
  • Attempt time-based analysis and observe how awkward it is without a date dimension.
  • Identify data redundancy that motivates a better data model.
Write basic aggregation queries

The first questions any stakeholder asks about retail data are straightforward: which product categories drive the most revenue, and who are our biggest customers? A flat table handles these with basic GROUP BY queries.

  • In SSMS, make sure you have the RetailAnalytics database selected in the database dropdown.
  • Open a new query window by clicking **New Query** in the toolbar.
  • Find total sales and profit by category by running this query:
-- Total sales and profit by product category
SELECT
    Category,
    SUM(Sales) AS TotalSales,
    SUM(Profit) AS TotalProfit
FROM dbo.superstore_orders
GROUP BY Category
ORDER BY TotalSales DESC;

What does this query do?

GROUP BY Category collapses all 9,994 rows into one row per category. SUM(Sales) and SUM(Profit) aggregate the numeric values for each group.

ORDER BY TotalSales DESC sorts the results so the highest-revenue category appears first.

You should see 3 rows in the results grid. Technology and Office Supplies should be the top two categories by sales.

Seeing an error about column names?

If you see an invalid column name error, check that your table was imported with the column name Category (no spaces). Some import methods rename columns. Run SELECT TOP 1 * FROM dbo.superstore_orders; to check the actual column names in your table.

help me fix a column name error in my SQL query

  • Now find the top 5 customers by total sales by running this query:
-- Top 5 customers by total sales revenue
SELECT TOP 5
    [Customer ID],
    [Customer Name],
    SUM(Sales) AS TotalSales
FROM dbo.superstore_orders
GROUP BY [Customer ID], [Customer Name]
ORDER BY TotalSales DESC;

Why the square brackets?

Column names with spaces (like Customer ID) need square brackets in SQL Server. Without them, the parser thinks each word is a separate identifier.

TOP 5 limits the results to five rows. Combined with ORDER BY TotalSales DESC, it gives you the five highest-spending customers.

You should see 5 customer names with their total spend. These basic aggregations work perfectly on the flat table.

Attempt time-based analysis

Category totals are useful, but stakeholders want to see trends over time. Monthly revenue patterns and year-over-year comparisons are core to any retail analysis.

  • Get monthly sales trends by extracting year and month from the order date. Run this query:
-- Monthly sales trend extracted from Order Date
SELECT
    YEAR([Order Date]) AS OrderYear,
    MONTH([Order Date]) AS OrderMonth,
    SUM(Sales) AS MonthlySales
FROM dbo.superstore_orders
GROUP BY YEAR([Order Date]), MONTH([Order Date])
ORDER BY OrderYear, OrderMonth;

What does this query do?

YEAR() and MONTH() extract numeric components from the [Order Date] column. Grouping by both gives you one row per year-month combination.

This works, but notice there's no easy way to group by fiscal quarter or compare seasons without manually mapping month numbers to quarter labels.

You should see rows for each year-month combination (roughly 48 rows covering 2014 through 2017). The query works, but it already feels limited.

Now for the real test. Before you run the next query, think about this: how would you compare each month's sales to the same month last year using just this flat table? Do you think it will be straightforward?

  • Attempt year-over-year growth by running this query:
-- Attempt YoY growth: requires a self-join on the flat table
WITH MonthlySales AS (
    SELECT
        YEAR([Order Date]) AS OrderYear,
        MONTH([Order Date]) AS OrderMonth,
        SUM(Sales) AS MonthlySales
    FROM dbo.superstore_orders
    GROUP BY YEAR([Order Date]), MONTH([Order Date])
)
SELECT
    curr.OrderYear,
    curr.OrderMonth,
    curr.MonthlySales AS CurrentSales,
    prev.MonthlySales AS PriorYearSales,
    CASE
        WHEN prev.MonthlySales > 0
        THEN (curr.MonthlySales - prev.MonthlySales)
             / prev.MonthlySales * 100
        ELSE NULL
    END AS YoYGrowthPct
FROM MonthlySales curr
LEFT JOIN MonthlySales prev
    ON curr.OrderMonth = prev.OrderMonth
    AND curr.OrderYear = prev.OrderYear + 1
ORDER BY curr.OrderYear, curr.OrderMonth;

What's happening here?

To get year-over-year growth from a flat table, you need a CTE (Common Table Expression) to first calculate monthly totals, then a self-join to match each month to the same month one year prior.

The LEFT JOIN matches on month and year+1, so January 2015 pairs with January 2014. The CASE expression calculates the percentage change while avoiding division by zero.

The query runs and returns results. But look at how much machinery that required for a basic year-over-year comparison. No fiscal quarters, no easy seasonal grouping, no reusable date hierarchy.

Why is this painful?

Without a dedicated date dimension table, every time-based question requires re-extracting date parts and writing custom join logic. Want quarterly trends? Write another extraction. Want to compare holiday seasons? Another self-join.

A proper date dimension table pre-computes Year, Quarter, Month, and other attributes for every date. You join to it once and get all time groupings for free.

Identify redundancy in the flat table

The time-analysis problem is one limitation. The other is data redundancy. Every row in the flat table repeats customer information, product details, and location data alongside the transactional facts.

Before running the next query, predict this: how many times do you think a single customer's name and segment appears in 9,994 order rows?

  • Check how many times each customer's info is repeated by running this query:
-- Show how often customer info is repeated in the flat table
SELECT
    [Customer ID],
    [Customer Name],
    Segment,
    COUNT(*) AS RowCount
FROM dbo.superstore_orders
GROUP BY [Customer ID], [Customer Name], Segment
ORDER BY RowCount DESC;

What does this reveal?

Each row in the results represents a unique customer. The RowCount column shows how many times that customer's name, ID, and segment are duplicated across the table.

Some customers appear dozens of times. Every one of those rows stores the same name and segment string. That's wasted space and a maintenance problem. If a customer changes segment, you'd need to update every row.

Look at the top results. You'll see customers with their info repeated across dozens of rows. The same pattern applies to product names, categories, and location data.

Query returning unexpected results?

Make sure you have the RetailAnalytics database selected in the dropdown at the top of your query window. If you see zero rows, your table name may differ. Run SELECT * FROM INFORMATION_SCHEMA.TABLES; to check the actual table name.

help me troubleshoot my SQL query results

This redundancy is the second reason a flat table falls short for real analytics. A star schema fixes both problems by separating measures (sales, profit, quantity) into a fact table and descriptive attributes (customer names, product categories, dates) into dimension tables.

You've felt the two core pain points of a flat table: time-based analysis requires complex self-joins, and descriptive data is duplicated across thousands of rows. In the next step, you'll build a star schema that eliminates both problems and gives Power BI an optimized model to work with.

Build the Star Schema

In the previous step, you felt the pain of querying a flat table. Customer info repeated across hundreds of rows, and computing year-over-year growth required awkward self-joins because there was no date dimension.

A star schema fixes both problems. It separates your measures (sales, profit, quantity) into a central fact table and your descriptive attributes (customer names, product categories, dates) into dimension tables that surround it. This is the industry-standard model for BI tools like Power BI, and building one here proves you can transform messy raw data into a clean analytical structure.

In this step, get ready to:
  • Create four dimension tables (customer, product, location, date) from the raw data.
  • Create a fact table that links to each dimension via foreign keys.
  • Verify the star schema with a join query across multiple tables.
Create the customer dimension table

Each dimension table holds one category of descriptive data with no duplicates. You will start with customers because it is the simplest. One row per unique customer.

  • In SSMS, open a new query window connected to your RetailAnalytics database by clicking New Query in the toolbar.
  • Create and populate the dim_customer table by running this query:
-- Create the customer dimension table
CREATE TABLE dbo.dim_customer (
    CustomerID NVARCHAR(20) PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Segment NVARCHAR(50)
);

-- Populate with unique customers from the raw data
INSERT INTO dbo.dim_customer (CustomerID, CustomerName, Segment)
SELECT DISTINCT
    [Customer ID],
    [Customer Name],
    [Segment]
FROM dbo.superstore_orders;

What does this query do?

The CREATE TABLE statement defines the dimension with CustomerID as the primary key. The INSERT...SELECT DISTINCT pulls each unique combination of customer ID, name, and segment from your flat table. This eliminates the hundreds of duplicate rows you saw earlier.

  • Press F5 or click Execute to run the query.
  • In the Messages pane, you should see two confirmations: the table was created and rows were inserted.

Seeing an error about the table already existing?

If you ran this query before and need to start fresh, run DROP TABLE dbo.dim_customer; first, then re-run the create script.

If you see a column name error, check that your imported table has columns named exactly Customer ID and Customer Name with spaces. The square brackets in the query handle the spaces.

Help me fix my dim_customer query

Create the product dimension table
  • Clear the query window and create the dim_product table by running this query:
-- Create the product dimension table
CREATE TABLE dbo.dim_product (
    ProductID NVARCHAR(50) PRIMARY KEY,
    ProductName NVARCHAR(200),
    Category NVARCHAR(50),
    SubCategory NVARCHAR(50)
);

-- Populate with unique products from the raw data
INSERT INTO dbo.dim_product (ProductID, ProductName, Category, SubCategory)
SELECT DISTINCT
    [Product ID],
    [Product Name],
    [Category],
    [Sub-Category]
FROM dbo.superstore_orders;

What does this query do?

Same pattern as the customer dimension. Each unique product gets one row, storing its name, category, and sub-category. ProductID is the natural key that already exists in the raw data.

  • Press F5 to execute. You should see the table created and rows inserted in the Messages pane.
Create the location dimension table

Unlike customers and products, locations do not have a natural unique ID in the raw data. You need a surrogate key. SQL Server's IDENTITY property auto-generates a unique integer for each row.

  • Create the dim_location table with a surrogate key by running this query:
-- Create the location dimension with a surrogate key
CREATE TABLE dbo.dim_location (
    LocationID INT IDENTITY(1,1) PRIMARY KEY,
    City NVARCHAR(100),
    State NVARCHAR(100),
    Region NVARCHAR(50),
    Country NVARCHAR(50)
);

-- Populate with unique locations from the raw data
INSERT INTO dbo.dim_location (City, State, Region, Country)
SELECT DISTINCT
    [City],
    [State],
    [Region],
    [Country]
FROM dbo.superstore_orders;

Why a surrogate key here?

The raw data has no single column that uniquely identifies a location. A city name alone is not enough because multiple states could share a city name. The IDENTITY(1,1) column generates a unique integer starting at 1, incrementing by 1 for each row. This gives every unique city-state-region-country combination its own ID.

  • Press F5 to execute. You should see the table created and rows inserted.
  • Confirm the surrogate keys were generated by running this quick check:
SELECT TOP 5 * FROM dbo.dim_location;

You should see rows with LocationID values starting at 1, each paired with a unique city-state-region-country combination.

Create the date dimension table

The date dimension is different from the others. Instead of pulling from the raw data, you generate a contiguous range of every single date from 2014 through 2017. This guarantees no gaps, which is a requirement for time intelligence functions in Power BI.

  • Create and populate the dim_date table by running this query:
-- Create the date dimension table
CREATE TABLE dbo.dim_date (
    Date DATE PRIMARY KEY,
    Year INT,
    Quarter INT,
    Month INT,
    MonthName NVARCHAR(20),
    DayOfWeek NVARCHAR(20)
);

-- Generate every date from 2014-01-01 to 2017-12-31
DECLARE @StartDate DATE = '2014-01-01';
DECLARE @EndDate DATE = '2017-12-31';

;WITH DateCTE AS (
    SELECT @StartDate AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateCTE
    WHERE DateValue < @EndDate
)
INSERT INTO dbo.dim_date (Date, Year, Quarter, Month, MonthName, DayOfWeek)
SELECT
    DateValue,
    YEAR(DateValue),
    DATEPART(QUARTER, DateValue),
    MONTH(DateValue),
    DATENAME(MONTH, DateValue),
    DATENAME(WEEKDAY, DateValue)
FROM DateCTE
OPTION (MAXRECURSION 1500);

What does this query do?

A recursive CTE starts at January 1, 2014 and adds one day at a time until December 31, 2017. That produces 1,461 consecutive dates with no gaps.

For each date, it extracts the year, quarter number, month number, month name, and day of the week. The OPTION (MAXRECURSION 1500) raises SQL Server's default recursion limit (100) so the CTE can iterate through all 1,461 days.

  • Press F5 to execute. You should see (1461 rows affected) in the Messages pane.

Seeing a maximum recursion error?

If you see an error about the maximum recursion level being exhausted, make sure you included the OPTION (MAXRECURSION 1500) line at the very end of the query. Without it, SQL Server stops at 100 iterations.

Help me fix my dim_date generation query

Create the fact table structure

The fact table is the center of the star. It holds the measurable numbers (sales, profit, quantity, discount) and foreign keys pointing to each dimension table.

  • Create the fact_sales table by running this query:
-- Create the central fact table
CREATE TABLE dbo.fact_sales (
    OrderID NVARCHAR(50),
    OrderDate DATE,
    ShipDate DATE,
    ShipMode NVARCHAR(50),
    CustomerID NVARCHAR(20),
    ProductID NVARCHAR(50),
    LocationID INT,
    Sales DECIMAL(10,2),
    Quantity INT,
    Discount DECIMAL(5,2),
    Profit DECIMAL(10,2)
);

What does this table hold?

Each row represents one line item from an order. OrderDate links to dim_date. CustomerID links to dim_customer. ProductID links to dim_product. LocationID links to dim_location. The numeric columns (Sales, Quantity, Discount, Profit) are the measures you will aggregate in Power BI.

  • Press F5 to execute. You should see a Commands completed successfully message.
Populate the fact table

Populating the fact table requires joining the raw data to dim_location so you can look up each row's surrogate LocationID. The other foreign keys (CustomerID, ProductID) transfer directly because they already match the dimension primary keys.

  • Populate fact_sales by running this query:
-- Populate the fact table by joining to dim_location for the surrogate key
INSERT INTO dbo.fact_sales (
    OrderID, OrderDate, ShipDate, ShipMode,
    CustomerID, ProductID, LocationID,
    Sales, Quantity, Discount, Profit
)
SELECT
    o.[Order ID],
    CAST(o.[Order Date] AS DATE),
    CAST(o.[Ship Date] AS DATE),
    o.[Ship Mode],
    o.[Customer ID],
    o.[Product ID],
    l.LocationID,
    o.[Sales],
    o.[Quantity],
    o.[Discount],
    o.[Profit]
FROM dbo.superstore_orders o
JOIN dbo.dim_location l
    ON o.[City] = l.City
    AND o.[State] = l.State
    AND o.[Region] = l.Region
    AND o.[Country] = l.Country;

Why the JOIN to dim_location?

CustomerID and ProductID already exist as natural keys in both the raw data and the dimension tables. You can copy them directly. But LocationID is a surrogate key that only exists in dim_location. The JOIN matches each raw row to its location by city, state, region, and country, then grabs the generated LocationID value.

  • Press F5 to execute. You should see (9994 rows affected) confirming every row from the original table made it into the fact table.

Seeing fewer than 9,994 rows?

If the row count is lower, the JOIN to dim_location is dropping rows. This means some city-state-region-country combinations in superstore_orders do not have a match in dim_location. Check for trailing spaces or inconsistent casing in location columns.

Help me debug my fact_sales INSERT join

Verify the star schema relationships

All five tables are created and populated. The final check proves that the foreign key relationships actually work by joining across fact and dimension tables in a single query.

  • Before you run the verification, predict: if the star schema is correctly built, what columns would you expect to see from a JOIN across fact_sales, dim_customer, and dim_product?
  • Run this verification query:
-- Verify relationships work across the star schema
SELECT TOP 10
    f.Sales,
    f.Profit,
    c.CustomerName,
    c.Segment,
    p.Category,
    p.SubCategory
FROM dbo.fact_sales f
JOIN dbo.dim_customer c ON f.CustomerID = c.CustomerID
JOIN dbo.dim_product p ON f.ProductID = p.ProductID;

You should see 10 rows with Sales and Profit values from the fact table alongside customer names and product categories pulled from the dimension tables. Each row combines data from three separate tables through the JOIN relationships.

  • Now confirm the date dimension covers the full range by running:
SELECT COUNT(*) AS TotalDates FROM dbo.dim_date;

You should see 1461 as the result. That is every date from January 1, 2014 through December 31, 2017 with no gaps.

Why does the date count matter?

Power BI's time intelligence functions (like year-over-year comparisons) require a contiguous date column with no missing dates. If you had 1,400 rows instead of 1,461, it would mean some dates are missing and your DAX calculations could return incorrect results.

Your star schema is built and verified. Next up, you will write analytical queries that take advantage of this clean structure to surface real business insights using CTEs, window functions, and multi-table joins.

Write Analytical Queries on the Star Schema

Your star schema separates facts from dimensions, which means complex analytical queries that were painful on the flat table become straightforward.

In this step, you will write three queries that demonstrate the SQL skills hiring managers look for: CTEs, window functions, and multi-table joins.

In this step, get ready to:
  • Identify money-losing sub-categories with a CTE profitability analysis.
  • Compute month-over-month and year-over-year growth using LAG window functions.
  • Rank top products per category using RANK() and PARTITION BY.
Analyze profitability by sub-category using a CTE

The first business question any retail analyst asks is: which products are actually making money? A CTE (Common Table Expression) lets you define a named temporary result set, then query it cleanly in a second step.

  • In SSMS, open a new query window by pressing Ctrl+N.
  • Make sure the RetailAnalytics database is selected in the database dropdown at the top.
  • Paste the following profitability query:
-- CTE to calculate profitability metrics per sub-category
WITH subcategory_profitability AS (
    SELECT
        p.SubCategory,
        SUM(f.Sales) AS TotalSales,
        SUM(f.Profit) AS TotalProfit,
        SUM(f.Profit) / NULLIF(SUM(f.Sales), 0) AS ProfitMargin
    FROM dbo.fact_sales f
    JOIN dbo.dim_product p ON f.ProductID = p.ProductID
    GROUP BY p.SubCategory
)
SELECT
    SubCategory,
    TotalSales,
    TotalProfit,
    ROUND(ProfitMargin * 100, 2) AS ProfitMarginPct
FROM subcategory_profitability
ORDER BY TotalProfit ASC;

What does this query do?

  • The WITH clause defines a CTE called subcategory_profitability that aggregates sales and profit from fact_sales joined to dim_product.
  • NULLIF prevents division-by-zero errors when calculating profit margin.
  • Ordering by TotalProfit ASC puts the biggest money-losers at the top of the results.

Before you run this, what do you think you will see at the top of the results? Will every sub-category be profitable, or might some be losing money?

  • Press F5 to execute the query.

You should see Tables and Bookcases at the top with negative TotalProfit values and negative ProfitMarginPct. These sub-categories are actively losing money for the business.

Seeing an error about invalid column names?

  • Check that your dim_product table has a column called SubCategory (not Sub-Category or Sub_Category). Run SELECT TOP 1 * FROM dbo.dim_product; to verify the column names.
  • Confirm the database dropdown at the top of SSMS shows RetailAnalytics, not master.

Help me debug my profitability CTE query.

Compute time-based trends with window functions

Time-based analysis is where the star schema pays off. Because dim_date already has Year and Month columns, you can group by them directly instead of extracting date parts from raw strings.

  • Open a new query window by pressing Ctrl+N.
  • Write the monthly sales trend query with month-over-month growth by pasting this:
-- Monthly sales with month-over-month growth using LAG
WITH monthly_sales AS (
    SELECT
        d.Year,
        d.Month,
        d.MonthName,
        SUM(f.Sales) AS MonthlySales
    FROM dbo.fact_sales f
    JOIN dbo.dim_date d ON f.OrderDate = d.Date
    GROUP BY d.Year, d.Month, d.MonthName
)
SELECT
    Year,
    Month,
    MonthName,
    MonthlySales,
    LAG(MonthlySales) OVER (ORDER BY Year, Month) AS PrevMonthSales,
    ROUND(
        (MonthlySales - LAG(MonthlySales) OVER (ORDER BY Year, Month))
        / NULLIF(LAG(MonthlySales) OVER (ORDER BY Year, Month), 0) * 100,
        2
    ) AS MoMGrowthPct
FROM monthly_sales
ORDER BY Year, Month;

What does LAG do here?

LAG is a window function that looks back one row in the ordered result set. By ordering the CTE results by Year, Month, LAG(MonthlySales) returns the previous month's sales.

The growth percentage formula divides the difference between current and previous month by the previous month's value. The first row will show NULL for growth because there is no prior month to compare against.

  • Press F5 to execute.
  • Scroll through the results. You should see monthly rows from 2014 through 2017, with MoMGrowthPct showing positive and negative swings each month.

Now write the year-over-year comparison. This uses the same LAG pattern but groups at the annual level.

  • Open a new query window by pressing Ctrl+N.
  • Paste the YoY growth query:
-- Year-over-year sales growth using LAG
WITH yearly_sales AS (
    SELECT
        d.Year,
        SUM(f.Sales) AS AnnualSales
    FROM dbo.fact_sales f
    JOIN dbo.dim_date d ON f.OrderDate = d.Date
    GROUP BY d.Year
)
SELECT
    Year,
    AnnualSales,
    LAG(AnnualSales) OVER (ORDER BY Year) AS PrevYearSales,
    ROUND(
        (AnnualSales - LAG(AnnualSales) OVER (ORDER BY Year))
        / NULLIF(LAG(AnnualSales) OVER (ORDER BY Year), 0) * 100,
        2
    ) AS YoYGrowthPct
FROM yearly_sales
ORDER BY Year;

What does this query do?

  • The CTE aggregates total sales per year using the Year column from dim_date.
  • LAG grabs last year's total, and the outer query computes the percentage change.
  • This is exactly the kind of query that was awkward on the flat table. No self-joins needed.
  • Press F5 to execute.
  • You should see 4 rows (2014 through 2017) with YoYGrowthPct showing NULL for 2014 and positive growth for subsequent years.

Getting zero rows or NULL for everything?

  • Make sure f.OrderDate and d.Date are the same data type. If one is datetime and the other is date, the join may silently produce no matches. Cast with CAST(f.OrderDate AS date) if needed.
  • Check that dim_date is populated by running SELECT COUNT(*) FROM dbo.dim_date; to confirm it has rows.

My time-based trend query returns NULL or empty results.

Rank top products per category with RANK()

The final query answers: what are the best-selling products in each category? RANK() combined with PARTITION BY assigns a rank within each group, so you can filter to the top N without writing separate queries per category.

  • Open a new query window by pressing Ctrl+N.
  • Paste the top-3 products ranking query:
-- Top 3 products per category by total sales
WITH ranked_products AS (
    SELECT
        p.Category,
        p.ProductName,
        SUM(f.Sales) AS TotalSales,
        RANK() OVER (
            PARTITION BY p.Category
            ORDER BY SUM(f.Sales) DESC
        ) AS SalesRank
    FROM dbo.fact_sales f
    JOIN dbo.dim_product p ON f.ProductID = p.ProductID
    GROUP BY p.Category, p.ProductName
)
SELECT
    Category,
    ProductName,
    TotalSales,
    SalesRank
FROM ranked_products
WHERE SalesRank <= 3
ORDER BY Category, SalesRank;

What does PARTITION BY do?

PARTITION BY divides the result set into groups (one per Category). RANK() then assigns rankings independently within each group.

The outer WHERE SalesRank <= 3 filters to only the top 3 per category. This pattern scales to any top-N analysis without rewriting the query.

Before you run this, how many rows do you expect to see? There are 3 categories, and you are asking for the top 3 in each.

  • Press F5 to execute.

You should see 9 rows (or slightly more if ties exist). Each of the 3 categories shows its top-selling products ranked 1, 2, and 3.

Seeing more than 9 rows?

  • That is expected if products are tied in total sales. RANK() assigns the same rank to ties and skips the next number. If you want exactly 3 rows per category with no ties, replace RANK() with ROW_NUMBER() which breaks ties arbitrarily.

Help me understand the difference between RANK and ROW_NUMBER.

You now have three production-quality analytical queries demonstrating CTEs, window functions, and multi-table joins on your star schema. Next up, you will connect Power BI to this database and turn these insights into an interactive dashboard that stakeholders can explore themselves.

Build the Power BI Dashboard

Your star schema is built and your analytical queries prove the data is solid. Now it is time to build what stakeholders actually see: an interactive dashboard.

In this step, you will connect Power BI Desktop to your SQL Server star schema, create DAX measures for KPIs and year-over-year comparisons, then assemble a polished report page with cards, charts, and slicers.

In this step, get ready to:
  • Connect Power BI to your SQL Server database and verify star schema relationships.
  • Create DAX measures for KPIs and time intelligence.
  • Build an interactive report page with KPI cards, a trend line chart, a profitability bar chart, and slicers.
Connect Power BI to SQL Server

Power BI needs a live connection to your RetailAnalytics database to pull in the star schema tables you built.

  • Open Power BI Desktop. If it is not pinned to your taskbar, press the Windows key, type Power BI Desktop, and press Enter.
  • On the Home tab, click Get Data.
  • Select SQL Server database, then click Connect.
  • In the Server field, enter your SQL Server instance name. If you are using SQL Server Express, enter localhost\SQLEXPRESS. For a default instance, enter localhost.
  • In the Database field, enter RetailAnalytics.
  • Under Data Connectivity mode, select Import.
  • Click OK.

Why Import mode instead of DirectQuery?

Import mode loads a snapshot of your data into Power BI's in-memory engine. This makes DAX time intelligence functions like SAMEPERIODLASTYEAR work correctly. DirectQuery has performance limitations with time intelligence and does not support time intelligence quick measures.

  • In the Navigator window, check the boxes for all five tables: dim_customer, dim_date, dim_location, dim_product, and fact_sales.
  • Click Load.

Power BI imports the data. You should see all five tables listed in the Fields pane on the right side of your screen.

  • Switch to Model view by clicking the Model icon in the left sidebar (three connected boxes).

You should see all five tables displayed as boxes with relationship lines connecting fact_sales to each dimension table. If any relationship is missing, drag the foreign key column from fact_sales onto the matching primary key in the dimension table to create it manually.

Not seeing relationship lines?

Power BI auto-detects relationships based on matching column names. If your column names differ slightly between fact and dimension tables, you need to create the relationships manually. Drag CustomerID from fact_sales to CustomerID in dim_customer. Repeat for ProductID, LocationID, and OrderDate to dim_date[Date].

Help me set up relationships in Power BI Model view

Now mark dim_date as the official date table so DAX time intelligence functions work correctly.

  • In the Fields pane, right-click the dim_date table.
  • Select Mark as date table > Mark as date table.
  • In the dialog that appears, select the Date column from the dropdown, then click OK.

Why mark as date table?

DAX time intelligence functions like SAMEPERIODLASTYEAR require a dedicated date table with no missing dates between the first and last dates. Marking the table tells Power BI which column to use as the date key for these calculations.

Create DAX measures

DAX measures are formulas that calculate values dynamically based on the current filter context. You will create six measures that power your dashboard KPIs and time comparisons.

  • Switch to Report view by clicking the chart icon in the left sidebar.
  • In the Fields pane, right-click the fact_sales table.
  • Select New measure.
  • In the formula bar at the top, type the following and press Enter to commit:
Total Sales = SUM(fact_sales[Sales])
  • Right-click fact_sales again, select New measure, and enter:
Total Profit = SUM(fact_sales[Profit])

To quickly verify these measures work, drag Total Sales from the Fields pane onto the blank canvas. A card visual appears showing a dollar value. This confirms your connection and measure are working.

What do these measures do?

Each measure aggregates a column from fact_sales. Total Sales sums all revenue. Total Profit sums all profit. DAX evaluates these dynamically, so when you filter by region or year, the values recalculate automatically.

  • Create two more measures on fact_sales using the same process (right-click > New measure). Enter each formula and press Enter to commit:
Profit Margin % = DIVIDE([Total Profit],[Total Sales])
Total Orders = DISTINCTCOUNT(fact_sales[OrderID])

What do these measures do?

The DIVIDE function safely divides two values, returning BLANK() instead of an error when the denominator is zero. DISTINCTCOUNT counts unique order IDs, giving you the total number of distinct orders rather than the row count.

Now delete the test card visual you created earlier (click it, press Delete). You will build the final layout in the next substep.

The last two measures use time intelligence to compare this year's sales to last year's.

  • Create a new measure on fact_sales with this formula:
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(dim_date[Date]))
  • Create one final measure on fact_sales:
YoY Growth % = VAR SalesPriorYear = [Sales LY] RETURN DIVIDE(([Total Sales] - SalesPriorYear), SalesPriorYear)

What do the time intelligence measures do?

The SAMEPERIODLASTYEAR function shifts the date filter context back by one year. Sales LY calculates the same Total Sales measure but in last year's date range.

The YoY Growth % measure uses the DAX VAR pattern to store last year's value, then divides the difference by that baseline. This gives you a percentage growth rate.

Seeing a blank for Sales LY or YoY Growth %?

Time intelligence measures return BLANK() for the first year in your dataset (2014) because there is no prior year to compare against. This is expected. When you add a Year slicer later and select 2015, 2016, or 2017, the values will populate.

If all years show BLANK(), confirm that you marked dim_date as the date table in the previous substep. Without this, SAMEPERIODLASTYEAR cannot resolve the date context.

Help me troubleshoot blank time intelligence measures

Build the report page

With all six measures ready, you can now assemble the dashboard layout. You will place KPI cards across the top, a line chart for trends, a bar chart for profitability, and slicers for interactivity.

  • Make sure you are in Report view with a blank canvas.
  • In the Visualizations pane, click the Card visual icon (a rectangle with a large number).
  • Drag Total Sales from the Fields pane into the card's Fields well.
  • Resize and position the card in the top-left area of the canvas.
  • Repeat for three more cards: Total Profit, Profit Margin %, and YoY Growth %. Line them up across the top of the page.

You should see four card visuals across the top showing numeric values. The YoY Growth % card may show blank if the full date range is selected (2014 has no prior year).

  • Click an empty area on the canvas, then select the Line chart visual in the Visualizations pane.
  • Drag dim_date[Date] to the X-axis well.
  • Drag Total Sales to the Y-axis well.
  • Resize the chart to fill the left half of the canvas below the KPI cards.

You should see a line chart showing monthly sales trends across the dataset's date range.

  • Click an empty area, then select the Clustered bar chart visual.
  • Drag dim_product[SubCategory] to the Y-axis well.
  • Drag Total Profit to the X-axis well.
  • Click the three dots on the visual header, then select Sort axis > Total Profit, and set it to sort ascending. This puts the most unprofitable sub-categories at the top.
  • Position this chart on the right half of the canvas below the KPI cards.

You should see sub-categories like Tables and Bookcases with negative profit bars extending to the left, confirming the pattern you found in your SQL queries.

  • Click an empty area, then select the Slicer visual.
  • Drag dim_location[Region] into the slicer's Field well. Position it above or beside your charts.
  • Create a second slicer and drag dim_date[Year] into it. Position it next to the Region slicer.

You now have two slicers that filter every visual on the page.

  • Double-click the page title area at the top of the canvas (or go to Format pane > Page information > Name) and rename the page to Retail Sales Executive Dashboard.
  • Select all visuals and apply a consistent color theme by going to View tab > Themes and choosing a professional theme that suits your style.
  • Save your file as RetailAnalyticsDashboard.pbix by pressing Ctrl+S.

Before you test interactivity, what do you think will happen to the YoY Growth % card when you select 2016 in the Year slicer?

  • Click 2016 in the Year slicer.

All visuals update instantly. The YoY Growth % card now shows a value because 2016 has a prior year (2015) to compare against. The line chart narrows to only 2016 months. The bar chart recalculates profitability for that year alone.

  • Click a region in the Region slicer (for example, West) and confirm that the KPI cards, line chart, and bar chart all respond to the filter.

Slicer not filtering other visuals?

Make sure all visuals are on the same page and that no visual-level filters are overriding the slicer. Click on the unresponsive visual and check the Filters pane to ensure no conflicting filter is applied.

Help me fix my Power BI slicer interactions

Your executive dashboard is live and interactive. Next up, the Secret Mission challenges you to add RFM customer segmentation using advanced DAX calculated columns.

Secret mission

Add RFM Customer Segmentation

RFM (Recency, Frequency, Monetary) segmentation classifies customers by their purchasing behavior. In this extension, you will create DAX calculated columns that score each customer on three dimensions and assign them a segment label like Champion or At Risk. This is the kind of advanced analytics that separates basic dashboards from actionable business intelligence.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely on your local machine with free tools, so there are no ongoing costs.

Resources you used:

  • SQL Server database: RetailAnalytics (local instance, includes all dimension and fact tables).
  • Power BI report file: RetailAnalyticsDashboard.pbix (saved locally).
  • Downloaded Superstore CSV file (from Kaggle).

Keep everything running

No action needed. Choose this if you are still actively building on the dashboard or plan to add more features soon.

  • Your RetailAnalytics database stays in SQL Server and uses minimal disk space (~2 MB for 9,994 rows plus dimensions).
  • Your RetailAnalyticsDashboard.pbix file remains wherever you saved it.
  • The Superstore CSV stays in your Downloads folder (or wherever you placed it).

Pause - I'll come back to this later

Shut down SQL Server to free up memory while keeping all your files and data intact for later.

  • Open the Windows Services app (press the Windows key and type Services).
  • Find SQL Server (MSSQLSERVER) or SQL Server (SQLEXPRESS) in the list.
  • Right-click it and select Stop.
  • When you want to resume, right-click the same service and select Start.
  • Your database, Power BI file, and CSV all remain untouched on disk.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your database and files.

  • Drop the database: open SSMS, connect to your local instance, open a New Query window, and run the following command:
  • Close Power BI Desktop if it is still open.
USE master;
GO
ALTER DATABASE RetailAnalytics SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE RetailAnalytics;
GO
  • Delete the Power BI report file: open File Explorer, navigate to where you saved RetailAnalyticsDashboard.pbix, right-click it, and select Delete.
  • Delete the Superstore CSV: find the downloaded CSV file in your Downloads folder (or wherever you saved it), right-click it, and select Delete.
  • Empty your Recycle Bin to permanently remove the files from disk.

Nice Work!

Nice Work!

You just built a complete retail analytics dashboard from scratch. You went from a flat 9,994-row CSV all the way to an interactive executive dashboard backed by a proper star schema in SQL Server.

You've learned how to:

  • Build a star schema data model in SQL Server by separating a flat dataset into fact and dimension tables, proving you can structure data for analytics.
  • Write analytical SQL queries using CTEs, window functions (LAG, RANK), and multi-table joins to surface business insights like unprofitable sub-categories and month-over-month growth.
  • Connect Power BI to SQL Server and build an interactive dashboard with DAX measures including time intelligence (SAMEPERIODLASTYEAR) for year-over-year comparisons.
  • Secret Mission: Added RFM customer segmentation with DAX calculated columns, classifying customers into actionable segments like Champion and At Risk.

Ready to quiz yourself?