Lesson 8 of 15 · Concept
What is a star schema? Facts and dimensions
A star schema keeps events in one fact table and descriptions in dimension tables around it, so each fact is stored once and each description is too. You add up facts and group by dimensions. It earns its keep once several reports share the same definitions.
| Table | Kind | One row is | Holds |
|---|---|---|---|
| Sales | Fact | One sale | date_key, product_key, store_key, amount |
| Date | Dimension | One day | Month, quarter, year, weekday |
| Product | Dimension | One product | Name, category |
| Store | Dimension | One store | City, region |
The date dimension is the one nearly every star has. A row per calendar day makes "by quarter" one grouping everywhere, and it lets a report show a day with no sales, which the fact table can't.
One fact table, one dimension
In this course's fictional example, Vandelay Coffee Supply has a repeatable flow for invoices. The flow still has to say which region each invoice belongs to, because customer Dunder Mifflin moved from South to North on August 1.
| Table | Kind | One row is | Columns |
|---|---|---|---|
| Invoices | Fact | A booked invoice | invoice_id, customer_id, invoice_on, net_eur |
| Customers | Dimension | A customer | customer_id, customer, region, rebate_pct |
Keep facts and dimensions apart and a region changes in one place. The price is that you must decide which region a fact belongs to when the region moved after the invoice.
Current region or region at the time?
Dunder Mifflin's July invoice is €640, the only one dated before the move. The current figures are North €5,578 and South €5,900.
If that invoice counts where Dunder Mifflin was at the time, what is South's margin over North?
€1,602. South becomes €6,540 and North €4,938, because €640 moves across and counts twice in the gap. The current mapping gives €322.
Now choose one for the question at hand, which is where Max Thrust, the founder, should place an account manager. Write one sentence defending it before reading on.
Compare with a model answer
Current region. Max is placing a person to look after accounts from now on, and Dunder Mifflin is North's account now. As-was would suit a commission calculation, where the question is who earned it in July.
Neither mapping is wrong. Each answers a different question, and the choice belongs in the metric contract. Don Tremendous's report uses his old map, so his South still holds Dunder Mifflin, and that's part of why his numbers look as they do.
Slowly changing dimensions: Type 1 and Type 2
A dimension that changes now and then is called a slowly changing dimension, or SCD. Two types cover most cases.
| Type | What happens | Reports show |
|---|---|---|
| SCD Type 1 | Overwrite the row | The current value, for all history |
| SCD Type 2 | Add a new row with dates | The value at the time |
Vandelay's register is Type 2 in shape, with a valid_from date on each Dunder Mifflin row. A plain lookup on customer ID then matches both rows, which is the join fan-out Sydney Swell hit. With Type 2, join on the key and the date, or pick the current row first.
When a flat table is enough, and what a snowflake is
Apply the flat-table test. One report, one owner and one grain can sit happily in one wide table. Move to a star when several reports need the same customers and dates, or when history matters.
A snowflake splits a dimension further, with product pointing to category and category to department. It saves little in most BI work and adds joins. Keep dimensions in one table unless a shared table has its own owner. Power BI's modeling guidance recommends the star shape, and treats the one side of a relationship as the dimension and the many side as the fact. The data preparation lessons on joins show how the tables connect.
To join a fact table to a dimension yourself, try Strata, Oplema's browser app for data prep, with no account needed.