Glossary

Dimension

Also known as: dimension table · dim table

Definition

A dimension describes the things that facts are about: customers, products, stores, dates. Its attributes, such as a customer's region or a product's category, are what reports group and filter by. Dimensions change slowly over time, and how those changes are kept, overwritten (SCD Type 1) or kept as history (SCD Type 2), decides what past reports show.

For example, a retail model might have a dim_product table with one row per product: its id, name, brand, category, size and launch date. A fact_sales table records each sale with a product key, a store key, a date key, a quantity and an amount. A report of revenue by category joins the facts to dim_product and groups by the category column. The facts hold the numbers; the dimension holds the words people use to ask about them.

Dimensions are usually wide and descriptive, with many text columns, and they are where business rules about naming live: what counts as a region, how product categories roll up, which customers count as active. A date dimension, with one row per day and columns such as week, month, quarter and holiday flags, is common because it makes calendar questions easy without date arithmetic in every report.

A dimension usually has a surrogate key for joins and keeps the source's natural key for lookups. When attributes change, the choice between overwriting and keeping history decides whether last year's sales report still shows last year's categories. See also grain and semantic model.

Conformed dimensions are shared by several fact tables, such as one customer dimension used by both the sales and the support facts. Sharing them is what lets a report compare sales and support tickets by the same customer segment, because both facts use the same definition of a segment. See also fact table and SCD Type 1.

Emolysis's SCD Type 2 shape is designed for dimensions with history. Dimensions are joined to a fact table. A dimension is usually small and slow to change, while the fact table it describes grows with every event.