Sales Analytics with Microsoft Fabric

Build an end-to-end BI solution with a star schema and Power BI dashboard.

Introduction

30 Second Summary

Every business decision starts with a question about the numbers. Whether it's "which product category is driving revenue?" or "which region is underperforming?", the answer lives in the data.

In this project, you will build a complete end-to-end sales analytics solution inside Microsoft Fabric. You'll generate sample data, model it into a star schema, write business metrics, and deliver an interactive dashboard.

What You'll Build

A browser-based Business Intelligence solution that takes raw sales data from a Lakehouse through to an interactive Power BI dashboard, all running inside Microsoft Fabric on your Mac.

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

  • A Fabric workspace with a Lakehouse containing 4 Delta tables arranged in a star schema (1 fact table, 3 dimension tables).
  • A semantic model with star schema relationships and DAX measures calculating business metrics like Total Revenue and Revenue Growth.
  • An interactive sales dashboard with KPI cards, charts, and slicers that cross-filter your data in real time.
  • Secret Mission: Add time intelligence DAX measures and a drill-through page for customer-level detail.

Are there any prerequisites?

You'll need a Microsoft 365 account, familiarity with Power BI concepts like visuals and filters, and basic SQL knowledge (SELECT, JOIN, GROUP BY). No prior experience with Microsoft Fabric is required.

Before We Start

Before We Start...

Set Up Your Fabric Workspace

Before you can build a dashboard, you need a place to store your data and run analytics.

Microsoft Fabric bundles data engineering, SQL analytics, and Power BI into a single browser-based platform. In this step, you will activate your free trial, create a workspace, and spin up a Lakehouse to hold your sales data.

In this step, get ready to:
  • Activate your 60-day Microsoft Fabric trial.
  • Create a workspace with Trial capacity.
  • Create a Lakehouse to store your Delta tables.
Activate your Microsoft Fabric trial

Microsoft Fabric offers a free 60-day trial with 64 CU capacity and 1 TB of OneLake storage. No credit card required.

  • Open your browser and navigate to app.fabric.microsoft.com.
  • Sign in with your Microsoft 365 account.
  • Click your profile icon in the top-right corner to open the Account Manager.
  • Select Start trial (you may see it labeled Free trial).
  • Choose your default region.
  • Click Activate.

You should see a confirmation message that your Fabric trial is now active.

What is a Lakehouse?

A Lakehouse combines the flexibility of a data lake with the structure of a data warehouse. It stores data as Delta tables in OneLake, which means you can write data with PySpark notebooks and query it with SQL from the same place.

Later, when you build your semantic model, Fabric loads data directly from OneLake into memory. This is called Direct Lake mode. No duplication, no extra imports.

Trial not activating?

Microsoft has blocked Fabric trials on newly created tenants. If your Microsoft 365 account was created very recently, you may need to wait or contact your tenant administrator.

Also make sure third-party cookies are enabled for app.powerbi.com in your browser settings. Fabric requires them to function correctly.

Help me troubleshoot my Fabric trial activation

Create a new workspace

A workspace is a container that holds all of your Fabric items: lakehouses, notebooks, semantic models, and reports. You will create one workspace for this entire project.

  • Select Workspaces in the left navigation panel.
  • Click + New workspace.
  • Enter Sales Analytics Project as the workspace name.
  • Expand the Advanced section.
  • Set the Workspace type to Trial.
  • Click Apply.

Your new workspace opens. You should see the workspace name in the top-left of the screen with an empty item list.

Don't see the Trial option?

If Trial does not appear under the Workspace type dropdown, your Fabric trial may not have activated successfully. Go back to your Account Manager (profile icon, top-right) and confirm the trial status shows as active.

Help me find the Trial capacity option for my workspace

Create a Lakehouse
  • Inside your Sales Analytics Project workspace, click + New item.
  • Select Lakehouse from the list of item types.
  • Enter sales_lakehouse as the name.
  • Click Create.

Fabric creates the Lakehouse and opens it. You should see two empty sections on the left panel: Tables and Files.

Why a Lakehouse instead of a Warehouse?

Fabric offers both Lakehouse and Warehouse items. The Lakehouse lets you use PySpark notebooks to generate and transform data, which demonstrates the Python familiarity that BI roles increasingly expect.

You still get a full SQL analytics endpoint for querying. The Lakehouse gives you both Python and SQL from a single resource.

Your Fabric environment is ready. Next up, you will generate realistic sales data using a PySpark notebook directly inside this Lakehouse.

Generate Sample Data with a Notebook

Your workspace and lakehouse are ready to go. Now it's time to populate them with sales data that mirrors a real business scenario.

You'll use a PySpark notebook inside Fabric to generate this data programmatically. The notebook writes Delta tables directly into your lakehouse, giving you a star schema with products, customers, dates, and sales transactions.

In this step, get ready to:
  • Create a notebook linked to your lakehouse.
  • Generate three dimension tables for products, customers, and dates.
  • Generate a fact table with 200 sales transactions.
Create a new notebook
  • From inside your sales_lakehouse view, select Open notebook from the top menu bar.
  • Select New notebook.

