Fact Table vs. Dimension Table
Also called: Fact and dimension tables, Facts and dimensions
A fact table records events and their numbers, like orders and revenue. A dimension table describes the things involved, like products, dates and regions.
The short version
A report answers questions of the form “how much, by what?”. The fact table holds the “how much”: revenue, quantity, tickets, hours. The dimension tables hold the “by what”: by region, by product, by month, by customer segment.
In the example, the Sales fact table has one row per order line, with a Region key and a Revenue value. The Region dimension table has four rows, one per region. Group Revenue by region and you get North America $1.12M, EMEA $0.84M, APAC $0.56M and LATAM $0.28M, which add up to $2.80M.
How they differ
| Fact table | Dimension table | |
|---|---|---|
| A row is | An event or measurement | A thing or category |
| Examples | Sales, web visits, tickets | Date, Product, Region, Customer |
| Size | Many rows (grows over time) | Few rows (changes slowly) |
| Columns | Keys to dimensions, plus numbers | A key, plus descriptive text |
| Role in a report | Supplies values for measures | Supplies axes, legends, slicers, filters |
Spotting them in a report
Look at any visual and sort its fields. The field you sum or count (Revenue) comes from a fact table. The fields on the axis, legend, rows or in a slicer (Region, Month, Category) come from dimensions. If a slicer is built on a column in the fact table, it often means a dimension is missing.
Types of fact tables
- Transaction facts: one row per event, such as an order line. The most common.
- Periodic snapshot facts: one row per period, such as inventory at month end.
- Accumulating facts: one row per process, updated as it moves through stages, such as an order from placed to delivered.
Best practices
- Decide the grain first. Write down what one fact row means before adding columns.
- Keep dimensions descriptive. Names, categories and groupings belong there, not repeated in the facts.
- Use consistent keys. Every key in the fact table should exist in its dimension.
- Share dimensions across facts. One Date table serves Sales, Budget and Headcount.
- Hide keys from report builders. They need to see Region, not Region key.
Facts and dimensions together form a star schema, related through one-to-many relationships.
Mocking up facts and dimensions
Reading a mockup this way is a fast model check: the numbers on cards and bars are facts, and the filters and axes are dimensions. List them before building, and the model nearly designs itself. See wireframing best practices.
