Lecture video · L04 The Data Model

[Slide 1] Module Two begins tonight, and it begins with a question that sounds innocent: what is our return rate by category? Sales are one hundred sixty-five thousand rows in one table. Returns are six and a half thousand rows in another. And I will show you that no amount of Module One skill — no append, no merge, no flat file — answers that question honestly. What answers it is a shape, worked out decades before Power BI existed, called the star schema. By the end of tonight your pile of clean tables becomes a model, and Model view — nearly empty since day one — becomes the most important screen in the application.

[Slide 2] Here is why the question is fatal to everything we have done so far. Sales and returns are both tables of events, but they are different kinds of event, recorded at different resolutions. Appending them stacks apples on oranges. Merging returns into sales forces each return to pick one order line to attach to, double-counting some things and losing others. And pre-flattening everything into one giant table fails hardest of all, because a row of that table would have to be a sale and a return and, once finance's targets arrive, a monthly target — three kinds of fact at three resolutions. The answer is to stop trying to make one table and start relating several.

[Slide 3] The classifying question for any table you will ever meet: what does one row mean? If one row records something that happened — an order line, a returned item, a monthly target, a stock snapshot — the table is a fact table. If one row describes something that exists — a product, a store, a customer — it is a dimension. Facts are verbs; dimensions are nouns. Look at the sizes: our facts run from about a thousand rows to a hundred sixty-five thousand, our dimensions from thirty-nine to eight thousand. Facts are long and grow daily; dimensions are short and mostly stable. And count our facts: four of them, at four different grains. That number four is precisely why no single flat table could ever hold this business.

[Slide 4] Arrange 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 payoff is one word: sharing. The products table describes sales rows and returns rows at the same time. So the killer question — return rate by category — becomes almost embarrassingly simple: Category, from products, filters both facts at once, and units returned divides by units sold. Across the whole dataset that is eight thousand three hundred thirty-four returned against two hundred fifty-nine thousand four hundred twenty-one sold — a three point two percent return rate. One dimension, two facts, one honest answer. That is what the shape buys.

[Slide 5] Drag ProductID from sales onto ProductID in products, and the relationship appears marked many-to-one: many sales rows point at one product. Here is what I need you to internalize: Power BI did not decide that cardinality. Your data did. A relationship's shape follows from uniqueness — the one side must be a column where every value appears exactly once. Products qualifies: two hundred forty rows, two hundred forty distinct. So do stores and customers. Now profile sales' OrderID: a hundred sixty-five thousand rows, but only eighty-five and a half thousand distinct — because one order holds many lines. That column is not a key; it is a grain. This is Lesson Three's profiling habit graduating into law: rows versus distinct, ten seconds, before every relationship you ever create.

[Slide 6] Quick discussion. Power BI offers to detect relationships automatically, and it works by matching column names. Take a minute and invent two ways that could go wrong. The classics: two tables both have a column called Name — customer names and product names — and the algorithm happily proposes marrying them. Or a leftover Region text column matches a Region lookup you meant to delete. On our tidy universe auto-detect would guess well; real models are not this polite. The answer is neither blind acceptance nor refusal: treat suggestions as drafts. You read the cardinality, you confirm the key, you approve the line. A model whose relationships you cannot explain is a model you cannot defend in a meeting.

[Slide 7] Now the arrows on the lines — filter direction — which answer something you have taken on faith since day one: how does clicking Lighting in a slicer change a revenue card? Watch the mechanism. The click filters the products table down to its forty lighting products. That filter flows down the relationship, from the one side to the many side, and selects exactly the sales rows pointing at those products — thirty-eight thousand three hundred sixty-nine of the hundred sixty-five thousand. Then every measure computes over the survivors: Lighting's eight point four seven million dollars, out of the company's sixty-six point eight one million total. Dimensions filter facts, in a single direction. That default is right so consistently that this course will not flip a relationship to bidirectional even once — when a filter refuses to flow, the model wants a shared dimension, not a setting.

[Slide 8] And now the mistake this dataset was built to let you make honestly. Returns and sales share an OrderID column, and it feels natural to connect them. Try it, and the dialog announces many-to-many — because, as your key check already told you, OrderID is unique in neither table. Here is the dangerous part: the dialog warns you, and then offers the OK button anyway. Click it and one return row matches every line of its order, and totals quietly multiply — Lesson Three's growing join, now living permanently inside your model, corrupting every visual it touches without one error message. The rule that keeps you safe has five words: facts never relate to facts. They meet in the dimensions they share. Return rate by category, by store, by month — every question that tempted you into that line routes through a shared dimension instead, cleanly.

[Slide 9] Let me close the loop with a familiar number. Put Region on a slicer and revenue on a card, click Midwest, and the model walks from stores into sales and reports two point seven six million dollars for twenty twenty-five — the same verified figure your Lesson One flat extract produced. Same answer, and that agreement matters: it says the model is computing honestly. But notice what else this version can do that the extract never could: the same click reaches the Midwest's returns, its targets, its inventory. The extract froze one pre-joined answer; the model relates, live, in every direction the star extends. That is the difference you built tonight.

[Slide 10] The week in five ideas. Grain classifies every table: what does one row mean — happened is a fact, exists is a dimension. The star shares dimensions across facts, which is why return rate by category went from impossible to a ten-second visual. Cardinality is a fact about uniqueness that you verify in ten seconds, rows versus distinct. Filters flow from dimension to fact — you watched one click select thirty-eight thousand lines and recompute a card. And facts never relate to facts; the many-to-many warning is the model asking you to reconsider the shape. This week's assignment has you build the star properly, and I mean it when I say build it well: it is the file Lessons Five and Six live inside. Next week we stop borrowing Power BI's automatic sums and start writing our own — a language called DAX, and the single most confused distinction in the product: the calculated column versus the measure.