A new notebook opens in your browser. It's automatically linked to your lakehouse, so any tables you create will land in sales_lakehouse.

What is a Fabric notebook?

A Fabric notebook is a browser-based coding environment that runs PySpark. You don't need to install Python or Spark locally.

The spark session is pre-initialized for you. Any tables you save with saveAsTable() are written as Delta tables directly into your linked lakehouse.

Don't see Open notebook in the menu?

Make sure you're viewing the lakehouse item itself, not the workspace list. Click sales_lakehouse in your workspace to open it, then look for Open notebook in the top toolbar.

If your browser blocked a pop-up, allow pop-ups for app.fabric.microsoft.com and try again.

Help me open a notebook in my Fabric lakehouse

Create the dimension tables

Dimension tables describe the "who, what, and when" of your data. You'll create three: one for products, one for customers, and one for dates.

  • Paste the following code into the first cell of your notebook:
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DoubleType, DateType
from datetime import date, timedelta

# --- dim_product ---
products_data = [
    (1, "Wireless Headphones", "Electronics", 79.99),
    (2, "USB-C Hub", "Electronics", 45.99),
    (3, "Mechanical Keyboard", "Electronics", 129.99),
    (4, "Ergonomic Chair", "Furniture", 399.99),
    (5, "Standing Desk", "Furniture", 549.99),
    (6, "Monitor Arm", "Furniture", 89.99),
    (7, "Notebook Set", "Office Supplies", 12.99),
    (8, "Whiteboard Markers", "Office Supplies", 8.99),
    (9, "Desk Lamp", "Office Supplies", 34.99),
    (10, "Webcam HD", "Electronics", 69.99),
]

product_schema = StructType([
    StructField("ProductID", IntegerType(), False),
    StructField("ProductName", StringType(), False),
    StructField("Category", StringType(), False),
    StructField("UnitPrice", DoubleType(), False),
])

df_products = spark.createDataFrame(products_data, schema=product_schema)
df_products.write.mode("overwrite").format("delta").saveAsTable("dim_product")

# --- dim_customer ---
customers_data = [
    (1, "Contoso Ltd", "West", "Enterprise"),
    (2, "Northwind Traders", "East", "Enterprise"),
    (3, "Adventure Works", "West", "Mid-Market"),
    (4, "Fabrikam Inc", "Central", "Enterprise"),
    (5, "Tailspin Toys", "East", "Small Business"),
    (6, "Wide World Importers", "Central", "Mid-Market"),
    (7, "Alpine Ski House", "West", "Small Business"),
    (8, "Bellows College", "East", "Enterprise"),
    (9, "Best For You Organics", "Central", "Small Business"),
    (10, "Coho Vineyard", "West", "Mid-Market"),
    (11, "Datum Corporation", "East", "Enterprise"),
    (12, "Liberty Delights", "Central", "Mid-Market"),
]

customer_schema = StructType([
    StructField("CustomerID", IntegerType(), False),
    StructField("CustomerName", StringType(), False),
    StructField("Region", StringType(), False),
    StructField("Segment", StringType(), False),
])

df_customers = spark.createDataFrame(customers_data, schema=customer_schema)
df_customers.write.mode("overwrite").format("delta").saveAsTable("dim_customer")

# --- dim_date ---
start = date(2024, 1, 1)
end = date(2024, 12, 31)
dates_data = []
current = start
while current <= end:
    quarter = f"Q{(current.month - 1) // 3 + 1}"
    day_type = "Weekend" if current.weekday() >= 5 else "Weekday"
    dates_data.append((
        current,
        current.year,
        current.month,
        current.strftime("%B"),
        quarter,
        day_type
    ))
    current += timedelta(days=1)

date_schema = StructType([
    StructField("OrderDate", DateType(), False),
    StructField("Year", IntegerType(), False),
    StructField("MonthNumber", IntegerType(), False),
    StructField("MonthName", StringType(), False),
    StructField("Quarter", StringType(), False),
    StructField("DayType", StringType(), False),
])

df_dates = spark.createDataFrame(dates_data, schema=date_schema)
df_dates.write.mode("overwrite").format("delta").saveAsTable("dim_date")

print("Dimension tables created!")
print(f"  dim_product: {df_products.count()} rows")
print(f"  dim_customer: {df_customers.count()} rows")
print(f"  dim_date: {df_dates.count()} rows")

What does this code do?

This cell creates three dimension tables that describe the context around each sale:

  • dim_product holds 10 products across 3 categories (Electronics, Furniture, Office Supplies) with their unit prices.
  • dim_customer holds 12 fictional companies across 3 regions (West, East, Central) and 3 segments (Enterprise, Mid-Market, Small Business).
  • dim_date generates every date in 2024 (366 rows) with year, month, quarter, and weekday/weekend labels.

Each DataFrame is saved as a Delta table using saveAsTable(). This writes the data directly into your lakehouse's Tables section.

  • Click the Run button (play icon) on the left side of the cell.
  • Wait for the cell to finish executing. You should see this output beneath the cell:
