Star Schema and Dimensional Modeling
Dimensional modeling organizes analytical data into fact tables of measurements surrounded by dimension tables of descriptive context.
Facts and dimensions
Dimensional modeling structures a warehouse around two kinds of table. A fact table holds measurements of events: numeric, additive quantities like a count, a duration, or an energy reading, one row per event. Dimension tables hold the descriptive context you filter and group by: the who, what, where, and when. The fact table references dimensions by foreign key.
The star shape
Drawn as a diagram, a central fact table linked to several dimension tables looks like a star, which names the pattern. Queries join the fact table to the dimensions it needs, filter on dimension attributes, and aggregate the fact's measures. Because the fact table is long and narrow and the dimensions are short and wide, this shape scans efficiently.
Grain
The single most important decision is the fact table's grain: what one row represents. "One row per sensor reading per second" is a fine grain; "one row per site per day" is coarse. Every measure and every dimension must be consistent with the declared grain. Mixing grains in one table, for instance daily totals beside per-reading values, produces silent double counting when aggregated.
Slowly changing dimensions
- Type 1: overwrite the old attribute, keeping no history
- Type 2: add a new row with validity dates, preserving history
- Type 3: keep a limited previous-value column
- The choice depends on whether analyses must reflect the past state accurately
Star versus snowflake
A snowflake schema normalizes dimensions into sub-tables, saving space but adding joins. A pure star denormalizes each dimension into one wide table, trading redundancy for simpler, faster queries. Analytical stores usually favor the star because reads dominate and joins are the cost to minimize. This is the same denormalization logic that separates OLAP from OLTP design. See OLAP vs OLTP and warehouse vs lake.