Power BI Star Schema: Define the Grain Before Relationships

A star schema separates events you measure from the descriptive attributes you use to group them. In Power BI, it can make relationships and measures easier to reason about. The first design decision is not which tables to draw; it is what exactly one row in the fact table represents.

Write a one-sentence grain statement

For a small shop model, define FactSales as one row per order line. An order with three different products therefore creates three fact rows. Quantity and line amount belong at that grain. A whole-order shipping charge cannot be repeated on each line and summed without an allocation rule.

FactSales
OrderLineKey | DateKey  | ProductKey | Quantity | LineAmount
1001         | 20260901 | 10         | 2        | 400
1002         | 20260901 | 20         | 1        | 150

DimProduct
ProductKey | ProductName | Category
10         | Notebook    | Stationery
20         | Pen set     | Stationery

Make dimension keys unique

DimProduct should have one row per ProductKey for this simple model. If the same key appears twice, investigate whether your source has duplicates or historical versions. A relationship does not repair that ambiguity. For a historical dimension, use a design that identifies the correct version for each fact row.

Use a predictable filter path

Create a one-to-many relationship from the dimension key to the fact key. Single-direction filtering from dimensions to facts is a useful starting point. Do not switch every relationship to bidirectional merely to make a visual change: that can introduce ambiguity and unexpected interactions elsewhere.

Validate totals with a tiny fixture

Before importing a large dataset, verify that the sample quantity totals 3 and line amount totals 550. Filter to product 10 and expect quantity 2 and amount 400. Add an unmatched ProductKey to learn how your model presents missing dimension members, then correct the underlying data contract.

Separate descriptive and measured fields

Use dimensions for product name, category and calendar attributes. Use explicit measures for important calculations so the intended aggregation is visible. Hide technical keys from report consumers when they are not useful for analysis, but keep them available for model diagnostics.

Practice and check your understanding

Add a customer dimension and one customer key to each fact row. Write the grain of every table and list the uniqueness checks you would run before refreshing the model. Test a duplicate dimension key as a deliberate failure case.

Continue learning

Explore the related Code Hacker course syllabus for structured learning. Then read Power BI Measures vs Calculated Columns: A Sales Example to connect this exercise to the next skill.

Reference

Microsoft star schema guidance provides the underlying API or technical guidance. Check the documentation for the version you use; examples here are small learning exercises rather than complete production implementations.