Dimension tables created!
  dim_product: 10 rows
  dim_customer: 12 rows
  dim_date: 366 rows

Cell taking a long time?

The first cell in a new notebook takes 30-60 seconds to run because Fabric needs to start the Spark session. Subsequent cells run much faster.

If you see an error about spark not being defined, make sure your notebook is linked to your lakehouse. Check the lakehouse icon in the left panel of the notebook.

Help me debug my Fabric notebook cell

Create the fact table

The fact table records what actually happened. Each row is one sales transaction, linking back to your dimension tables through ID columns.

  • Click the + Code button below Cell 1 to add a new cell.
  • Paste the following code into Cell 2:
import random
from pyspark.sql.types import StructType, StructField, IntegerType, DoubleType, DateType
from datetime import date, timedelta

random.seed(42)

products_with_prices = [
    (1, 79.99), (2, 45.99), (3, 129.99), (4, 399.99), (5, 549.99),
    (6, 89.99), (7, 12.99), (8, 8.99), (9, 34.99), (10, 69.99),
]

start_date = date(2024, 1, 1)
sales_data = []

for order_id in range(1, 201):
    days_offset = random.randint(0, 364)
    order_date = start_date + timedelta(days=days_offset)
    product_id, unit_price = random.choice(products_with_prices)
    customer_id = random.randint(1, 12)
    quantity = random.randint(1, 8)
    total_amount = round(quantity * unit_price, 2)
    sales_data.append((order_id, order_date, product_id, customer_id, quantity, unit_price, total_amount))

sales_schema = StructType([
    StructField("OrderID", IntegerType(), False),
    StructField("OrderDate", DateType(), False),
    StructField("ProductID", IntegerType(), False),
    StructField("CustomerID", IntegerType(), False),
    StructField("Quantity", IntegerType(), False),
    StructField("UnitPrice", DoubleType(), False),
    StructField("TotalAmount", DoubleType(), False),
])

df_sales = spark.createDataFrame(sales_data, schema=sales_schema)
df_sales.write.mode("overwrite").format("delta").saveAsTable("fact_sales")

print(f"fact_sales created: {df_sales.count()} rows")
print(f"Date range: {start_date} to {start_date + timedelta(days=364)}")
print("All tables ready for analysis!")

What does this code do?

This cell generates 200 randomized sales transactions spanning all of 2024.

  • random.seed(42) ensures reproducible results. Everyone running this code gets the same 200 orders.
  • Each transaction picks a random product, customer, date, and quantity, then calculates the total amount.
  • The ProductID and CustomerID columns are foreign keys that link back to your dimension tables. This is what makes it a star schema.
  • Click the Run button on Cell 2.
  • Confirm the output shows:
fact_sales created: 200 rows
Date range: 2024-01-01 to 2024-12-31
All tables ready for analysis!
  • Navigate back to your lakehouse view by clicking sales_lakehouse in the left panel (under your lakehouse icon).
  • Click the Refresh icon in the Tables section.
  • You should see all 4 tables listed: dim_customer, dim_date, dim_product, and fact_sales.

Tables not appearing after refresh?

Make sure both cells completed without errors. A green checkmark appears next to each cell when it finishes successfully.

If you still don't see tables after clicking refresh, try closing and reopening the lakehouse from your workspace. Select Workspaces in the left navigation, open Sales Analytics Project, then click sales_lakehouse.

Help me find my tables in the Fabric lakehouse

✔️ Awesome, I've got everything!

Great. Make sure you can see all 4 tables in your lakehouse before moving on.

ⓧ I'd like to double check the full code

Here is the complete code for Cell 1 (dimension tables):

from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DoubleType, DateType
from datetime import date, timedelta

# --- dim_product ---
products_data = [
    (1, "Wireless Headphones", "Electronics", 79.99),
    (2, "USB-C Hub", "Electronics", 45.99),
    (3, "Mechanical Keyboard", "Electronics", 129.99),
    (4, "Ergonomic Chair", "Furniture", 399.99),
    (5, "Standing Desk", "Furniture", 549.99),
    (6, "Monitor Arm", "Furniture", 89.99),
    (7, "Notebook Set", "Office Supplies", 12.99),
    (8, "Whiteboard Markers", "Office Supplies", 8.99),
    (9, "Desk Lamp", "Office Supplies", 34.99),
    (10, "Webcam HD", "Electronics", 69.99),
]

product_schema = StructType([
    StructField("ProductID", IntegerType(), False),
    StructField("ProductName", StringType(), False),
    StructField("Category", StringType(), False),
    StructField("UnitPrice", DoubleType(), False),
])

df_products = spark.createDataFrame(products_data, schema=product_schema)
df_products.write.mode("overwrite").format("delta").saveAsTable("dim_product")

