Star Schema: Fact & Dimension Tables
The professional way to model data: one central fact table of events surrounded by dimension tables that describe them.
What you will learn
- Tell a fact table apart from a dimension table
- Arrange tables into a star schema
- Explain why this shape makes models fast and correct
Why the shape of your model matters
You can dump every column into one giant table — but professionals almost never do. The proven design is the star schema: one central table of events (the numbers you measure) surrounded by smaller tables that describe those events. Drawn out, it looks like a star, which is where the name comes from.
Two kinds of table
Every table in a good model is one of two types. Learning to tell them apart is the whole skill.
| Fact table | Dimension table | |
|---|---|---|
| Holds | Events / transactions | Descriptions / lookups |
| Example | Sales (each row = one sale) | Products, Customers, Dates |
| Typical columns | Amount, Quantity, dates, keys | Name, Category, City, Country |
| Size | Many rows (long & thin) | Fewer rows (short & wide) |
| You measure it? | Yes — sum, count, average | No — you slice/group by it |
A plain-words test: if a row is something that happened (a sale, a click, a payment), it belongs in a fact table. If a row describes a thing (a product, a customer, a date), it belongs in a dimension table.
A worked star schema
Take a shop. The fact is each sale. The dimensions describe each sale: which product, which customer, on which date. Each dimension links to the fact by a shared key column.
Products Customers
(dimension) (dimension)
\ /
\ ProductID / CustomerID
\ /
[ Sales ] <- FACT (the events)
/ \
/ DateKey \
/ (more dimensions...)
Dates
(dimension)Note: Output: The Sales fact sits in the middle, joined to Products, Customers and Dates — the three dimensions. Each line is a one-to-many relationship (one product → many sales). With this shape, a single measure like Total Sales can be sliced by product Category, customer City or any date field, because every dimension filters the fact.
Why this beats one big flat table
- No repetition: a product’s category is stored once in Products, not copied onto every sale row.
- Faster: Power BI is built and optimised for exactly this star shape — measures run quicker.
- Correct totals: filters flow cleanly from each dimension into the one fact, so numbers add up right.
- Easy to read: anyone can see at a glance what is measured (the centre) and what describes it (the points).
The special dimension: a Date table
One dimension deserves its own seat at the table: a Date table. This is a dimension with one row per calendar day, plus helper columns — Year, Quarter, Month, Day name, Weekday/weekend. You connect your fact’s date column to it. It feels like extra work, but it unlocks two big things: clean date grouping (group by Month or Quarter easily) and, crucially, time-intelligence measures like year-to-date and last-year comparisons (the next lessons).
Date Year Quarter Month MonthName Weekday
2026-01-01 2026 Q1 1 January Thu
2026-01-02 2026 Q1 1 January Fri
...one row for every single day...Note: Output: The Date table gives every calendar day its own row with the year, quarter and month already broken out. Once you connect your Sales date to it and tell Power BI to “Mark as date table”, you can group sales by Quarter or Month effortlessly and use time-intelligence functions reliably.
You can build a Date table with one DAX line in New Table (Modeling ribbon):
Dates = CALENDAR(DATE(2026,1,1), DATE(2026,12,31))Note: Output: This creates a Dates table with one row for every day of 2026 (365 rows). From there you add columns like Year = YEAR(Dates[Date]) and Month = MONTH(Dates[Date]), then mark it as the date table — your model now has a proper calendar to drive all time analysis.
Tip: Aim for one fact table in the middle, dimensions around it, each joined one-to-many. If you find yourself copying the same descriptive text onto thousands of fact rows, that text belongs in a dimension instead.
Watch out: Do not link dimensions directly to each other or build long chains (a “snowflake”). Keep every dimension connected straight to the central fact — that flat star keeps filters simple and totals correct.
Q. In a star schema, which table holds the events you measure (like each individual sale)?
✍️ Practice
- Take a dataset and label each table as a fact or a dimension, explaining your reasoning.
- Sketch a star schema with one fact in the middle and at least three dimensions around it.
🏠 Homework
- Build a Date table with CALENDAR, add Year and Month columns, mark it as the date table, and connect it to a fact table’s date column.