Star Schema Sales Model in Fabric

Build a star schema sales analytics model in Microsoft Fabric.

Introduction

30 Second Summary

Every online store generates thousands of transactions daily. The difference between a thriving business and a struggling one often comes down to how well they can analyze that data to spot trends and make decisions.

In this project, you will transform raw sales data into a professional analytics dashboard using Microsoft Fabric. You'll design a star schema, write DAX measures for business KPIs, query data with SQL, and build an interactive report entirely in your browser.

What You'll Build

A complete sales analytics solution that transforms flat CSV files into a star schema data model with interactive Power BI visualizations, all built in the browser on your Mac.

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

  • A Lakehouse in Microsoft Fabric storing raw CSV data as Delta tables, with a Dataflow Gen2 that transforms flat data into a proper star schema using Power Query.
  • A semantic model with star schema relationships and DAX measures (total revenue, average order value, order count), queryable through the SQL analytics endpoint.
  • An interactive Power BI report with cards, charts, and slicers that visualize sales KPIs across multiple dimensions.
  • Secret Mission: Use Copilot to generate advanced DAX measures with natural language prompts.

Are there any prerequisites?

You should be comfortable writing basic SQL queries and have a general understanding of data tables and relationships. No prior experience with Microsoft Fabric or Power BI is required.

Set Up Microsoft Fabric

Microsoft Fabric is a cloud-based analytics platform that runs entirely in your browser. Since Power BI Desktop is not available on macOS, you will use the browser-based Fabric experience for the entire project.

To start building, you need three things: an Azure account, a user identity in Microsoft Entra ID, and an active Microsoft Fabric trial. In this step, you will create all three and set up a workspace to hold your analytics project.

In this step, get ready to:
  • Create an Azure free account and a Microsoft Entra ID user.
  • Activate the Microsoft Fabric 60-day free trial.
  • Create a workspace called SalesAnalytics.
Create an Azure free account and Microsoft Entra ID user

Microsoft Fabric requires a work-style identity to sign in. Since you are using a personal email, you will create a free Azure account first and then add a user inside Microsoft Entra ID. That user acts as your work-style login for the Fabric portal.

Will I be charged?

You may be asked for phone verification and a payment method. Microsoft uses this for identity verification only. You will not be charged unless you explicitly upgrade to a paid subscription.

Once registration is complete, you are in the Azure portal. Now you will create a user identity that Fabric can accept as a sign-in.

  • In the Azure portal search bar at the top, type Microsoft Entra ID and select it from the results.
  • From the left menu, expand Manage and select Users.
  • Select New user, then select Create new user.
  • In the User principal name field, enter a username (for example, your first name).
  • In the Display name field, enter your full name.
  • Uncheck Auto-generate password and set a strong password you will remember.
  • Select Review + create, then select Create.
  • After the user is created, return to the Users list and copy the User principal name (UPN) that was generated. It will look like yourname@yourdomain.onmicrosoft.com.

What is a User Principal Name?

A User Principal Name (UPN) is the username format that Microsoft services use for sign-in. It looks like an email address but belongs to your Azure directory. You will use this UPN to sign in to Microsoft Fabric in the next substep.

Save your UPN and password somewhere safe

You will need the UPN and the password you just set to sign in to Fabric. Write them down or paste them into a temporary note.

Don't see the Create new user option?

Make sure you are signed in with the same account you used to create the Azure free account. That account is automatically a Global Administrator for the new directory, which gives you permission to create users.

If you only see an Invite option instead of Create new user, confirm you selected New user first and then Create new user (not Invite external user).

help me troubleshoot why I can't create a new user in Microsoft Entra ID

Activate the Fabric free trial

Now that you have a user identity, you can sign in to Microsoft Fabric and activate the 60-day free trial. The trial gives you a full-featured capacity at no cost.

What browsers are supported?

Microsoft Fabric supports Safari 16.4 or later, Chrome (latest), Edge 120 or later, and Firefox (latest, newer than v93). Make sure third-party cookies are enabled for app.powerbi.com in your browser settings.

  • Open a new browser tab and go to the Fabric portal:
  • https://app.fabric.microsoft.com
  • Sign in using the UPN and password you created in the previous substep.
  • If prompted to set up two-factor authentication, follow the on-screen instructions. The Microsoft Authenticator app is recommended.
  • Complete any remaining authentication prompts until you reach the Fabric home page.

You are now signed in to the Fabric portal. Next, activate the trial capacity.

  • Select your profile icon in the upper-right corner of the page.
  • Select Free trial.
  • Choose a capacity region from the dropdown. Pick the region closest to you for best performance.
  • Select Activate.

You should see a confirmation message that your 60-day free Fabric trial capacity is now active.

Don't see the Free trial option?

Make sure you signed in with the Entra ID UPN (ending in @yourdomain.onmicrosoft.com), not your personal email. Fabric trials require a work-style identity.

