Level 1 · Facts and dimensions

Every Power BI model is built from two kinds of table, and telling them apart is the first thing you do with new data. Get it right and the relationships, the filters and the measures almost draw themselves.

What this practice level is

Power BI Desktop runs only on Windows, so this chapter uses a practice model built for this site. This level is a quiz: no diagram yet, just the decision every model starts with. In Power BI Desktop you would see the tables in Model view (the third icon on the left rail) once they are loaded with Home → Get data.

Fact tables: what happened

A fact table records events. Each row is one thing that happened at a point in time: an order line sold, a parcel shipped, a payment received. Fact tables tend to be:

• long: thousands to millions of rows, growing every day;
• numeric: quantities, amounts, weights, costs, the columns you SUM and AVERAGE;
• full of keys: product_id, order_id, carrier_id, each pointing at the thing involved.

At Harbor & Pine, order_items is the main fact table: one row per order line, with quantity, unit_price and line_total.

Dimension tables: who, what, when, where

A dimension table describes the things that events refer to. One row per product, per customer, per carrier, per day. Dimensions tend to be:

• short: 120 products, 2,000 customers, 6 carriers;
• descriptive: names, categories, cities, segments, the text you put on slicers and axes;
• keyed: one column, like product_id, that is unique and names each row.

products, customers and the Date table are Harbor & Pine’s dimensions. When a manager says “sales by category, by state, by month”, every word after “by” is a dimension column.

Why the split matters

Measures aggregate fact columns; slicers and axes use dimension columns; relationships connect the two, with the dimension on the “one” side. A report that mixes the roles, such as category names typed into the fact table or totals stored in a dimension, ends up with duplicated text, slower refreshes and numbers that change depending on which table you happened to filter.

A quick test

For any new table, finish the sentence “one row is one ____”.

Table	One row is one…	Role
order_items	line on an order	fact
returns	returned order line	fact
products	product	dimension
customers	customer	dimension

If the answer is an event with numbers attached, it is a fact. If it is a thing you describe and group by, it is a dimension.

Common mistakes

• “No sales column, so it’s a dimension.” Shipping costs, web sessions and returns are all facts. What matters is that a row is an event.
• Judging by row count alone. Usually right, but a dimension of 2 million customers is still a dimension.
• One giant table. Flattening everything into one wide sheet is the Excel habit. Power BI works best with separate, related facts and dimensions.