# --- dim_customer ---
customers_data = [
    (1, "Contoso Ltd", "West", "Enterprise"),
    (2, "Northwind Traders", "East", "Enterprise"),
    (3, "Adventure Works", "West", "Mid-Market"),
    (4, "Fabrikam Inc", "Central", "Enterprise"),
    (5, "Tailspin Toys", "East", "Small Business"),
    (6, "Wide World Importers", "Central", "Mid-Market"),
    (7, "Alpine Ski House", "West", "Small Business"),
    (8, "Bellows College", "East", "Enterprise"),
    (9, "Best For You Organics", "Central", "Small Business"),
    (10, "Coho Vineyard", "West", "Mid-Market"),
    (11, "Datum Corporation", "East", "Enterprise"),
    (12, "Liberty Delights", "Central", "Mid-Market"),
]

customer_schema = StructType([
    StructField("CustomerID", IntegerType(), False),
    StructField("CustomerName", StringType(), False),
    StructField("Region", StringType(), False),
    StructField("Segment", StringType(), False),
])

df_customers = spark.createDataFrame(customers_data, schema=customer_schema)
df_customers.write.mode("overwrite").format("delta").saveAsTable("dim_customer")

# --- dim_date ---
start = date(2024, 1, 1)
end = date(2024, 12, 31)
dates_data = []
current = start
while current <= end:
    quarter = f"Q{(current.month - 1) // 3 + 1}"
    day_type = "Weekend" if current.weekday() >= 5 else "Weekday"
    dates_data.append((
        current,
        current.year,
        current.month,
        current.strftime("%B"),
        quarter,
        day_type
    ))
    current += timedelta(days=1)

date_schema = StructType([
    StructField("OrderDate", DateType(), False),
    StructField("Year", IntegerType(), False),
    StructField("MonthNumber", IntegerType(), False),
    StructField("MonthName", StringType(), False),
    StructField("Quarter", StringType(), False),
    StructField("DayType", StringType(), False),
])

df_dates = spark.createDataFrame(dates_data, schema=date_schema)
df_dates.write.mode("overwrite").format("delta").saveAsTable("dim_date")

print("Dimension tables created!")
print(f"  dim_product: {df_products.count()} rows")
print(f"  dim_customer: {df_customers.count()} rows")
print(f"  dim_date: {df_dates.count()} rows")

And here is the complete code for Cell 2 (fact table):

import random
from pyspark.sql.types import StructType, StructField, IntegerType, DoubleType, DateType
from datetime import date, timedelta

random.seed(42)

products_with_prices = [
    (1, 79.99), (2, 45.99), (3, 129.99), (4, 399.99), (5, 549.99),
    (6, 89.99), (7, 12.99), (8, 8.99), (9, 34.99), (10, 69.99),
]

start_date = date(2024, 1, 1)
sales_data = []

for order_id in range(1, 201):
    days_offset = random.randint(0, 364)
    order_date = start_date + timedelta(days=days_offset)
    product_id, unit_price = random.choice(products_with_prices)
    customer_id = random.randint(1, 12)
    quantity = random.randint(1, 8)
    total_amount = round(quantity * unit_price, 2)
    sales_data.append((order_id, order_date, product_id, customer_id, quantity, unit_price, total_amount))

sales_schema = StructType([
    StructField("OrderID", IntegerType(), False),
    StructField("OrderDate", DateType(), False),
    StructField("ProductID", IntegerType(), False),
    StructField("CustomerID", IntegerType(), False),
    StructField("Quantity", IntegerType(), False),
    StructField("UnitPrice", DoubleType(), False),
    StructField("TotalAmount", DoubleType(), False),
])

df_sales = spark.createDataFrame(sales_data, schema=sales_schema)
df_sales.write.mode("overwrite").format("delta").saveAsTable("fact_sales")

print(f"fact_sales created: {df_sales.count()} rows")
print(f"Date range: {start_date} to {start_date + timedelta(days=364)}")
print("All tables ready for analysis!")

Your lakehouse now has a complete star schema with realistic sales data. Next up, you'll query this data using SQL to validate its structure and preview the insights your dashboard will show.

Explore and Validate Data with SQL

Your lakehouse now has 200 sales transactions spread across four tables. Before you build a data model on top of this, you need to confirm the data is correct and the relationships between tables actually work.

Microsoft Fabric gives every lakehouse a SQL analytics endpoint that lets you run T-SQL queries directly against your Delta tables. In this step, you will write JOIN queries that prove your star schema holds together and preview the insights your dashboard will eventually show.

In this step, get ready to:
  • Open the SQL analytics endpoint for your lakehouse.
  • Run validation queries that JOIN your fact and dimension tables.
  • Write an analytical query with a window function to preview monthly trends.
Open the SQL analytics endpoint

The SQL analytics endpoint is a read-only T-SQL interface that sits on top of your Delta tables. You cannot create or insert data through it, but you can run any SELECT query to explore your data.

  • In the lakehouse view, find the dropdown in the top-right area of the screen that says Lakehouse.
  • Click the dropdown and select SQL analytics endpoint.
  • Wait for the endpoint to load. You should see the same 4 tables listed in the left panel: dim_product, dim_customer, dim_date, and fact_sales.

What is the SQL analytics endpoint?

Every Fabric lakehouse automatically exposes a SQL analytics endpoint. It translates standard T-SQL queries into reads against your Delta files stored in OneLake.