If you see Trial status instead of Free trial, you may already have a trial in progress. Select it to check your remaining days.

help me troubleshoot why I cannot activate my Microsoft Fabric free trial

Create a workspace

A workspace is a shared container in Fabric where all of your analytics items live. You will create one workspace to hold the Lakehouse, dataflow, semantic model, and report for this project.

  • In the Fabric portal left navigation, select Workspaces.
  • Select + New workspace.
  • In the Name field, enter SalesAnalytics.
  • Ensure the workspace is assigned to your Trial capacity. This should be selected by default since the trial is your only capacity.
  • Select Apply.

Your new SalesAnalytics workspace opens automatically. You can also find it any time from the Workspaces menu in the left navigation.

Why a dedicated workspace?

Workspaces in Fabric group related items together and control who has access. By creating a dedicated workspace for this project, you keep your Lakehouse, dataflows, models, and reports organized in one place.

Can't assign to Trial capacity?

If the Trial capacity option does not appear, go back to your profile icon, select Free trial, and confirm the trial is active. Then try creating the workspace again.

help me troubleshoot why my workspace cannot be assigned to Trial capacity in Microsoft Fabric

Your Fabric environment is ready. Next up, you will create a Lakehouse and load the raw sales data that will become your star schema.

Create a Lakehouse and Load Raw Data

Your workspace is ready. Now you need somewhere to store the actual data your analytics model will be built on.

A Lakehouse in Microsoft Fabric combines a data lake (for raw files) with a structured table layer (for queryable data). In this step, you will create a Lakehouse, upload sample sales CSV files into it, and convert those CSVs into Delta tables that can be queried with SQL.

In this step, get ready to:
  • Create a Lakehouse in your SalesAnalytics workspace.
  • Create and upload four CSV data files.
  • Load each CSV as a Delta table.
Create the Lakehouse

A Lakehouse gives you two storage areas: a Files section for raw uploads and a Tables section for structured, queryable Delta tables.

  • In your SalesAnalytics workspace, select + New item.
  • Search for or select Lakehouse.
  • Enter SalesLakehouse as the name.
  • Select Create.

The Lakehouse opens in the Lakehouse Explorer. You should see an empty Files section and an empty Tables section in the left panel.

What is a Lakehouse?

A Lakehouse in Microsoft Fabric stores all your data in OneLake. The Files area is a landing zone for raw data in any format (CSV, JSON, Parquet). The Tables area holds structured Delta tables that SQL and Spark can query directly.

Everything you put in either area is accessible to other Fabric workloads without copying the data.

Don't see the Lakehouse option?

Make sure you are inside your SalesAnalytics workspace (not the default "My workspace"). The + New item button should appear at the top of the workspace view.

If you still cannot find it, confirm your trial capacity is active by selecting your profile icon and checking your trial status.

Why can't I find the Lakehouse option in my workspace?

Create and upload CSV files

Your analytics model needs source data. You will create four CSV files on your Mac that represent a simplified e-commerce dataset, then upload them into the Lakehouse.

The fastest way to create CSV files on a Mac is with TextEdit in plain text mode. For each file below, follow these steps:

  • Press Cmd+Space to open Spotlight, type TextEdit, and press Enter.
  • Select Format in the menu bar, then select Make Plain Text.
  • Copy the file content from below and paste it into the document.
  • Press Cmd+S to save. Name the file exactly as shown (e.g. raw_sales.csv) and save it to your Desktop.
  • If macOS asks whether to use the .csv extension, select Use .csv.

Already have a code editor?

If you have VS Code, Sublime Text, or another plain text editor installed, use that instead. The key requirement is that each file is saved as plain text with a .csv extension.

Create raw_sales.csv with this content:

OrderID,OrderDate,ProductName,Category,CustomerName,City,Country,Segment,Quantity,UnitPrice
1001,2024-01-15,Wireless Mouse,Electronics,Alice Johnson,New York,USA,Consumer,2,29.99
1002,2024-01-18,Standing Desk,Furniture,Bob Smith,London,UK,Corporate,1,499.99
1003,2024-02-02,USB-C Hub,Electronics,Carol White,Sydney,Australia,Consumer,3,45.00
1004,2024-02-14,Ergonomic Chair,Furniture,David Brown,Toronto,Canada,Corporate,1,350.00
1005,2024-03-01,Wireless Mouse,Electronics,Eve Davis,New York,USA,Consumer,5,29.99
1006,2024-03-10,Notebook Set,Stationery,Frank Wilson,Berlin,Germany,Home Office,10,12.50
1007,2024-04-05,Standing Desk,Furniture,Alice Johnson,New York,USA,Consumer,1,499.99
1008,2024-04-22,Webcam HD,Electronics,Grace Lee,Tokyo,Japan,Corporate,2,89.99
1009,2024-05-08,Ergonomic Chair,Furniture,Henry Taylor,Paris,France,Home Office,2,350.00
1010,2024-05-15,USB-C Hub,Electronics,Bob Smith,London,UK,Corporate,1,45.00
1011,2024-06-01,Wireless Mouse,Electronics,Carol White,Sydney,Australia,Consumer,4,29.99
1012,2024-06-20,Notebook Set,Stationery,David Brown,Toronto,Canada,Corporate,20,12.50
1013,2024-07-04,Standing Desk,Furniture,Eve Davis,New York,USA,Consumer,1,499.99
1014,2024-07-19,Webcam HD,Electronics,Frank Wilson,Berlin,Germany,Home Office,1,89.99
1015,2024-08-02,Ergonomic Chair,Furniture,Grace Lee,Tokyo,Japan,Corporate,1,350.00
1016,2024-08-25,USB-C Hub,Electronics,Henry Taylor,Paris,France,Home Office,2,45.00
1017,2024-09-10,Wireless Mouse,Electronics,Alice Johnson,New York,USA,Consumer,3,29.99
1018,2024-09-28,Notebook Set,Stationery,Bob Smith,London,UK,Corporate,15,12.50
1019,2024-10-15,Standing Desk,Furniture,Carol White,Sydney,Australia,Consumer,1,499.99
1020,2024-10-30,Webcam HD,Electronics,David Brown,Toronto,Canada,Corporate,2,89.99
1021,2024-11-12,Ergonomic Chair,Furniture,Eve Davis,New York,USA,Consumer,1,350.00
1022,2024-11-28,USB-C Hub,Electronics,Frank Wilson,Berlin,Germany,Home Office,4,45.00
1023,2024-12-05,Wireless Mouse,Electronics,Grace Lee,Tokyo,Japan,Corporate,2,29.99
1024,2024-12-20,Standing Desk,Furniture,Henry Taylor,Paris,France,Home Office,1,499.99
1025,2025-01-08,Wireless Mouse,Electronics,Alice Johnson,New York,USA,Consumer,6,29.99
1026,2025-01-22,Ergonomic Chair,Furniture,Bob Smith,London,UK,Corporate,2,350.00
1027,2025-02-10,USB-C Hub,Electronics,Carol White,Sydney,Australia,Consumer,3,45.00
1028,2025-02-28,Notebook Set,Stationery,David Brown,Toronto,Canada,Corporate,25,12.50
1029,2025-03-15,Standing Desk,Furniture,Eve Davis,New York,USA,Consumer,2,499.99
1030,2025-03-30,Webcam HD,Electronics,Frank Wilson,Berlin,Germany,Home Office,3,89.99

Create dim_product.csv with this content:

ProductID,ProductName,Category
P001,Wireless Mouse,Electronics
P002,Standing Desk,Furniture
P003,USB-C Hub,Electronics
P004,Ergonomic Chair,Furniture
P005,Notebook Set,Stationery
P006,Webcam HD,Electronics

Create dim_customer.csv with this content:

CustomerID,CustomerName,City,Country,Segment
C001,Alice Johnson,New York,USA,Consumer
C002,Bob Smith,London,UK,Corporate
C003,Carol White,Sydney,Australia,Consumer
C004,David Brown,Toronto,Canada,Corporate
C005,Eve Davis,New York,USA,Consumer
C006,Frank Wilson,Berlin,Germany,Home Office
C007,Grace Lee,Tokyo,Japan,Corporate
C008,Henry Taylor,Paris,France,Home Office

Create dim_date.csv with this content:

DateKey,FullDate,Year,Quarter,MonthNumber,MonthName
20240115,2024-01-15,2024,Q1,1,January
20240118,2024-01-18,2024,Q1,1,January
20240202,2024-02-02,2024,Q1,2,February
20240214,2024-02-14,2024,Q1,2,February
20240301,2024-03-01,2024,Q1,3,March
20240310,2024-03-10,2024,Q1,3,March
20240405,2024-04-05,2024,Q2,4,April
20240422,2024-04-22,2024,Q2,4,April
20240508,2024-05-08,2024,Q2,5,May
20240515,2024-05-15,2024,Q2,5,May
20240601,2024-06-01,2024,Q2,6,June
20240620,2024-06-20,2024,Q2,6,June
20240704,2024-07-04,2024,Q3,7,July
20240719,2024-07-19,2024,Q3,7,July
20240802,2024-08-02,2024,Q3,8,August
20240825,2024-08-25,2024,Q3,8,August
20240910,2024-09-10,2024,Q3,9,September
20240928,2024-09-28,2024,Q3,9,September
20241015,2024-10-15,2024,Q4,10,October
20241030,2024-10-30,2024,Q4,10,October
20241112,2024-11-12,2024,Q4,11,November
20241128,2024-11-28,2024,Q4,11,November
20241205,2024-12-05,2024,Q4,12,December
20241220,2024-12-20,2024,Q4,12,December
20250108,2025-01-08,2025,Q1,1,January
20250122,2025-01-22,2025,Q1,1,January
20250210,2025-02-10,2025,Q1,2,February
20250228,2025-02-28,2025,Q1,2,February
20250315,2025-03-15,2025,Q1,3,March
20250330,2025-03-30,2025,Q1,3,March

What are these four files?

These files represent a simplified star schema dataset for e-commerce sales.

  • raw_sales.csv is the flat transaction data with 30 orders spanning January 2024 to March 2025.
  • dim_product.csv lists six unique products with their categories.
  • dim_customer.csv lists eight customers with their location and segment.
  • dim_date.csv maps each order date to a year, quarter, and month for time-based analysis.

Now upload the files to your Lakehouse.

  • Return to the SalesLakehouse in the Fabric portal.
  • In the Lakehouse Explorer, hover over Files, select the ellipsis (...), and choose New subfolder.
  • Name the subfolder RawData and press Enter.
  • Select the ellipsis (...) next to the RawData folder, then choose Upload > Upload files.
  • In the upload pane, browse to your Desktop and select all four CSV files (raw_sales.csv, dim_product.csv, dim_customer.csv, dim_date.csv).
  • Select Upload, then close the upload pane.

Select the RawData folder in the Explorer to confirm all four files appear.

Files not appearing after upload?

Try refreshing the page in your browser. If the files still do not appear, verify you uploaded into the RawData subfolder (not directly into Files). Also confirm each file was saved as plain text with a .csv extension (not .csv.txt or .rtf).

Help me troubleshoot my CSV file upload to the Lakehouse.

Load files to Delta tables

Raw CSV files in the Files area cannot be queried with SQL yet. You need to load them as Delta tables so they become structured, queryable objects.

  • In the Lakehouse Explorer, expand the RawData folder under Files.
  • Right-click (or use the ellipsis) on raw_sales.csv, then select Load to tables > New table.
  • Review the suggested table name. Ensure it is raw_sales (lowercase, no spaces).
  • Select Load.
  • Repeat for dim_product.csv (table name: dim_product).
  • Repeat for dim_customer.csv (table name: dim_customer).
  • Repeat for dim_date.csv (table name: dim_date).

What are Delta tables?

Delta tables store data in an open format that supports SQL queries, schema enforcement, and time travel (versioning). When you load a CSV as a Delta table, Fabric converts the raw file into this optimized format so it can be queried through the SQL analytics endpoint and used in semantic models.

  • Once all four tables have loaded, expand the Tables section in the Lakehouse Explorer.
  • If the tables do not appear, select the ellipsis (...) next to Tables and select Refresh.
  • Verify that all four tables appear: raw_sales, dim_product, dim_customer, dim_date.
  • Select any table to preview its data and confirm the columns look correct.

Tables not showing after loading?

Loading can take a minute per file. Wait for the loading notification to complete before refreshing. If a table appears with an unexpected name (e.g. raw_sales_csv), you can rename it by selecting the ellipsis on the table and choosing Rename.

Help me fix my Delta table loading issue.

Your data foundation is in place. Next up, you will use Power Query to transform this raw data into a proper star schema fact table.

Transform Data with Power Query

Your Lakehouse now holds four Delta tables loaded from raw CSV files. The dimension tables (dim_product, dim_customer, dim_date) are already shaped correctly for a star schema.

The raw_sales table still contains every column from the original flat file. A proper fact table should hold only numeric measures and foreign keys that link to dimensions. In this step, you will use Dataflow Gen2 to transform raw_sales into a lean fact_sales table with calculated columns.

In this step, get ready to:
  • Create a Dataflow Gen2 that connects to your Lakehouse data.
  • Apply Power Query transformations to build a fact table with calculated columns.
  • Verify the new fact_sales Delta table appears in your Lakehouse.
Create a Dataflow Gen2 to build the fact table

Power Query is a visual data transformation engine. In Microsoft Fabric, Dataflow Gen2 gives you the full Power Query Online editor so you can shape data before it lands in your Lakehouse as a Delta table.

  • Return to the SalesLakehouse in the Fabric portal.
  • In the Lakehouse ribbon, select Get data, then select New Dataflow Gen2.
  • Name the dataflow SalesFactTransform and select Create.

The Power Query Online editor opens with a blank canvas.

  • In the Power Query editor, select Get data from the ribbon, then select Lakehouse.
  • Sign in with your organizational account and select Next.
  • In the Choose data dialog, navigate to your SalesLakehouse, select the raw_sales table, then select Create.

The Power Query editor now shows a preview of your raw_sales data with all ten original columns.

What is Dataflow Gen2?

Dataflow Gen2 is Microsoft Fabric's low-code data transformation tool. It uses the same Power Query interface found in Power BI Desktop and Excel, but runs entirely in the browser.

You define transformations visually, and the output lands directly in your Lakehouse as a Delta table. This gives you genuine Power Query experience without needing a Windows machine.

Apply transformations to create the fact table

