Power BI Exercise - Building a Simple Star Schema (Retail Dataset)

By infotech.razzi.my

Power BI Exercise - Building a Simple Star Schema (Retail Dataset)

Transform the flat retail dataset into a clear, reusable semantic model.

In this guided exercise, you will improve the retail semantic model by separating descriptive fields into Product, Region, and Channel dimension tables. You will then create one-to-many relationships, hide duplicate fact-table fields, and confirm that all sales measures still return the correct values.

Learning goal: understand the different jobs performed by fact and dimension tables and build a clear star-shaped model around the Sales table.
Star schemaFact tableDimensionsRelationships

What is a star schema?

A star schema separates tables according to their purpose:

  • Fact tables store business events and numeric values to summarize. In this model, Sales is the fact table.
  • Dimension tables describe business entities and provide fields for filtering and grouping. Examples include Date, Product, Region, and Channel.
Retail star schema The Date, Product, Region, and Channel dimensions each have a one-to-many, single-direction relationship to the central Sales fact table. Date Date Year · Month · Day DimProduct Product Category 15 unique products Sales FACT TABLE SaleID · SaleDate Quantity · UnitPrice SalesAmount DimRegion Region 4 sales regions DimChannel Channel Online · Store 1* 1* 1* 1*

1 = unique dimension value   ·   * = multiple Sales rows   ·   arrows show single-direction filtering

Each dimension sits on the one side of a relationship. Sales sits on the many side because a product, region, channel, or date can appear in many transactions.

Before you begin

Complete the previous Date-table exercise first. Your model should contain:

  • A Sales table containing 15 transactions
  • A marked Date table related to Sales[SaleDate]
  • The measures [Total Sales], [Total Quantity], and [Transaction Count]
Control totals: Total Sales = 21,788, Total Quantity = 42, and Transaction Count = 15.

Step 1: Create the Product dimension

  1. On the Modeling ribbon, select New table.
  2. Enter the following DAX expression.
DimProduct =
SUMMARIZE (
    Sales,
    Sales[Product],
    Sales[Category]
)

SUMMARIZE creates one row for each unique Product and Category combination. The sample dataset should produce 15 product rows.

Why include Category? Category describes the product. Keeping Product and Category together creates one reusable product dimension and avoids a separate one-column Category table for this small model.

Step 2: Verify that Product is unique

  1. Open Data view.
  2. Select DimProduct.
  3. Confirm that every product appears once.
  4. Confirm the table contains 15 rows.

The Product column must be unique because it will form the one side of the relationship.

Step 3: Create the Region dimension

Create another calculated table:

DimRegion =
DISTINCT ( Sales[Region] )

The expected four rows are Central, East, North, and South.

Step 4: Create the Channel dimension

DimChannel =
DISTINCT ( Sales[Channel] )

The expected two rows are Online and Store.

Step 5: Create the relationships

Open Model view and create the following active relationships:

One sideMany sideCardinalityFilter direction
DimProduct[Product]Sales[Product]One to many (1:*)Single
DimRegion[Region]Sales[Region]One to many (1:*)Single
DimChannel[Channel]Sales[Channel]One to many (1:*)Single

Your existing Date relationship should remain:

Date[Date]  1 ---- *  Sales[SaleDate]
Use single-direction filtering: filters should flow from each dimension into Sales. Do not enable Both unless a specific model requirement has been tested and justified.

Step 6: Organize the model diagram

  1. Place the Sales table in the centre.
  2. Arrange Date, DimProduct, DimRegion, and DimChannel around Sales.
  3. Confirm each relationship line displays 1 beside the dimension and * beside Sales.
  4. Confirm every relationship is shown as a solid line, indicating that it is active.

Step 7: Hide duplicate descriptive columns

Report authors should use dimension fields for slicing and grouping. In Model view, right-click and choose Hide in report view for:

  • Sales[Product]
  • Sales[Category]
  • Sales[Region]
  • Sales[Channel]

Do not delete these columns. They are still required as relationship keys in this learning model.

Result: report authors see one clear version of Product, Category, Region, and Channel. Numeric transaction fields and measures remain associated with Sales.

Step 8: Rebuild the slicers with dimension fields

  1. Replace the existing Product or Category slicer with DimProduct[Product] or DimProduct[Category].
  2. Replace the Region slicer with DimRegion[Region].
  3. Replace the Channel slicer with DimChannel[Channel].
  4. Keep using columns from the Date table for date filtering.

Your existing measures do not need to be rewritten. Dimension selections travel through the relationships and filter the Sales rows evaluated by each measure.

Step 9: Validate category results

Open DAX Query View and run:

EVALUATE
SUMMARIZECOLUMNS (
    DimProduct[Category],
    "Total Sales", [Total Sales],
    "Total Quantity", [Total Quantity],
    "Transactions", [Transaction Count]
)
ORDER BY DimProduct[Category]
CategoryTotal SalesTotal QuantityTransactions
Computing5,959114
Entertainment5,89373
Home4,77194
Mobile5,165154

Step 10: Validate region and channel filters

Region results

RegionExpected Total Sales
Central9,281
East3,078
North2,734
South6,695

Channel results

ChannelExpected Total Sales
Online6,545
Store15,243

Step 11: Test combined filtering

  1. Select Central in the Region slicer.
  2. Select Computing in the Category slicer.
  3. Confirm Total Sales is 4,997.
  4. Keep Central selected and change Category to Home.
  5. Confirm Total Sales is 2,185.

Multiple dimension filters combine using AND logic. A Sales row must satisfy every active selection to contribute to the result.

Model design note

This guided exercise uses DAX calculated tables to make the star-schema transformation easy to reproduce. In a production model, dimension tables are commonly prepared in a data warehouse or Power Query so data quality, keys, refresh behavior, and large data volumes can be managed more deliberately.

Knowledge check

  1. Which table is the fact table, and what is its row-level grain?
  2. Why must a dimension key contain unique values?
  3. Why should report slicers use dimension fields?
  4. What does the 1:* symbol mean?
  5. How does selecting Central and Computing affect the Sales table?

Troubleshooting

ProblemWhat to check
Power BI creates a many-to-many relationship.Check for duplicate or blank values in the intended dimension key.
A slicer does not change Total Sales.Confirm the slicer uses a dimension column and that its relationship to Sales is active.
Category totals are incorrect.Confirm each product maps to one category and that DimProduct contains 15 unique products.
Duplicate field names confuse report authors.Hide the descriptive fields in Sales and use dimension fields in visuals.
A relationship line is dashed.Edit the relationship and make it active, provided this does not create an ambiguous path.

Exercise complete

You have converted the flat retail table into a simple star schema. Product, Region, Channel, and Date now provide consistent filtering and grouping, while Sales remains responsible for transaction-level values and measures.
Completion checklist
  • DimProduct contains 15 unique products and their categories.
  • DimRegion contains four regions.
  • DimChannel contains two channels.
  • All four dimensions have active one-to-many relationships to Sales.
  • Slicers use dimension fields.
  • All control totals match the expected results.

References