Power BI star schema: fact tables, dimensions, relationships

The idea

A Power BI model is not one big table. It is a handful of small tables joined by relationships, and the shape that works best has a name: the star schema.

In the middle sits one fact table. A fact table has one row for each thing that happened: an order line, a click, a payment. Its columns are mostly numbers you add up (line_total, quantity) and ids that say who, what and when. Around it sit dimension tables. A dimension has one row for each product, customer or day, and its columns are the words you group and filter by: category, state, Year.

Draw it and it looks like a star: the fact in the centre, a dimension on each point.

Figure 1

One flat table against a model of small tables

One flat table: every order line with everything about it
order_item_id	first_name	state	product	line_total
1	Jasmine	MA	Bramble Ceramic Floor Lamp	287.91
2	Jasmine	MA	Vale Enamel Bowl Set	35.97
3	Paul	AZ	Vale Enamel Bowl Set	47.96
4	Paul	AZ	Chester Brass Wall Mirror	149.00

order_items (fact)
order_item_id	order_id	product_id	line_total
1	1001	121	287.91
2	1001	154	35.97
3	1002	154	47.96
4	1002	194	149.00

orders
order_id	customer_id
1001	125
1002	156

customers (dimension)
customer_id	first_name	state
125	Jasmine	MA
156	Paul	AZ

Step 1 of 4 A flat table: one row per order line, with the customer's details copied onto every line.

A flat table: one row per order line, with the customer's details copied onto every line.
Jasmine is written on two rows and Paul on two. Counting the rows says four customers; there are two. Fix a state and you must fix it on every line.
Split it. order_items keeps only the numbers and the ids. Each customer is written once, in customers, and orders says who bought what.
Now COUNTROWS(customers) is 2, the state lives in one place, and SUM(order_items[line_total]) still adds up to 520.84.

Why it exists

You could keep everything in one flat table, the way a spreadsheet export looks. For a small file that works. Then the problems stack up.

Repeated text. Jasmine's name and state are copied onto every line she ever bought. Fix her state and you must fix it on every row, or the report shows two Jasmines.
Wrong counts. Count the rows of a flat table and you count lines, not customers. Every distinct count turns into a workaround.
Size. Power BI compresses columns, but a million lines with a copied product name is still a million copies. Small tables are faster to scan and to refresh.
One place to slice. A single Year column on the dimension serves every measure. In a flat table each fact would need its own date column.

The star keeps each fact written once. That is also why one measure like SUM(order_items[line_total]) can answer "by category", "by state" and "by month" without changing a character.

How it works under the hood

Keys

Tables find each other through keys. A primary key is a column whose values are unique: customers[customer_id] has exactly one row per customer. A foreign key is a column in another table that repeats those values to point at a row: orders[customer_id]. Power BI does not need you to declare them. You pick the two columns when you draw the relationship, and it checks that the 1 side really is unique.

One-to-many relationships

A relationship joins a key to a foreign key. One customer has many orders, one order has many lines, one product appears on many lines, one day holds many orders. Each of those is one-to-many: the 1 side is the dimension, the many (*) side is the table that points at it.

You draw them in Model view (the third icon on Power BI Desktop's left rail): drag a column onto another, or use Home → Manage relationships → New. The dialog asks for the two columns, the cardinality and the cross-filter direction.

Figure 2

The Harbor & Pine star: one fact table, descriptions around it

'Date'

Date
Year
Month

orders

order_id
customer_id
order_date
channel

customers

customer_id
segment
state

products

product_id
category
unit_price

order_items

order_item_id
order_id
product_id
line_total

Step 1 of 4 order_items is the fact table: one row per order line, with the numbers (line_total) and the ids that point elsewhere. It is the longest table by far.

order_items is the fact table: one row per order line, with the numbers (line_total) and the ids that point elsewhere. It is the longest table by far.
products, customers and 'Date' are dimensions: one row per product, customer or day, full of the words you slice by.
Each line has a 1 at one end and a * at the other. The 1 side is the table that holds the key once; the * side holds many rows pointing at it.
orders is a small header table between the lines and the customer and the calendar. The shape is still a star: every description is a short hop from the numbers.

Filter direction

A relationship is not just a line. It is a pipe that carries filters, and in a normal star it carries them one way: from the 1 side to the many side. Pick MA on a state slicer and the filter reaches customers, then orders, then order_items. The sum then runs over the MA lines only.

Figure 3

A slicer on state: the filter runs one way

'Date'

Date
Year
Month

orders

order_id
customer_id
order_date
channel

customers

customer_id
segment
state

state = "MA"

products

product_id
category
unit_price

order_items

order_item_id
order_id
product_id
line_total

Step 1 of 3 A slicer picks state = "MA". The filter lands on customers, the table that owns the column.

A slicer picks state = "MA". The filter lands on customers, the table that owns the column.
It flows from the 1 side to the * side: first to orders, then to order_items. Only Massachusetts lines are left to add up.
It does not flow back up. products is untouched, so a category slicer still lists every category, even ones MA never bought.

That one-way flow is a feature. It means a measure always reads the fact table through a predictable path, and a slicer on a dimension never has two ways to reach the numbers. The dialog also offers Both directions. Leave it alone until you can say what path each filter takes: two paths between the same tables make the model ambiguous, and Power BI refuses or picks one for you.

What goes where

When you meet a new column, ask one question: is it something I add up, or something I group by? Add up, and it belongs to a fact (line_total). Group by, and it belongs to a dimension (category, state, Month). A column that is both a label and a number, like unit_price, is usually a dimension attribute: it describes the product, even if a fact stores the price actually charged.

Why the calendar is a table of its own

orders[order_date] already holds a date, so why a separate 'Date' table? Because a fact only has the days when something happened, and a dimension can hold every day, with Year, Quarter and Month ready to group by. One calendar then serves every fact table that has a date. The time-intelligence chapter builds on it.

When a key finds no match

If an order points at a customer id that customers does not hold, Power BI keeps the order and files it under a blank row on the dimension side. A visible (Blank) in a matrix usually means a foreign key with no partner. Check the keys before you blame the measure.

Common misconceptions

"One big table is simpler, so it must be better"

It is simpler to look at and harder to use. The first time you need distinct customers, a second date, or a corrected product name, the flat table starts costing you. Splitting once, at the start, is the cheap version of that work.

"A slicer filters every table that shares a column name"

Column names mean nothing to the engine. Only relationships carry filters. Two tables with a customer_id and no relationship between them ignore each other, and a slicer on one never touches the other. If a visual does not react to a slicer, open Model view and look for the line.

"Filters travel in both directions along a relationship"

Not by default. They run from the 1 side to the many side. A filter on products thins the lines, not customers, and a filter on order_items does not shrink the lists in other tables. That asymmetry is why the dimensions go on the 1 side and the numbers on the many side.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

In the Harbor & Pine model, which statement about customers[customer_id] and orders[customer_id] is true? customers[customer_id] is unique and sits on the 1 side; orders[customer_id] repeats and sits on the * side Both columns must be unique, otherwise Power BI cannot draw the relationship orders[customer_id] is the key; customers points at it with a foreign key The relationship runs from orders to customers, so an orders filter reaches customers