This means you get the power of SQL without importing or copying data anywhere. The endpoint is read-only, so all data creation still happens through notebooks or pipelines.

Don't see your tables listed?

If the left panel shows no tables, click the refresh icon near the Tables section. It can take a moment for the SQL endpoint to sync with newly created Delta tables.

help me find my tables in the SQL analytics endpoint

Run validation queries to check your star schema

A star schema only works if the keys in your fact table successfully match keys in your dimension tables. These queries prove that every JOIN resolves correctly and your data tells a coherent story.

  • Click New SQL query in the toolbar at the top of the SQL analytics endpoint.
  • Paste this first query into the editor. It joins the fact table to the product dimension and calculates revenue by category:
-- Query 1: Revenue by product category
SELECT
    p.Category,
    COUNT(f.OrderID) AS TotalOrders,
    SUM(f.TotalAmount) AS TotalRevenue,
    ROUND(AVG(f.TotalAmount), 2) AS AvgOrderValue
FROM fact_sales f
INNER JOIN dim_product p ON f.ProductID = p.ProductID
GROUP BY p.Category
ORDER BY TotalRevenue DESC;

What does this query do?

  • INNER JOIN connects each sale to its product details using ProductID. If any IDs were orphaned, those rows would be excluded.
  • GROUP BY p.Category aggregates results into Electronics, Furniture, and Office Supplies.
  • The three metrics (TotalOrders, TotalRevenue, AvgOrderValue) give you a quick health check on each product category.
  • Click Run in the toolbar (or press Shift+Enter).
  • You should see 3 rows in the results grid, one for each category: Electronics, Furniture, and Office Supplies.

Now clear the query editor and paste the second validation query. This one joins the fact table to the customer dimension to show sales broken down by region and customer segment:

  • Select all text in the query editor and delete it.
  • Paste this query:
-- Query 2: Sales by region and segment
SELECT
    c.Region,
    c.Segment,
    COUNT(f.OrderID) AS Orders,
    SUM(f.TotalAmount) AS Revenue
FROM fact_sales f
INNER JOIN dim_customer c ON f.CustomerID = c.CustomerID
GROUP BY c.Region, c.Segment
ORDER BY c.Region, Revenue DESC;

What does this query do?

  • This joins fact_sales to dim_customer on CustomerID, testing the second relationship in your star schema.
  • GROUP BY c.Region, c.Segment breaks results into combinations like "West / Enterprise" and "East / Small Business".
  • If you see results for all 3 regions (West, East, Central) and all 3 segments (Enterprise, Mid-Market, Small Business), your customer dimension is healthy.
  • Click Run.
  • You should see up to 9 rows showing the region and segment combinations with their order counts and revenue totals.

Seeing zero rows or an error?

If the query returns 0 rows, double-check that you ran both notebook cells in Step 2 and that each cell printed its success message.

If you see a syntax error, make sure you copied the entire query including the final semicolon.

help me troubleshoot my SQL query in Fabric

Run an analytical query with a window function

Your dashboard will eventually show monthly revenue trends. This final query previews that insight and uses a window function to calculate a running total across the year.

  • Clear the query editor again.
  • Paste this query that joins the fact table to the date dimension:
-- Query 3: Monthly revenue with running total
SELECT
    d.MonthNumber,
    d.MonthName,
    SUM(f.TotalAmount) AS MonthlyRevenue,
    SUM(SUM(f.TotalAmount)) OVER (ORDER BY d.MonthNumber) AS RunningTotal
FROM fact_sales f
INNER JOIN dim_date d ON f.OrderDate = d.OrderDate
GROUP BY d.MonthNumber, d.MonthName
ORDER BY d.MonthNumber;

What does this query do?

  • This joins fact_sales to dim_date on OrderDate, testing the third and final relationship in your star schema.
  • SUM(SUM(f.TotalAmount)) OVER (ORDER BY d.MonthNumber) is a window function. It calculates a cumulative sum that grows month over month.
  • The result gives you 12 rows (January through December) showing how revenue accumulates across the year.
  • Click Run.
  • You should see 12 rows, one per month, with the RunningTotal column increasing in each row.

Why validate before modeling?

These three queries confirmed that all JOIN relationships work correctly. Every ProductID, CustomerID, and OrderDate in the fact table matched a row in its corresponding dimension table.

If you had orphaned keys (fact rows pointing to nonexistent dimension rows), your dashboard would silently exclude data. Catching that now saves hours of debugging later.

✔️ Awesome, I've got everything!

All three queries returned results with no errors. Your star schema is validated and ready for the next step.

ⓧ I'd like to double check the full code

Here are all three queries together. You can paste them one at a time (clear the editor between each run) or run them all at once if you highlight each query individually before clicking Run.

-- Query 1: Revenue by product category
SELECT
    p.Category,
    COUNT(f.OrderID) AS TotalOrders,
    SUM(f.TotalAmount) AS TotalRevenue,
    ROUND(AVG(f.TotalAmount), 2) AS AvgOrderValue
