Orientation
What You'll Master Here
You can now design a clean, normalized operational schema. But the moment the question changes from "process this order" to "show revenue by category by month," that normalized shape fights you. Dimensional modeling is the design language built specifically for analytics, and this chapter is its core.
You will learn the two kinds of tables analytics is built from, facts (the numbers you measure) and dimensions (the context you slice by), how they snap together into a star schema, and Kimball’s four-step process for designing one from any business process.
This is the most widely used analytical modeling approach in the world, and every later warehouse topic (snowflakes, fact design, slowly changing dimensions) builds directly on it. Every idea here is shown with real DDL, a real star query, and the numbers it returns.
Why it matters
Dimensional models are what make analytics fast and understandable. 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.
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".
- 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.
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.
Dimensional modeling optimizes for reading and understanding, not writing. It deliberately denormalizes so that the common analytics query is simple and fast.
Hold one example in mind throughout: retail sales. We model it as a star, query it, and design it with the 4-step method, so the pieces connect.
Practice prompts
- Name a business process and split it into one fact and three dimensions.
- Write one analytics question as "measure ___ by ___".
Remember this
Dimensional modeling splits data into facts (measures) and dimensions (context) arranged as a star, the standard, query-friendly shape for analytics.