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.

A small sales star
TableKindOne row isHolds
SalesFactOne saledate_key, product_key, store_key, amount
DateDimensionOne dayMonth, quarter, year, weekday
ProductDimensionOne productName, category
StoreDimensionOne storeCity, 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.

Vandelay's star, with net_eur as net value in euros
TableKindOne row isColumns
InvoicesFactA booked invoiceinvoice_id, customer_id, invoice_on, net_eur
CustomersDimensionA customercustomer_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.

Two ways to record a move
TypeWhat happensReports show
SCD Type 1Overwrite the rowThe current value, for all history
SCD Type 2Add a new row with datesThe 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.