The Data Model

Why this matters

Module 1 ended with you combining files like a professional. Now the business asks a question that ends the flat-file era for good: "What is our return rate by category?"

Think about what answering it requires. Sales live in one table — 165,042 rows, one per product per order, covering three and a half years. Returns live in another — 6,599 rows, one per returned item. You cannot append them (different shapes, different meanings). You cannot merge returns into sales row-by-row without double-counting or losing partial returns. And you certainly cannot pre-flatten everything into one giant table, because a row of that table would have to be simultaneously a sale and a return and, once finance's targets arrive, a monthly target — three different kinds of fact that happen at three different resolutions.

The answer to this problem was worked out decades before Power BI existed, and Power BI is built around it: keep the tables separate, relate them, and let the model do the joining at query time. This lesson is where your pile of clean tables becomes a model — and Model view, nearly empty since Lesson 1, becomes the most important screen in the application.

Grain: the one question that classifies every table

Ask of any table: "what does one row mean?" That answer is the table's grain, and it splits every table you will ever meet into two families:

Facts are verbs; dimensions are nouns. A sale happened; a product exists. Notice the sizes: facts are three to four orders of magnitude longer than dimensions here, and that ratio is typical. Notice also that Lantern & Pine has four fact tables at four different grains — an order line, a returned item, a monthly target, a monthly stock snapshot. That is precisely why no single flat table could ever hold this business.

The star schema

Arrange the model with facts in the middle and dimensions around them, one relationship from each fact to each dimension it mentions, and the diagram draws itself into a star:

The star's payoff is that dimensions are shared. The products table describes sales rows and returns rows. So "return rate by category" — the question that broke the flat file — becomes almost embarrassingly simple: Category (from products) filters both facts at once, units returned divides by units sold, done. Across the whole dataset that is 8,334 returned units against 259,421 sold — a 3.2% return rate — and the same calculation per-category lands between 3.15% and 3.30%. One dimension, two facts, one honest answer.

Relationships: cardinality is a fact, not a setting

Drag ProductID from sales onto ProductID in products and Power BI creates a relationship marked many-to-one (*:1): many sales rows point at one product row. But here is the part to internalize: Power BI did not decide that — the data did. A relationship's cardinality follows from whether each side's column is unique:

This is Lesson 3's key-profiling habit graduating into a law: the "one" side of a relationship must be a unique column. Check it in ten seconds — rows versus distinct — before creating any relationship, and you will never be surprised by what the dialog offers.

Filter direction: how a click becomes a calculation

The arrows on each relationship line show filter direction, and they answer a question you have been taking on faith since Lesson 1: how does clicking "Lighting" in a slicer change a revenue card? Like this: the filter lands on the products dimension (40 lighting products of the 240), flows down the relationship from the one side to the many side, and selects exactly the sales rows pointing at those products — 38,369 of the 165,042 lines. Every measure then computes over the surviving rows: Lighting's $8.47M of the company's $66.81M total.

Dimensions filter facts. That is the default, single direction, and it is the right direction so consistently that this course will not turn on bidirectional filtering even once — when you meet a problem that seems to need it, the model usually needs a shared dimension instead.

The trap: relating facts to facts

Now the mistake this dataset was built to let you make honestly. It seems reasonable to relate returns to sales directly — they share an OrderID column, and returns "belong to" sales. Try it, and the dialog announces a many-to-many relationship, because — as the table above showed — OrderID is unique in neither table. Power BI will let you proceed. Do not. A many-to-many between two facts means one return row matches every line of its order, and totals quietly multiply — the join that grows, from Lesson 3, now living permanently inside your model.

The star answer: facts never relate to facts. They meet in the dimensions they share. Returns already relates to products (its ProductID is many-to-one, cleanly) and through it shares Category with sales. Every question that made you want the fact-to-fact line — return rate by category, by store, by month — routes through a shared dimension instead, without a single multiplied row.

What the model buys you: one number, honestly

A model earns its keep the first time one filter answers across tables. With the star built, put Region on a slicer and revenue on a card, click Midwest, and the model walks stores → sales and reports $2.76M for 2025 — the same verified figure your L01 flat extract produced, now computed live across related tables instead of pre-baked into an export. Same answer, but this version also knows the Midwest's returns, its targets, and its inventory — because the model relates, where the extract froze.

Common mistakes

• Building one giant flat table because "it worked in Lesson 1." It worked because there was one fact at one grain. The moment returns or targets arrive, flat means double-counted or unanswerable.
• Accepting auto-detected relationships unread. Same-named columns are a hint, not a truth. Read every line's cardinality; delete what you cannot explain.
• Relating two facts because they share a column. Shared columns make a many-to-many, not a model. Facts meet in dimensions.
• Turning on bidirectional filtering to "fix" a filter that won't flow. The missing flow is almost always a missing shared dimension. Solve the shape, not the setting.
• Skipping the key check. Rows versus distinct, ten seconds, before every relationship. 8,000 = 8,000 is a key; 165,042 vs 85,538 is a grain.

Summary