A fact table stores measurable events (quantities, amounts) and integer keys that reference dimension tables. You need to calculate a TotalAmount column, generate a DateKey in YYYYMMDD integer format, and strip out the descriptive columns that belong in dimensions.

  • In the Power Query ribbon, select Add Column, then select Custom Column.
  • In the New column name field, type TotalAmount.
  • In the Custom column formula field, enter the following formula:
[Quantity] * [UnitPrice]
  • Select OK to add the TotalAmount column.

What does this formula do?

The formula [Quantity] * [UnitPrice] multiplies the quantity sold by the unit price for each row. Square brackets reference column names in Power Query M language.

This gives you the total revenue for each line item, which is the core measure your analytics dashboard will aggregate later.

  • Select Add Column again, then select Custom Column.
  • In the New column name field, type DateKey.
  • In the Custom column formula field, enter:
Number.FromText(Date.ToText([OrderDate], "yyyyMMdd"))
  • Select OK to add the DateKey column.

What does the DateKey formula do?

This formula converts each order date into an integer like 20240115. It first formats the date as text in yyyyMMdd format using Date.ToText, then converts that text to a number with Number.FromText.

This integer key matches the DateKey column in your dim_date table, allowing you to join fact and dimension tables later.

Now remove the columns that belong in dimension tables, not the fact table.

  • In the data preview, hold Cmd (macOS) and click the column headers for ProductName, Category, CustomerName, City, Country, and Segment to select all six columns.
  • On the Home tab in the ribbon, select Remove columns.

Why remove these columns?

In a star schema, descriptive attributes like product names and customer details live in dimension tables. The fact table stores only numeric measures (Quantity, UnitPrice, TotalAmount) and keys (OrderID, DateKey) that point to those dimensions.

This separation keeps the fact table compact and queries fast.

  • Also select and remove the OrderDate column (since DateKey now replaces it as the date reference).
  • In the Query settings pane on the right, find the Name field.
  • Change the query name from raw_sales to fact_sales.
  • In the Query settings pane, verify that Data destination shows your Lakehouse. If it does not, select it and configure the output to SalesLakehouse.
  • Select Save and Run from the toolbar.

The dataflow processes your transformations and writes the result as a new Delta table. This may take a minute or two.

Seeing an error when running the dataflow?

Make sure your custom column formulas use square brackets around column names exactly as shown ([Quantity], [UnitPrice], [OrderDate]). Power Query is case-sensitive for column references.

If the data destination is not set, select the query name in the Queries pane, then look for the Data destination option in Query settings and configure it to point to your Lakehouse.

Help me troubleshoot my Dataflow Gen2 run error.

Verify the fact table
  • Return to the SalesLakehouse in the Lakehouse Explorer.
  • Under Tables, select the ellipsis (...) next to the dbo schema and select Refresh.
  • Verify the fact_sales table appears in the Tables section.
  • Select fact_sales to preview its data. You should see five columns: OrderID, DateKey, Quantity, UnitPrice, and TotalAmount.

Don't see the fact_sales table?

Make sure you selected Refresh on the Tables section. New tables created by dataflows do not appear automatically until you refresh the explorer.

If the table still does not appear, return to your workspace and check whether the SalesFactTransform dataflow shows a successful run status. A failed run means the transformations encountered an error.

Help me find my fact_sales table in the Lakehouse.

Your star schema is taking shape. You have a lean fact table with calculated measures and integer keys ready to join with your dimensions. Next up, you will query this data model with SQL to validate the relationships.

Query Data with SQL

Your star schema is built. You have a fact table and three dimension tables sitting in your Lakehouse as Delta tables, ready to answer business questions.

Now you will query these tables using SQL to validate that the star schema works correctly. The SQL analytics endpoint gives you a read-only T-SQL editor directly in the browser, so you can write familiar SQL against your Delta tables without any additional setup.

In this step, get ready to:
  • Open the SQL analytics endpoint for your Lakehouse.
  • Write SQL queries that join fact and dimension tables.
  • Validate that your star schema returns correct aggregated results.
Open the SQL analytics endpoint

The SQL analytics endpoint is automatically generated for every Lakehouse. It provides T-SQL access to your Delta tables without requiring a separate data warehouse.

  • In the Lakehouse Explorer for your SalesLakehouse, locate the dropdown in the top-right area of the ribbon.
  • Select SQL analytics endpoint from the dropdown.

The SQL editor opens, showing your Delta tables as queryable objects under the dbo schema.

What is the SQL analytics endpoint?

Fabric automatically creates this endpoint when you create a Lakehouse. It gives you a read-only T-SQL surface over your Delta tables.

You can write standard SQL queries here without spinning up a Spark cluster or configuring a separate warehouse. It is designed for exploration, reporting, and ad-hoc analysis.

Write analytical SQL queries