FROM fact_sales f
INNER JOIN dim_product p ON f.ProductID = p.ProductID
GROUP BY p.Category
ORDER BY TotalRevenue DESC;

-- Query 2: Sales by region and segment
SELECT
    c.Region,
    c.Segment,
    COUNT(f.OrderID) AS Orders,
    SUM(f.TotalAmount) AS Revenue
FROM fact_sales f
INNER JOIN dim_customer c ON f.CustomerID = c.CustomerID
GROUP BY c.Region, c.Segment
ORDER BY c.Region, Revenue DESC;

-- Query 3: Monthly revenue with running total
SELECT
    d.MonthNumber,
    d.MonthName,
    SUM(f.TotalAmount) AS MonthlyRevenue,
    SUM(SUM(f.TotalAmount)) OVER (ORDER BY d.MonthNumber) AS RunningTotal
FROM fact_sales f
INNER JOIN dim_date d ON f.OrderDate = d.OrderDate
GROUP BY d.MonthNumber, d.MonthName
ORDER BY d.MonthNumber;

Your data is validated and your star schema is solid. Next up, you will build a semantic model that defines these relationships formally and add DAX measures to calculate business metrics like Total Revenue and Revenue Growth.

Build the Semantic Model with DAX Measures

Your SQL queries confirmed the data is clean and the joins work correctly. Now it's time to build the layer that transforms raw tables into business intelligence.

A semantic model defines how your tables relate to each other and what calculations Power BI can perform on them. You'll connect your fact table to its dimension tables in a star schema, then add DAX measures that calculate business metrics like revenue and growth.

In this step, get ready to:
  • Create a semantic model from your lakehouse tables.
  • Define star schema relationships between fact and dimension tables.
  • Write DAX measures for revenue, orders, and month-over-month growth.
Create a new semantic model

A semantic model sits on top of your lakehouse tables and defines relationships, hierarchies, and calculations. In Microsoft Fabric, creating one from the SQL analytics endpoint automatically uses Direct Lake mode, which loads data directly from OneLake into memory without duplicating it.

  • From your SQL analytics endpoint view, select New semantic model in the toolbar ribbon at the top.
  • In the name field, type Sales Analytics Model.
  • Select all 4 tables: fact_sales, dim_product, dim_customer, and dim_date.
  • Select Confirm.

Fabric creates the semantic model and opens it. You should see all four tables listed in the model.

What is Direct Lake mode?

Direct Lake is Fabric's unique connection mode. Traditional Power BI models either import data (creating a copy) or use DirectQuery (querying the source live with latency). Direct Lake loads parquet files from OneLake directly into memory, giving you import-level speed without duplicating data.

Don't see the New semantic model button?

Make sure you are in the SQL analytics endpoint view, not the lakehouse view. The dropdown at the top-right of the screen should say SQL analytics endpoint. If it says Lakehouse, switch it using that dropdown.

help me find the New semantic model button in my Fabric lakehouse

Define star schema relationships

A star schema connects a central fact table to surrounding dimension tables. You need to tell the model which columns link each table together so Power BI can filter and aggregate data correctly.

  • Navigate back to your workspace by selecting Sales Analytics Project in the left navigation breadcrumb.
  • Find and select the Sales Analytics Model item (it has a semantic model icon).
  • Select Open data model to enter the web modeling experience.
  • In the top-right corner, find the mode dropdown. It defaults to Viewing. Switch it to Editing.

Pop-up blockers can interfere

The web modeling experience may open in a new tab. If nothing happens when you click Open data model, check your browser's pop-up blocker and allow pop-ups from app.fabric.microsoft.com.

You should now see a diagram with your four tables. Time to draw the relationships.

  • Drag ProductID from the fact_sales table onto ProductID in the dim_product table.
  • A relationship dialog appears. Confirm the cardinality is set to Many to one.
  • Check the Assume referential integrity checkbox.
  • Select Confirm (or OK) to save the relationship.
  • Repeat the same process: drag CustomerID from fact_sales onto CustomerID in dim_customer. Set Many to one, check Assume referential integrity, and confirm.
  • Drag OrderDate from fact_sales onto OrderDate in dim_date. Set Many to one, check Assume referential integrity, and confirm.

You should now see lines connecting fact_sales to all three dimension tables, forming a star pattern.

Why check Assume referential integrity?

This tells the engine that every key in the fact table has a matching row in the dimension table. Because you generated the data yourself and validated it with SQL in the previous step, you know this is true.

Enabling this setting allows Direct Lake to use more efficient INNER JOINs instead of OUTER JOINs, improving query performance.

Relationship creation isn't working?

Make sure the mode dropdown in the top-right says Editing, not Viewing. You cannot create relationships in viewing mode.

If dragging doesn't work, try double-clicking the space between two tables or using the Manage relationships option in the toolbar.

help me create relationships in my Fabric semantic model

Add DAX measures

DAX measures are calculations that run at query time. Instead of storing pre-computed values in your table, measures dynamically calculate results based on how the user filters and slices the data in a report.

  • In the model diagram (still in Editing mode), select the fact_sales table.
  • Select New measure from the toolbar.
  • A formula bar appears at the top. Enter the following DAX formula:
Total Revenue = SUM(fact_sales[TotalAmount])
  • Press Enter or select the checkmark to confirm the measure.
  • Select New measure again and enter this formula:
Total Orders = COUNTROWS(fact_sales)
  • Confirm, then select New measure one more time:
Average Order Value = DIVIDE([Total Revenue], [Total Orders])
  • Confirm the measure.

What do these measures do?

Total Revenue sums the TotalAmount column across whatever rows are currently filtered. Total Orders counts the number of rows (one row = one order). Average Order Value divides revenue by order count.

Using DIVIDE instead of the `/` operator is a DAX best practice. DIVIDE handles division-by-zero gracefully by returning BLANK instead of an error.

Now add the final, more advanced measure. This one calculates how much revenue changed compared to the previous month.

  • Select New measure and enter this formula:
Revenue Growth MoM =
VAR CurrentMonth = [Total Revenue]
VAR PreviousMonth =
    CALCULATE(
        [Total Revenue],
        DATEADD(dim_date[OrderDate], -1, MONTH)
    )
RETURN
    DIVIDE(CurrentMonth - PreviousMonth, PreviousMonth)
  • Confirm the measure.

What does Revenue Growth MoM do?

This measure uses VAR to store intermediate values. CurrentMonth captures the Total Revenue in the current filter context. PreviousMonth uses CALCULATE with DATEADD to shift the date filter back one month and recalculate revenue.

RETURN then divides the difference by the previous month's value, giving you a percentage growth rate. A result of 0.25 means 25% growth.

  • Save the model by pressing Cmd+S (macOS) or Ctrl+S (Windows).

You should see all four measures listed under the fact_sales table in the model view.

Measure formula showing an error?

Check that you typed the table and column names exactly as they appear in your model: fact_sales[TotalAmount] and dim_date[OrderDate]. DAX is case-insensitive but the column names must match.

For Revenue Growth MoM, make sure you have the closing parenthesis after MONTH and that RETURN is on its own line.

help me fix my DAX measure formula error in Fabric

✔️ I see all four measures under fact_sales

Your semantic model is complete. The star schema relationships and DAX measures are ready to power your dashboard.

ⓧ I can't find where my measures went

Measures attach to whichever table was selected when you clicked New measure. If you accidentally created a measure on a dimension table, right-click it and select Move to table to move it to fact_sales.

If you don't see any measures at all, make sure you confirmed each one by pressing Enter or clicking the checkmark after typing the formula.

Your semantic model now has a clean star schema and four business metrics ready to go. Next up, you'll bring these numbers to life in an interactive Power BI dashboard.

Create an Interactive Sales Dashboard

Your semantic model has the relationships and measures that describe how your sales data connects. Now it is time to turn those numbers into something a stakeholder can actually interact with.

In this step, you will build an interactive Power BI dashboard directly in the browser. The dashboard uses your DAX measures to show KPI cards, revenue breakdowns by category, monthly trends, and slicers that let users filter the data themselves.

In this step, get ready to:
  • Create a new Power BI report from your semantic model.
  • Add KPI cards, a bar chart, and a line chart to visualize key metrics.
  • Add slicers for Region and Category to enable interactive filtering.
Create a new report from your semantic model

Power BI reports in Microsoft Fabric pull their data directly from a semantic model. Because your model already has relationships and measures defined, the report canvas knows exactly how to aggregate and filter data without any extra configuration.

  • Navigate to your workspace by selecting Workspaces in the left navigation, then selecting Sales Analytics Project.
  • Find Sales Analytics Model in the list of workspace items.
  • Select the model to open it, then select New report in the ribbon at the top.
  • The Power BI report canvas opens in your browser with the Visualizations pane on the right and the Data pane listing your tables and measures.

What is Direct Lake mode?

Because you created this report from a Fabric Lakehouse-backed semantic model, Power BI uses Direct Lake mode. This loads data directly from OneLake into memory without importing or duplicating it.

You get import-like performance with no data copies to manage. This is what makes Fabric's BI integration unique compared to traditional Import or DirectQuery modes.

Report canvas not opening?

If nothing happens when you select New report, your browser's pop-up blocker may be interfering. Allow pop-ups for app.fabric.microsoft.com and try again.

Also confirm that third-party cookies are enabled for app.powerbi.com in your browser settings. Power BI requires this to render the canvas.

Help me troubleshoot why the Power BI report canvas isn't loading in my browser.

Build the KPI cards and charts

A dashboard needs headline metrics that executives can scan in seconds. KPI cards display single numbers prominently, making them the first visuals to add.

  • In the Visualizations pane on the right, select the Card visual (the icon that looks like a number tile).
  • In the Data pane, expand the fact_sales table and drag Total Revenue into the card's Fields well.
  • Resize the card and position it in the top-left area of the canvas.
  • Click an empty area of the canvas to deselect the first card.
  • Add a second Card visual and drag Total Orders into its Fields well. Position it next to the first card.
  • Add a third Card visual and drag Average Order Value into its Fields well. Position it next to the second card.

