Comparison
Dimension tablevsFact table
Dimension table
everything you would ever put in a group-by or a filter — country, product name, channel — lives in one wide, short table.
The table describing the context of a fact: who, what, where, which. Dimensions are small, wide and heavily denormalised on purpose, because the join to them is on every query and the redundancy is cheap compared to a second hop. When someone complains that the warehouse has 'duplicated' the product name in three places, this is usually the answer, and it is deliberate.
Full entry →Fact table
the table has one row per order line, a handful of numbers you can add up, and the rest is foreign keys.
The table holding the measurements of a business process — one row per occurrence, with numeric measures and keys pointing at the things being described. It is the big one, and everything about the warehouse's cost is decided by its grain and its partitioning. The test of whether something belongs in a fact is whether it is a number you would want to sum or average; if it is a label you would group by, it belongs in a dimension.
Full entry →