You will write three queries that test the star schema from different angles. Each query joins the fact table to a dimension table, proving that your data model supports real business questions.

  • Select New SQL Query from the ribbon to open a blank query tab.
  • Write your first query to calculate total revenue and order count by pasting this into the editor:
-- Total revenue and order count
SELECT 
    COUNT(DISTINCT OrderID) AS TotalOrders, 
    SUM(TotalAmount) AS TotalRevenue 
FROM dbo.fact_sales;

What does this query do?

  • COUNT(DISTINCT OrderID) counts the number of unique orders in your fact table.
  • SUM(TotalAmount) adds up all revenue across every transaction.
  • This query hits only the fact table. No joins needed because you are asking a question about raw totals.
  • Click the Run button (or press F5) to execute the query.
  • You should see a single row with TotalOrders showing 30 and TotalRevenue showing the sum of all your sales data.

Query returning zero rows?

  • Make sure you are in the SQL analytics endpoint view, not the Lakehouse Explorer. Check the dropdown in the top-right area of the ribbon.
  • Verify the table name matches exactly. It should be dbo.fact_sales (lowercase, with underscore).
  • If the table does not appear, return to the Lakehouse Explorer, select the ellipsis next to the dbo schema, and choose Refresh.

Why isn't my SQL query returning results in the Fabric SQL analytics endpoint?

  • Next, write a query that joins the fact table to the product dimension to see revenue by category. Paste this below your first query:
-- Revenue by product category
SELECT 
    p.Category, 
    SUM(f.TotalAmount) AS CategoryRevenue 
FROM dbo.fact_sales f 
JOIN dbo.dim_product p ON f.ProductName = p.ProductName 
GROUP BY p.Category 
ORDER BY CategoryRevenue DESC;

What does this query do?

  • JOIN dbo.dim_product p ON f.ProductName = p.ProductName connects the fact table to the product dimension using the product name as the key.
  • GROUP BY p.Category rolls up revenue to the category level (Electronics, Furniture, Stationery).
  • This is the power of a star schema. You slice fact data by dimension attributes without storing category names in the fact table itself.
  • Highlight just this second query and click Run to execute it.
  • You should see three rows: Electronics, Furniture, and Stationery, each with their total revenue, sorted from highest to lowest.
  • Finally, write a query that joins the fact table to the date dimension to show monthly revenue trends. Paste this below your second query:
-- Monthly revenue trend
SELECT 
    d.Year, 
    d.MonthName, 
    SUM(f.TotalAmount) AS MonthlyRevenue 
FROM dbo.fact_sales f 
JOIN dbo.dim_date d ON f.DateKey = d.DateKey 
GROUP BY d.Year, d.MonthName, d.MonthNumber 
ORDER BY d.Year, d.MonthNumber;

What does this query do?

  • JOIN dbo.dim_date d ON f.DateKey = d.DateKey connects transactions to the date dimension using the integer date key you created in the Dataflow.
  • GROUP BY d.Year, d.MonthName, d.MonthNumber groups revenue by month. MonthNumber is included for correct sort order even though it is not displayed.
  • The results show how revenue flows across months, which is exactly the kind of trend a business stakeholder asks about.
  • Highlight this third query and click Run to execute it.
  • You should see rows for each month in your data, with the year, month name, and aggregated revenue for that month.

Join returning fewer rows than expected?

  • Check that DateKey values in fact_sales match the DateKey values in dim_date. Both should be YYYYMMDD integers (e.g. 20240115).
  • If the Dataflow formatted DateKey differently than the CSV, the join will silently drop rows. Preview both tables to compare formats.

Help me debug why my SQL JOIN is returning fewer rows than expected in Fabric.

✔️ Awesome, I've got everything!

Great. Make sure all three queries are saved in your query tab for reference.

ⓧ I'd like to double check the full code

Here are all three queries together. Your SQL query tab should contain these:

-- Total revenue and order count
SELECT 
    COUNT(DISTINCT OrderID) AS TotalOrders, 
    SUM(TotalAmount) AS TotalRevenue 
FROM dbo.fact_sales;

-- Revenue by product category
SELECT 
    p.Category, 
    SUM(f.TotalAmount) AS CategoryRevenue 
FROM dbo.fact_sales f 
JOIN dbo.dim_product p ON f.ProductName = p.ProductName 
GROUP BY p.Category 
ORDER BY CategoryRevenue DESC;

-- Monthly revenue trend
SELECT 
    d.Year, 
    d.MonthName, 
    SUM(f.TotalAmount) AS MonthlyRevenue 
FROM dbo.fact_sales f 
JOIN dbo.dim_date d ON f.DateKey = d.DateKey 
GROUP BY d.Year, d.MonthName, d.MonthNumber 
ORDER BY d.Year, d.MonthNumber;
Validate your results

