Webbo3 Power BI · Cohort 1 · Assignment 2 · Released Day 9 · Due Day 11
Assignment 2: Build the Webbo Oil Star Schema
The full model, from the raw workbook to relationships, and a short written explanation of why it is shaped the way it is.
By the end of today you can
- Build Sales from both clean years, with station and product details merged on the cleaned names
- Load the fact table and the four dimension tables
- Connect every relationship correctly
- Explain which table is the fact table, and why, in your own words
Days 5 to 9 built this model as a class. Assignment 2 is proof that you can build it alone, and explain it to someone who was not there.
The Brief
Build the full Webbo Oil star schema. Merge station and product details into Sales on the cleaned names, and the yearly targets into Station_Master on StationID. Connect all relationships. Take a screenshot of Model view. Explain which table is the fact table and why.
Start from a new, empty .pbix file, not your class file. The only things you may copy are your own cleaning queries from Day 4 and Assignment 1: right-click a query in the Queries pane, Copy, and Paste it into the new file's Queries pane.
What You Must Do
| # | Task | Done when |
|---|---|---|
| 1 | Connect to the Webbo Oil workbook and bring in the six data sheets (not Cover) | Every sheet appears as its own query |
| 2 | Clean both sales sheets (the Assignment 1 recipe), then Append them into one query called Sales | Sales has 6,181 rows |
| 3 | Merge Station_Master into Sales on StationName (expand City, Region) and Product_Master on ProductName (expand Category) | Every sale has a Region and a Category; the row count is unchanged |
| 4 | Merge Station_Targets into Station_Master on StationID, sense-check it, and rename the target column AnnualRevenueTarget_NGN | Seven stations, each with a yearly target |
| 5 | Untick Enable load on the yearly queries and Station_Targets; load Sales, Station_Master, Product_Master and Attendant_Directory | Four tables in the Data pane |
| 6 | Create the Date table (Day 7) and mark it as the date table | 731 rows, marked |
| 7 | Relate Sales to all four dimensions: Date, Product_Master, Attendant_Directory and Station_Master | Four Many-to-one, Single relationships, and no line between two dimensions |
| 8 | Arrange Model view as a star and take a screenshot | Sales in the middle, every line visible |
| 9 | Write the explanation (below) | 150 to 300 words |
IDs are given out by a system and never change spelling; names are typed by people. Webbo Oil's sales sheets only carry names, so every merge and relationship here stands on your Day 2 cleaning. Before you hand in, run a Left Anti merge of Sales against each master sheet: all three must return 0 rows. Where an ID does exist, as between Station_Targets and Station_Master, use it.
The Written Explanation
In 150 to 300 words, in your own words, answer these three questions:
- Which table is the fact table, and why? Say what one row of it means (its grain) and what numbers it holds.
- What does each dimension table add to the model? Give one question a manager could ask that needs it.
- Why are the relationships safe even though they are on names, and what would you check if a (Blank) station ever appeared? Why does (Blank) appear for attendants, and why is that one correct?
It uses the words from the lessons (grain, one-to-many, filter), it points at Webbo Oil's own tables rather than giving a textbook definition, and it could be read to the operations manager without her needing a dictionary.
What to Hand In
- Your .pbix (YourName_Assignment2.pbix) as a Google Drive link, set to "Anyone with the link can view".
- The Model view screenshot, as an image in the same Drive folder, or pasted into your Google Doc.
- A Google Doc with the written explanation, and a screenshot of Manage relationships.
Paste the links into the Assignment 2 hand-in box. You can edit until a tutor marks it.
How It Is Marked
| Criterion | Marks | What the tutor checks |
|---|---|---|
| Sales built correctly | 20 | Both years clean and appended, 6,181 rows, Revenue present |
| Merges and targets | 15 | Merges on the cleaned names, Left Outer, row count unchanged; targets merged on StationID and renamed to yearly |
| Relationships | 25 | Four, each Many to one and Single, no dimension-to-dimension line, Date marked |
| Model view screenshot | 10 | A readable star, Sales in the middle |
| Written explanation | 30 | Fact table and grain explained, each dimension's purpose, the name check and the unmanned (Blank) |
Questions You Will Have
If I merged Region into Sales, do I still need the Station_Master relationship?
Yes. The merge proves you can join in Power Query; the relationship is what lets Station_Master filter Sales in the report, including its yearly target, which is not merged into Sales.
Power BI drew some relationships for me when I loaded. Are they right?
Check every one in Manage relationships. Auto-detect matched them on column names, which is right here, but it can also pick the wrong columns or make a line inactive. You are marked on what is there, not on who drew it.
Can I relate Attendant_Directory to Station_Master as well?
No. It creates a second path from Station_Master to Sales, which is exactly what a star schema avoids. Day 7 explains why.
Tick Before You Move On
- ☐ Sales has 6,181 rows and a Revenue column
- ☐ My Left Anti merges against the three master sheets return 0 rows
- ☐ Four relationships, all Many to one, Single, from the dimension to Sales
- ☐ Station_Master has AnnualRevenueTarget_NGN
- ☐ The Date table is marked and has 731 rows
- ☐ I have the .pbix, the screenshots and the written explanation as links
Quick Recap
Build it alone from the raw workbook: clean, append, merge on clean names, load. Relate every dimension to Sales, and nothing to anything else. Explain the fact table by its grain and its numbers. Hand in the .pbix, the screenshots and the explanation as links.
Final Scenario
On Day 11 your tutor opens your model and asks, "What is one row of Sales?"
You say "one transaction at one pump" without looking at your notes, and point to the four lines that let a station, a product, an attendant or a date filter it.
That answer is the whole of Week 2.
Next, Day 10: CALCULATE, the most important function in DAX.