Practice — Hot · Solution
1 · The verdicts
| Decision | Verdict | Consequence |
|---|---|---|
| 1 · Category merged in, products deleted | Wrong — subtly | Right technique from Lesson 3, applied past its expiry date. With products gone, returns has no Category (so "return rate by category" is dead on arrival), and restoring it means a second merged copy — then a third for targets. Three copies of one truth, maintained separately, is how "Lighting" and "lighting" end up as different categories in different facts. The dimension existed precisely to hold Category once and lend it to every fact. Simpler-in-tables bought harder-in-truth. |
| 2 · returns ↔ sales, M:M, bidirectional | Wrong — dangerously | The lesson's central trap with the difficulty turned up: many-to-many multiplies (one return matches every line of its order), and bidirectional lets the ambiguity flow both directions, so even pure sales visuals can be disturbed by returns-side filters. Totals stop reconciling with verified figures, and no error ever fires. "Connected both ways" described the feeling, not the arithmetic. |
| 3 · targets related to nothing | Partially sound | The instinct to not force a bad relationship was correct — targets has no ID column matching anything, and jamming it in via Category (non-unique on both sides) would have made another many-to-many. The failure is stopping at "left it alone": unrelated, targets can only ever be its own island — no targets-versus-actuals page, which is the entire reason finance sent the table. |
2 · The rebuild, and what targets is waiting for
The star from A04 handles decisions 1 and 2: restore products, relate both facts to it, delete the fact-to-fact line. For targets, name what is missing: targets' grain is region × category × month, and the star currently has no unique-keyed table for any of those three. The model needs small conformed dimensions — at minimum a date/calendar table (unique row per month or day) and, cleanly, a tiny Category dimension (5 rows) and Region dimension (6) — so targets and sales can both hang off shared "one" sides. That is precisely the shape Lesson 6 builds when the date table arrives; targets has been designed as the reason it matters.
3 · A handoff note that would hold
"Kept your Power Query cleanup end to end — it is solid and I did not touch it. Two model changes: products is back as a shared dimension with both facts related to it, because returns needs Category as much as sales does and one copy of a truth beats three; and the returns-to-sales OrderID relationship is gone — it was many-to-many both ways, which multiplies totals unpredictably, and every question it served routes through the shared dimensions instead. Your call on targets was half right: forcing it in would have been worse, but it is not unusable — it needs a calendar and category dimension to hang from, which is the next build. The principle behind all three: facts never connect to facts; they meet in the dimensions they share."
Reflection answers
- A principle predicts consequences; a slogan predicts feelings. "Simpler is better" said nothing about what happens when the second fact needs Category. "Facts meet in shared dimensions" told you decision 1 and 2's failures and decision 3's fix before opening the file. If a rule cannot tell you what breaks, it is a slogan.
- Decision 3 is visibly incomplete — an island on the diagram, a targets-versus-actuals page that cannot be built. Decisions 1 and 2 are invisibly wrong — everything renders, and the numbers lie. In models as in Power Query, the dangerous failures are the quiet ones; a gap you can see beats a wrongness you cannot.