Running queries is only half the job. You also need to confirm that the results make sense and that dimension attributes correctly slice the fact data.

  • Review the category revenue results. Confirm that every category in dim_product (Electronics, Furniture, Stationery) appears in the output.
  • Review the monthly revenue results. Confirm that each month in your dataset has a corresponding row with a non-null revenue value.
  • Cross-check the total revenue from query 1 against the sum of all category revenues from query 2. They should match, confirming that no data was lost in the join.

Why cross-check totals?

If the sum of category revenues does not match the total revenue, it means some rows in fact_sales did not match a row in dim_product. This is a data integrity issue that you want to catch now, before building reports on top of the model.

Your star schema is validated. The fact table joins cleanly to each dimension, and SQL can slice your data by category, time period, and more. Next up, you will build a semantic model with DAX measures and create an interactive report.

Build a Semantic Model and Report

Your SQL queries confirmed the star schema works. Now it is time to turn those tables into something interactive.

In this step you will create a Power BI semantic model on top of your Delta tables, define relationships between fact and dimension tables, write DAX measures to calculate business KPIs, and build a live report with charts and a slicer.

In this step, get ready to:
  • Create a Power BI semantic model from your Lakehouse tables.
  • Define star schema relationships and write DAX measures.
  • Build an interactive report with cards, charts, and a slicer.
Create a semantic model

A semantic model is the layer that sits between your raw data and your report visuals. It defines which tables are available, how they relate to each other, and what calculations exist.

  • Return to your SalesLakehouse in the Fabric portal. Select Workspaces in the left navigation, open the SalesAnalytics workspace, and select SalesLakehouse.
  • In the Lakehouse ribbon at the top, select New Power BI semantic model.
  • In the dialog that appears, name it SalesModel.
  • Select the four star schema tables: fact_sales, dim_product, dim_customer, and dim_date.
  • Make sure raw_sales is NOT selected. You only want the star schema tables in your model.
  • Select Confirm.

What is Direct Lake mode?

When you create a semantic model from a Lakehouse, it uses Direct Lake mode by default. This loads data directly from OneLake Delta tables into memory for fast analysis without importing or duplicating data.

You get the speed of in-memory analysis with the freshness of reading directly from your Lakehouse tables.

The semantic model opens in the web modeling experience. You should see your four tables displayed as boxes in a diagram view.

Don't see the model editor?

If you land on a details page instead of the diagram, select Open from the toolbar. Then switch from Viewing to Editing mode using the dropdown in the top-right corner.

Help me switch to editing mode in the model editor

Define relationships and write DAX measures

Relationships tell Power BI how to join your tables when building visuals. Without them, your charts cannot combine data from the fact table with dimension attributes like category or month name.

  • In the model diagram, make sure you are in Editing mode (check the dropdown in the top-right corner).
  • From the fact_sales table, select and drag the ProductName field to the ProductName field in the dim_product table.
  • The New relationship dialog appears. Verify these settings:
  • Cardinality: Many-to-one.
  • Cross filter direction: Single.
  • Check the box for Assume referential integrity.
  • Select Save to create the relationship.
  • Create the second relationship: drag CustomerName from fact_sales to CustomerName in dim_customer.
  • Use the same settings (Many-to-one, Single, Assume referential integrity).
  • Select Save.
  • Create the third relationship: drag DateKey from fact_sales to DateKey in dim_date.
  • Use the same settings and select Save.

You should now see lines connecting fact_sales to each of the three dimension tables in the diagram.

Why Many-to-one with Single cross-filter?

Each row in the fact table maps to exactly one row in each dimension table (many orders can share one product). The Single cross-filter direction means filters flow from dimension to fact.

When you select a category in a slicer, it filters the fact rows. This is the standard star schema pattern.

Now you will create DAX measures to calculate business KPIs directly in the model.

  • In the Data pane on the right side, select the fact_sales table.
  • Select New measure from the ribbon at the top.
  • In the formula bar that appears, type the first measure and press Enter:
Total Revenue = SUM(fact_sales[TotalAmount])
  • Select New measure again and enter the second measure:
Total Orders = DISTINCTCOUNT(fact_sales[OrderID])
  • Select New measure one more time and enter the third measure:
Average Order Value = DIVIDE([Total Revenue], [Total Orders], 0)

What do these DAX measures do?

  • Total Revenue sums every value in the TotalAmount column. It recalculates dynamically based on whatever filters are active.
  • Total Orders counts the distinct order IDs, giving you the number of unique orders.
  • Average Order Value divides revenue by order count. The DIVIDE function handles division-by-zero safely by returning 0 instead of an error.

You should see your three measures listed under the fact_sales table in the Data pane, each with a small calculator icon.

Don't see the formula bar?

Make sure you are in Editing mode. The formula bar only appears when editing is active. Also confirm you have the fact_sales table selected in the Data pane before clicking New measure.

Help me find the New measure button

Build the report

