DATA ARCHITECTUREKimball

Dimensional Modeling: Facts & Dimensions

How data engineers design data that lasts — pick a topic on the left and its full breakdown loads here: the mental model, ERDs and worked schemas, trade-offs, edge cases, and the decisions that separate a durable model from a fragile one.

⏱ 18 min readTopics chapter readerLevel · Dimensional & Warehouse
01 · Orientation

What You'll Master Here

Facts are what you measure; dimensions are how you slice. Every analytics question is "aggregate a fact, grouped and filtered by dimensions".

⏱ 4 min · Topic 1 of 10

You can already design a clean, normalized operational schema. The moment the question changes from "process this order" to "show revenue by category by month," that shape fights you. Dimensional modeling is the design language analytics uses instead, and this chapter is its core.

Two kinds of table do all the work: facts hold the numbers you measure, dimensions hold the context you slice by. Take the decomposer below apart first — every analytics question you will ever be handed resolves into a measure from a fact plus a slice and a filter from dimensions.

Core mental model

Facts are what you measure; dimensions are how you slice. Every analytics question is "aggregate a fact, grouped and filtered by dimensions".

Why it matters

A well-built star lets a business user answer "measure X by dimension Y" with a simple, predictable query, and lets the warehouse run it quickly. Every later warehouse topic — snowflakes, fact design, slowly changing dimensions — builds directly on it.

fact table
A table of business-event measurements: foreign keys to dimensions plus numeric measures.
dimension table
A table of descriptive context (who, what, where, when) used to filter and label.
star schema
One central fact table surrounded by denormalized dimension tables.
conformed dimension
A dimension shared, identically, across multiple fact tables.
Every analytics question has the same three slotsA ski-pass operator scans a pass at every lift. Four tables hold the whole business.
0/6 slots filled

How many vertical metres did skiers descend in each region during February?

The four tables
  • fact_lift_scansdate_key · resort_key · pass_key · vertical_metres
  • dim_datemonth · is_weekend · season
  • dim_resortresort_name · region · altitude_band
  • dim_pass_typepass_type · age_band · is_season_pass
One of these holds numbers. The other three hold words. That split is the whole chapter.
Which table supplies each slot?
  • The measure — the number you aggregatevertical_metresWorked for you · fact_lift_scans. The number always comes from the fact. That is what makes it a fact.
  • The slice — what you GROUP BYregion
  • The filter — what you put in WHEREmonth
Where each slot is taught
Common mistake

Reusing a normalized OLTP schema directly for analytics. Reports require many joins, run slowly, and are hard for analysts to write correctly.

Better habit

Separate measurements (facts) from context (dimensions).

Design every analytics table around a clearly declared grain.

Make shared dimensions conformed so stars can be compared.

The big idea

Dimensional modeling optimizes for reading and understanding, not writing. It deliberately denormalizes so that the common analytics query is simple and fast.

Remember this

Dimensional modeling splits data into facts (measures) and dimensions (context) arranged as a star, the standard, query-friendly shape for analytics.