Webbo3 Power BI · Cohort 1 · Day 7 of 16
Day 7: Star Schema and Relationships
The shape every good Power BI model takes, and the lines that make filters travel.
By the end of today you can
- Tell a fact table from a dimension table
- Draw the Webbo Oil star schema and say what each table's grain is
- Create one-to-many relationships in Model view
- Set cardinality and cross filter direction, and avoid ambiguous paths
- Build a Date table and mark it as the date table
Yesterday every region showed the same total. Today you draw four lines in Model view and the numbers split properly.
Open your Day 6 file and go to Model view (the third icon on the left). You should see four table cards, probably with some lines already drawn between them.
1. Facts and Dimensions
Definition
A fact table records events: one row per thing that happened (a sale, a booking, a transaction), with the numbers you add up. A dimension table describes the things involved in those events: one row per station, per product, per attendant, per day, with the words you filter and group by.
Real-Life Example
A supermarket receipt roll is the fact table: every line a sale, with a quantity and an amount. The product catalogue, the staff list and the branch list are dimension tables: each thing once, with its details. You never reprint the catalogue on the receipt roll; the barcode points to it.
Types: Webbo Oil's Tables
| Table | Type | Grain (one row per...) | What you do with it |
|---|---|---|---|
| Sales | Fact | Transaction | Add up Quantity and TotalAmount_NGN |
| Station_Master | Dimension | Station | Filter and group by region and city; hold the yearly target |
| Product_Master | Dimension | Product | Filter and group by product and category (Fuel or Lubricant) |
| Attendant_Directory | Dimension | Attendant | Filter and group by attendant |
| Date | Dimension | Calendar day | Filter and group by year, quarter, month |
The grain is what one row means. Say it out loud for every table you load. If you cannot, you cannot trust any total built on it.
Picture the receipt roll and the catalogue: events in one place, descriptions in another, joined by a code.
2. The Star Schema
Definition
A star schema puts the fact table in the middle with each dimension table connected directly to it, like points of a star. Every dimension filters the fact; dimensions do not connect to each other.
Real-Life Example
A bus terminal with routes going out to every town. Each town connects to the terminal directly. If towns also had roads between themselves, a driver could reach the same place two ways, and the timetable would stop making sense.
Types: Model Shapes
| Shape | What it looks like | Verdict |
|---|---|---|
| Star | Every dimension joins the fact directly | What you build in this course |
| Snowflake | Dimensions join other dimensions (Attendant_Directory to Station_Master...) | Works, but slower and harder to read |
| One flat table | Everything merged into one wide table | Fine for a quick look; weak for a real report |
Picture the bus terminal: every town one direct road, and no shortcuts between towns.
3. Creating Relationships
Definition
A relationship is a line between a column in one table and a column in another whose values match. It lets a filter on one table travel to the other: pick "South West" in Station_Master and Sales shows only South West sales.
Real-Life Example
A phone contact linked to a WhatsApp chat. Tap the contact and the chat opens; the link is the phone number, which is the same in both places.
Types: The Settings on Every Relationship
| Setting | Options | For Webbo Oil |
|---|---|---|
| Cardinality | One-to-many (1:*), one-to-one (1:1), many-to-many (*:*) | One-to-many every time: one product, many sales |
| Cross filter direction | Single, or Both | Single: from the dimension to Sales |
| Active | Active (solid line) or inactive (dotted) | Active |
Draw the lines, or check the ones Power BI drew
All three relationships are on names, because the sales sheets carry no IDs. They are safe only because Day 2 cleaned the names and Day 5's Left Anti found zero unmatched rows. If one spelling had slipped through, those sales would appear under a (Blank) station or product in every visual.
615 sales say Unmanned, and there is no Unmanned row in Attendant_Directory, so a visual grouped by Attendant_Directory[AttendantName] puts them under (Blank). That is correct: no attendant served them. To see "Unmanned" by name, group by Sales[AttendantName] instead.
Proving the Relationships Work
Attendant_Directory has a Station column, so it is tempting to join it to Station_Master as well. Then a filter on Station_Master could reach Sales two ways (directly, and through the attendants), which Power BI calls an ambiguous path: it makes the new line inactive (dotted) or refuses it. Keep the star: every dimension joins Sales only.
Picture the phone contact and the chat: one number, one tap, and the right conversation opens.
4. The Date Table
Definition
A Date table has one row for every day in the period your data covers, with no gaps, and columns for year, quarter and month. It is the dimension that time calculations (Day 12) rely on, and it must be marked as a date table so Power BI trusts it.
Real-Life Example
A wall calendar. It has every day, including Sundays when the station sold little. A list of only the days with sales is not a calendar: ask it about a quiet day and it has nothing to say.
You create it with a short DAX formula (DAX properly starts tomorrow; read this one line by line). CALENDAR(start, end) makes a one-column table of every date between two dates. ADDCOLUMNS adds named columns to it, each worked out from [Date] on that row.
Click path
731 rows: 365 days of 2023 plus 366 of 2024, a leap year. Sort by column makes Month sort Jan, Feb, Mar instead of alphabetically (Apr, Aug, Dec).
Build a table visual with Date[Year] and the sum of Sales[TotalAmount_NGN]. You should see two rows that add up to ₦145,854,226. If you see one row with the total, the Date relationship is missing.
Picture the wall calendar: every day printed, busy or not.
Do This Now
- Say the grain of each of your five tables out loud.
- Draw the three relationships in Model view and check each is Many to one, Single.
- Rebuild the Region visual and check it splits and adds up.
- Create the Date table, mark it, sort Month by Month Number, and relate it to Sales.
- Arrange Model view as a star, Sales in the middle, and take a screenshot.
Checkpoint
Q1. Which Webbo Oil table is the fact table, and why?
Sales. Each row is an event (a sale) and it holds the numbers we add up, Quantity and TotalAmount_NGN. The others describe the things involved in those events.
Q2. What does one-to-many mean for Product_Master and Sales?
Each product appears once in Product_Master (the one side) and many times in Sales (the many side).
Q3. Why not also relate Attendant_Directory to Station_Master?
It creates a second path from Station_Master to Sales. Power BI cannot tell which path a filter should take, so it makes one relationship inactive. Keep the star: dimensions join the fact only.
Q4. Why must a Date table have every day, not just days with sales?
Time calculations such as year to date move through continuous dates. Gaps make them wrong or make Power BI refuse to mark the table as a date table.
Tick Before You Move On
- ☐ Sales is in the middle with four dimensions around it
- ☐ Every relationship is Many to one, Single, active
- ☐ Region and Category visuals split and add up to the total
- ☐ My Date table has 731 rows and is marked as the date table
- ☐ Month sorts January to December
Quick Recap
Facts are events with numbers; dimensions describe the things involved. A star schema joins every dimension directly to the fact. Relationships are one-to-many, single direction, from dimension to fact. Never give a filter two paths to the same table. A marked Date table with every day is what time calculations stand on.
Final Scenario
The manager asks for revenue by region, then by product category, then by quarter.
Last week each of those was a new Group By query. Now it is the same Sales table filtered by a different dimension, and each answer takes seconds, because the lines in Model view do the matching for you.
Build the model once, and every question after it is quick.
Next, Day 8: calculated columns versus measures, and the basics of DAX.