Module 8 · Data Modelling
Stars, snowflakes, dates and history
When to snowflake a dimension, why every model needs a date dimension, and how to keep history when attributes change.
About 35 minutes
The problem
On 1 April 2026, Peace Provisions moved from Kano (North West) to Abuja (North Central). If you simply update their region, every order they placed in 2025 now reports as North Central, and the North West's history quietly shrinks. Models for analysis must decide what happens to history when descriptions change.
The concept
Star or snowflake?
- In a star, each dimension is one table, even if values repeat (category written on every product).
- In a snowflake, dimensions are normalised further (products point to a categories table).
For Power BI and most analytics, prefer the star: fewer relationships, simpler filters, faster queries. Snowflake only when a sub-dimension is large, shared, or maintained separately.
The date dimension. Every model with dates needs its own date table: one row per day, with year, quarter, month name, month number, week, weekday and flags such as is_working_day or is_public_holiday. It lets you:
- group consistently (every report's "Q2" means the same thing);
- show days with no sales (a fact table can't show what didn't happen);
- mark Nigerian public holidays and see their effect on orders;
- use time intelligence in DAX (year-to-date, same period last year).
Slowly changing dimensions (SCDs) are the standard answers to "what happens when an attribute changes?":
| Type | Method | History | Use when |
|---|---|---|---|
| Type 1 | Overwrite the value | Lost | Corrections (a misspelt name) |
| Type 2 | Add a new row with valid-from / valid-to dates; the fact points to the row that was current at the time | Kept | Changes that matter for reporting (region, rep, price band) |
| Type 3 | Add a "previous value" column | Only one step back | Rare: a single planned reorganisation |
Type 2 is why dimensions use surrogate keys: customer 13 now has two rows, so the fact table can't use customer_id alone to know which version applied.
Example
What difference would it make at Kolanut? Revenue for the North West in H1 2025 was ₦31.1m. If a large North West customer moved region and the dimension were type 1, their 2025 orders would move with them, and the H1 2025 figure for North West would shrink after the fact. Last year's board report would no longer match this year's rerun. Type 2 keeps both reports true.
Walkthrough
Choosing the SCD type, attribute by attribute, for Kolanut's customers:
customer_name: corrected spellings → type 1.regionandcity: real moves, and regional reporting matters → type 2.sales_rep: reassignment changes commission and performance reports → type 2.credit_limit: finance only needs the current value → type 1.- Record the decision in the model documentation, so everyone knows which history the reports show.
Practice
Practice
Which slowly-changing-dimension type keeps full history by adding a new row? (Type the number.)
Practice
A date dimension covering 1 January 2025 to 31 December 2026 has one row per day. How many rows does it have?
Check your understanding
Answer every question to check.