You should now see three cards across the top of your canvas, each displaying a single metric value.

  • Click an empty area of the canvas to deselect.
  • In the Visualizations pane, select Clustered bar chart.
  • From the Data pane, expand dim_product and drag Category to the Y-axis well.
  • From fact_sales, drag Total Revenue to the X-axis well.
  • Resize the chart and position it below the KPI cards on the left half of the canvas.

The bar chart now shows revenue broken down by Electronics, Furniture, and Office Supplies.

  • Click an empty area of the canvas to deselect.
  • In the Visualizations pane, select Line chart.
  • From fact_sales, drag OrderDate to the X-axis well.
  • Power BI may automatically expand OrderDate into a date hierarchy. Click the down-arrow on the X-axis field and select Month to show monthly granularity.
  • Drag Total Revenue to the Y-axis well.
  • Resize and position the line chart on the right half of the canvas, below the KPI cards.

Your line chart now displays monthly revenue trends across 2024.

Why these three visual types?

Cards give instant headline numbers. The bar chart compares categories side by side. The line chart reveals trends over time.

Together, they answer the three core questions any sales stakeholder asks: How much total? Where is it coming from? Is it growing?

Add slicers and test interactivity

Slicers let users filter the entire dashboard by selecting values from a dropdown or list. Adding Region and Category slicers transforms your static visuals into a self-service analytics tool.

  • Click an empty area of the canvas to deselect any visual.
  • In the Visualizations pane, select Slicer.
  • From the Data pane, expand dim_customer and drag Region into the slicer's Field well.
  • Position the slicer along the top or side of the canvas where it does not overlap your charts.
  • Click an empty area to deselect, then add a second Slicer visual.
  • From dim_product, drag Category into the slicer's Field well. Position it next to the Region slicer.

Now test that cross-filtering works across all your visuals.

  • In the Region slicer, select West. Watch the KPI cards, bar chart, and line chart update to show only West region data.
  • Click a bar in the clustered bar chart (for example, Electronics). The line chart and cards filter to show only that category.
  • Clear your slicer selections by clicking the eraser icon on the Region slicer to reset the dashboard to its full view.

What is cross-filtering?

Cross-filtering means clicking on one visual automatically filters the others on the same page. Power BI enables this by default because your semantic model already defines how tables relate to each other through the star schema relationships you set up.

  • Save your report by selecting File in the top menu, then Save.
  • Name the report Sales Performance Dashboard and select Save.

Your report is now saved to the Sales Analytics Project workspace.

Visuals showing blank or no data?

Make sure you are dragging fields from the correct tables. The Total Revenue measure lives under fact_sales, not under a dimension table.

If your bar chart shows blank, confirm that Category came from dim_product (not from a different table). The relationship between fact_sales and dim_product is what allows the measure to calculate per category.

Help me figure out why my Power BI visuals are showing blank data even though my tables have rows.

Secret mission

Add Time Intelligence and Drill-Through

Your dashboard shows current performance, but stakeholders always ask the same question: how does this compare to last year? In this stretch challenge, you will add Year-over-Year DAX measures and a drill-through page that lets users right-click any customer to see their full purchase history.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on the Microsoft Fabric 60-day trial, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Microsoft Fabric Trial capacity (free, expires in 60 days).
  • 1 Workspace (Sales Analytics Project).
  • 1 Lakehouse (sales_lakehouse).
  • 1 Notebook (PySpark data generation).
  • 1 Semantic Model (Sales Analytics Model).
  • 1 Power BI Report (Sales Performance Dashboard).

Keep everything running

No action needed. Choose this if you are still actively building or want to refine your dashboard for portfolio screenshots.

  • Your trial runs for 60 days with no charges.
  • You can continue adding data sources, building new reports, or experimenting with DAX measures.
  • All your data stays in OneLake and remains accessible from your workspace.

Pause - I'll come back to this later

There is nothing to pause. Fabric trial capacity is always-on until it expires.

  • Your data stays in OneLake and your workspace remains intact for the full 60-day trial period.
  • You can return to app.fabric.microsoft.com at any time during the trial to pick up where you left off.
  • After trial expiration, non-Power BI items become inactive for 7 days. If you do not upgrade, they are then deleted.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes everything in the workspace permanently.

  • Open your workspace in Microsoft Fabric.
  • Select Workspace settings from the top menu.
  • Scroll to the bottom and select Remove this workspace.
  • This removes the lakehouse, notebook, semantic model, and Power BI report all at once.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete end-to-end Business Intelligence solution inside Microsoft Fabric, entirely in the browser.

What you learned:

  • Generated realistic sales data with a PySpark notebook and stored it as Delta tables in a Fabric Lakehouse.
  • Built a star schema semantic model with Direct Lake mode and wrote DAX measures for Total Revenue, Total Orders, Average Order Value, and Revenue Growth.
  • Created an interactive Power BI dashboard with KPI cards, charts, and cross-filtering slicers published directly in the Fabric portal.
  • Added time intelligence DAX measures and a drill-through page for customer-level detail.

Ready to quiz yourself?