Star Schema: Fact vs Dimension Tables
Power BI · Data Modeling
Theory
A star schema puts one fact table in the middle and dimension tables around it, like the points of a star. A fact table records business events and holds numeric columns to aggregate plus key columns: each row of Sales is one order line with Qty, Amount, CustomerID, ProductID and OrderDate. Fact tables are long (many rows) and narrow. A dimension table describes the who, what, where and when: Customers has Name, City and Segment; Products has Product, Category and Price. Dimensions are short and wide, with one row per key.
Each dimension connects to the fact table with a one-to-many relationship. Slicers and axes use dimension columns, and measures aggregate fact columns. If a dimension is split into further lookup tables (Product → Subcategory → Category), that is a snowflake; Power BI handles it, but flattening the chain into one Products table is usually simpler and faster.
Why we use it
The Power BI engine and DAX are designed around star schemas. They give simple measures, predictable filtering and small, fast models, and most modeling interview questions start here.
When to use it
Use a star schema for almost every analytical model. Flatten snowflakes when you can. A single flat table may be acceptable for a tiny one-off report, but it becomes painful as soon as a second fact table appears.
How it works
- Look at a table
- Does each row record an event with numbers to add up? Yes: Treat it as a fact table. No: Treat it as a dimension.
- Make sure dimension keys are unique
- Relate each dimension to the fact on its key
- Star schema formed
Syntax
Fact: keys + numeric measures (Sales: OrderID, OrderDate, CustomerID, ProductID, Region, Qty, Amount)
Dimension: unique key + descriptive attributes (Customers: CustomerID, Name, City, Segment)
Relationship: Dimension[Key] 1 → * Fact[Key]Example
Classifying the sample model:
1. Sales: fact (6 order rows here; millions in real life). Aggregate Qty and Amount.
2. Customers: dimension, one row per CustomerID (Asha, Ravi, Meera, Karan).
3. Products: dimension, one row per ProductID (Headphones, Keyboard, Desk Lamp).
4. 'Date': dimension, one row per calendar day.
5. Visual: Products[Category] on the axis, [Total Sales] as value → Electronics ₹4,200, Home ₹2,000.Key points
- Fact = events and numbers; dimension = descriptive attributes
- Facts are long and narrow; dimensions are short and wide
- Each dimension key must be unique
- Slice by dimension columns, aggregate fact columns
- Flatten snowflaked dimensions where practical