Assignment A04 — Build the Star
Skills assessed. Loading a multi-table universe · creating relationships with
correct cardinality and direction · declining the many-to-many trap · proving the star works
with a cross-fact visual.
Submit: A04_LastFirst.pbix. Worth 20 points. This file becomes your working
model for Lessons 5 and 6 — build it like you will live in it, because you will.
Scenario
The flat files are retired. Operations wants one model that can answer sales questions, returns questions, and — pointedly — questions that cross both, like the return-rate analysis the old extract could never touch.
Requirements
- Load five tables from
datasets/clean/:sales.csv,returns.csv,products.csv,stores.csv,customers.csv. Verify row counts: 165,042 · 6,599 · 240 · 39 · 8,000. - Create exactly five relationships, each many-to-one, single direction:
- sales → products (ProductID) · sales → stores (StoreID) · sales → customers (CustomerID)
- returns → products (ProductID) · returns → stores (StoreID)
- Do NOT relate returns to sales. If auto-detect created an OrderID relationship, delete it. The grader checks for its absence by name.
- Arrange Model view as a readable star: facts central, dimensions around. (Ungraded mechanically — but you will thank yourself in Lesson 6.)
- Prove the star on one report page:
- Card: sum of
Quantityfrom sales — 259,421 - Card: sum of
QuantityReturnedfrom returns — 8,334 - Bar chart:
QuantityReturnedbyCategory— Category from the products table, not from any column on returns. Lighting leads at 1,910. - Slicer:
Regionfrom stores. Test: selecting a region must move both cards — that is two facts answering one dimension, the star working.
- Card: sum of
The bar chart is the point
Putting products.Category against returns.QuantityReturned only works if the
returns → products relationship exists and flows. If the bar chart shows one bar labeled
"blank," your relationship is missing or misdirected — the visual is diagnosing your model.
Checklist (what the grader checks)
| # | Check | Points |
|---|---|---|
| 1 | Five tables loaded with verified row counts | 3 |
| 2 | Five relationships, correct columns, many-to-one, single direction | 6 |
| 3 | No relationship between returns and sales | 2 |
| 4 | Card: units sold = 259,421 | 2 |
| 5 | Card: units returned = 8,334 | 2 |
| 6 | Bar: returns by products.Category, Lighting top at 1,910 | 3 |
| 7 | Region slicer filters both cards | 1 |
| 8 | File named A04_LastFirst.pbix | 1 |
Before you submit
- Click every region in the slicer. Both cards must move every time. A card that sits still is a fact the filter cannot reach — trace the relationship path and find the break.
- Open Model view one last time and explain each line to yourself out loud: which side is the one, why, and which way the filter flows. If any line resists explanation, it does not belong in the model.
- Save, close, reopen, verify.