With the semantic model ready, you can create visuals that bring your data to life. The report canvas works entirely in your browser.

  • In the semantic model editor ribbon, select New report. A new browser tab opens to the report editing canvas.
  • In the Visualizations pane on the right, select the Card visual icon to add a card placeholder to the canvas.
  • From the Data pane, expand fact_sales and drag Total Revenue into the card's Fields well.
  • The card now displays your total revenue number.
  • Click an empty area of the canvas to deselect the card.
  • In the Visualizations pane, select the Clustered bar chart icon to add a bar chart placeholder.
  • Drag dim_product[Category] to the Y-axis well.
  • Drag Total Revenue to the X-axis well.

You should see horizontal bars comparing revenue across Electronics, Furniture, and Stationery.

  • Click an empty area of the canvas to deselect.
  • In the Visualizations pane, select the Line chart icon.
  • Drag dim_date[MonthName] to the X-axis well.
  • Drag Total Revenue to the Y-axis well.
  • To sort months in calendar order, select the ellipsis (...) on the line chart visual.
  • Choose Sort axis, then select MonthNumber.

You should see a line trending across months in calendar order.

  • Click an empty area of the canvas to deselect.
  • In the Visualizations pane, select the Slicer visual icon.
  • Drag dim_customer[Segment] into the slicer's Field well.

You should see the three segments (Consumer, Corporate, Home Office) appear as filter options.

  • Test the slicer by selecting Consumer. All visuals on the page should update to show only Consumer segment data.
  • Click the selected segment again to clear the filter and restore all data.

How does the slicer filter all visuals?

By default, slicers filter all other visuals on the same report page. When you select a segment, Power BI applies that filter through the relationships you defined.

The DAX measures recalculate dynamically to reflect only the filtered rows.

  • Save your report by selecting File then Save.
  • Name it Sales Analytics Dashboard and ensure it saves to the SalesAnalytics workspace.
  • Select Save.

Your report should display a KPI card, a clustered bar chart comparing categories, a line chart showing monthly revenue trends, and a segment slicer that filters everything dynamically.

Visuals not filtering when you click the slicer?

Make sure all your relationships are active (you should see solid lines, not dashed lines, in the model diagram). If a relationship is inactive, open the semantic model, right-click the relationship line, and check that Make this relationship active is selected.

Also confirm your slicer uses a field from a dimension table (dim_customer[Segment]), not from the fact table directly.

Help me debug why my slicer is not filtering

Secret mission

Use Copilot to Generate DAX with Natural Language

Your basic measures work, but real analyst interviews test time intelligence patterns like year-over-year growth and running totals. In this challenge, you will write natural language prompts that describe the business logic you need, then create advanced DAX measures that go beyond simple aggregations.

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 on Microsoft Fabric's free 60-day trial, so no resources incur charges during the trial period.

Resources you used:

  • Azure free account (no ongoing charges unless you upgrade).
  • Microsoft Entra ID user (free).
  • Microsoft Fabric trial capacity (expires after 60 days).
  • SalesAnalytics workspace containing SalesLakehouse, SalesFactTransform dataflow, SalesModel semantic model, and Sales Analytics Dashboard report.

Keep everything running

No action needed. Choose this if you are still actively experimenting with Microsoft Fabric.

  • Your trial lasts 60 days from the day you activated it.
  • Continue exploring Fabric features at no cost during the trial window.
  • Your Azure free account remains open with no charges unless you explicitly upgrade.

Pause - I'll come back to this later

No action needed. The trial capacity runs for 60 days regardless of activity. There is nothing to pause.

  • Your workspace, Lakehouse, dataflow, semantic model, and report remain available when you return.
  • No charges accumulate while the trial is active.
  • If you do not return before the trial expires, non-Power BI Fabric items may become unusable after 7 days.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below.

  • In the Fabric portal, open your SalesAnalytics workspace.
  • Select the ellipsis (...) on each item (SalesLakehouse, SalesFactTransform, SalesModel, Sales Analytics Dashboard) and choose Delete.
  • To cancel the Fabric trial, select your profile icon in the upper-right corner to open your Account manager, then select Cancel trial.
  • To delete the Microsoft Entra ID user, go to the Azure portal, search for and select Microsoft Entra ID, then select Users. Find the user you created, select it, and choose Delete user.
  • Your Azure free account incurs no charges and can remain open. If you wish to close it, visit the Azure portal's Cost Management + Billing section to manage your subscription.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete sales analytics solution inside Microsoft Fabric, from raw CSV files all the way to an interactive dashboard.

You've learned how to:

  • Build a star schema data model with fact and dimension tables in a Fabric Lakehouse, transforming raw data with Power Query (Dataflow Gen2).
  • Create a semantic model with star schema relationships and DAX measures, then validate it with SQL queries against the SQL analytics endpoint.
  • Build an interactive Power BI report with KPI cards, charts, and slicers, entirely in the browser on a Mac.
  • Secret Mission: Used prompt engineering (or manual DAX) to create advanced time intelligence measures including YoY Growth %, Running Total Revenue, and Revenue % of Total.

Ready to quiz yourself?