Dimensional Modeling & the Star Schema
A modeling technique from the 1990s that still powers Power BI, Tableau, Looker, and Fabric semantic models today — because it matches how humans actually ask questions.
Picture your business as one question: "What happened, and along which angles?"
- Facts — what happened (the measurements): sales amount, quantity, clicks, duration. Many rows, mostly numbers + keys.
- Dimensions — the angles you slice by: Customer, Product, Date, Store, Region. Fewer rows, rich descriptive text.
- The star — one fact table in the middle, dimensions around it. It literally looks like a star.
- Why it wins — humans think in "metric BY dimension" (sales by region by month).
- Still the default — it powers virtually every BI tool today.
Facts and Dimensions
Dimensional modeling, popularized by Ralph Kimball in the 1990s, splits the analytical world into two kinds of tables. Fact tables hold the measurements of business events — a sale, a click, a shipment — as numbers plus foreign keys. Dimension tables hold the descriptive context you slice those measurements by — who, what, where, when.
Put one fact table in the center and connect each dimension to it, and the diagram looks like a star. That shape is not an accident — it mirrors the structure of almost every analytical question a business asks.
Why This Shape Endures
1. It Matches How People Ask Questions
Every BI question is a measure sliced by attributes: "revenue (fact) by region by month (dimensions)." The star schema encodes exactly that structure, so translating a business question into a query becomes almost mechanical — pick the fact, pick the dimensions, group.
2. The Grain Is the Most Important Decision
The grain of a fact table is what a single row represents — one order line? one daily account balance? one website session? Declaring the grain first, before adding any column, is the single most important step in dimensional modeling. Get the grain right and everything else follows; get it wrong and the model never quite works.
3. Slowly Changing Dimensions (SCD)
Dimensions change — a customer moves, a product gets recategorized. Do you overwrite the old value (Type 1) or keep history by adding a new versioned row (Type 2)? Handling these slowly changing dimensions correctly is what lets you answer "what did this look like at the time?" — essential for trustworthy historical reporting.
4. Star vs. Snowflake Schema
A pure star keeps each dimension as one flat, denormalized table. A snowflake schema normalizes dimensions into sub-tables (e.g., product → category → department). Stars are simpler and faster for BI; snowflakes save space and tighten consistency. Most modern warehouses favor stars, because storage is cheap and query simplicity wins.
The Star, Visualized
One fact table at the center; dimensions radiating outward — the classic star.
Same Pattern, Every Platform
| The Concept | Snowflake | Databricks | BigQuery | Microsoft Fabric |
|---|---|---|---|---|
| Star schema | Identical — facts + dimensions are a logical pattern, not a feature | |||
| Build with | dbt / SQL | dbt / Spark SQL | dbt / SQL | SQL / Dataflows Gen2 |
| Consume in | Any BI tool | Databricks SQL / BI | Looker / BI Engine | Power BI (star is the recommended model) |
| SCD handling | dbt snapshots / MERGE | dbt snapshots / MERGE | dbt snapshots / MERGE | Dataflows / SQL MERGE |
Feature names evolve — treat this as a capability map, and confirm specifics against current vendor docs.
The takeaway: the star schema is a 30-year-old idea that every modern BI engine — including Power BI in Microsoft Fabric — still optimizes for. It's a pattern, not a product. Learn facts, dimensions, and grain once and you can model for any platform's BI layer.