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

  1. Look at a table
  2. Does each row record an event with numbers to add up? Yes: Treat it as a fact table. No: Treat it as a dimension.
  3. Make sure dimension keys are unique
  4. Relate each dimension to the fact on its key
  5. 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

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic