Power BI Exercise - Building a Simple Star Schema (Retail Dataset)
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.
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,
Salesis the fact table. - Dimension tables describe business entities and provide fields for filtering and grouping. Examples include Date, Product, Region, and Channel.
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
Salestable containing 15 transactions - A marked
Datetable related toSales[SaleDate] - The measures
[Total Sales],[Total Quantity], and[Transaction Count]
Step 1: Create the Product dimension
- On the Modeling ribbon, select New table.
- 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.
Step 2: Verify that Product is unique
- Open Data view.
- Select DimProduct.
- Confirm that every product appears once.
- 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 side | Many side | Cardinality | Filter 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]Step 6: Organize the model diagram
- Place the Sales table in the centre.
- Arrange Date, DimProduct, DimRegion, and DimChannel around Sales.
- Confirm each relationship line displays 1 beside the dimension and * beside Sales.
- 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.
Step 8: Rebuild the slicers with dimension fields
- Replace the existing Product or Category slicer with
DimProduct[Product]orDimProduct[Category]. - Replace the Region slicer with
DimRegion[Region]. - Replace the Channel slicer with
DimChannel[Channel]. - 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]| Category | Total Sales | Total Quantity | Transactions |
|---|---|---|---|
| Computing | 5,959 | 11 | 4 |
| Entertainment | 5,893 | 7 | 3 |
| Home | 4,771 | 9 | 4 |
| Mobile | 5,165 | 15 | 4 |
Step 10: Validate region and channel filters
Region results
| Region | Expected Total Sales |
|---|---|
| Central | 9,281 |
| East | 3,078 |
| North | 2,734 |
| South | 6,695 |
Channel results
| Channel | Expected Total Sales |
|---|---|
| Online | 6,545 |
| Store | 15,243 |
Step 11: Test combined filtering
- Select Central in the Region slicer.
- Select Computing in the Category slicer.
- Confirm Total Sales is 4,997.
- Keep Central selected and change Category to Home.
- 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
Knowledge check
- Which table is the fact table, and what is its row-level grain?
- Why must a dimension key contain unique values?
- Why should report slicers use dimension fields?
- What does the 1:* symbol mean?
- How does selecting Central and Computing affect the Sales table?
Troubleshooting
| Problem | What 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
- 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.