Fact and Dimension Tables

A fact table records measurements or occurrences at a declared grain, such as one order line. Dimension tables provide descriptive context such as customer, product, or date. A star schema links a central fact table to those dimensions. A numeric identifier is not a measure merely because its storage type is numeric.

Choose aggregation behavior for every measure. Same-currency line amounts can be additive; balances cannot generally be added across dates; ratios should usually be rebuilt from compatible numerators and denominators. A factless fact can record an occurrence without a numeric measure. Transaction, periodic snapshot, and accumulating snapshot facts represent different observation patterns.

A versioned customer dimension contains several rows per customer, so facts need the intended version. Conformed dimensions align definitions across fact tables; they need not be one physical table. Joining detailed orders to detailed support cases by customer can multiply both measures. Aggregate each fact to the shared reporting grain or define an allocation before combining them.

Reference: Kimball fact tables. See Data Modeling for Operational and Analytical Systems for the worked example.