Dimension Tables

Dimension tables provide the descriptive context needed to understand and analyze the measurements stored in fact tables. While fact tables capture business events, dimension tables answer the who, what, when and where of those events.

• The "Nouns": Think of dimension tables as the nouns of the business. Each dimension represents a business concept used to describe a business process, such as Customer, Product, Store, Employee or Date. Dimensions can be shared across multiple fact tables to provide a consistent view of the business.
• Descriptive Attributes: Dimension tables primarily contain descriptive attributes used to filter, group, label and explore facts. For example, a Product dimension might contain product_name, brand, category and department.
• Table Structure: A dimension table typically contains a surrogate key - usually a generated integer - along with descriptive attributes and, where applicable, the business key from the source system.
• Physical Characteristics: Dimension tables are typically wide and shallow - they contain relatively few rows compared with fact tables but can have many descriptive columns.

Dimensions provide the filtering and grouping capabilities used in Business Intelligence. For example, when a user asks for "Total Sales by Region," the region attribute comes from a dimension table.

💡 Numeric Value: Dimension Attribute or Fact Measure?

It can sometimes be unclear whether to model a numeric value as a Fact measure or a Dimension attribute.

The key distinction is how the number will be used. If the numeric value is a discrete or standard characteristic of an entity used primarily for filtering or grouping, it should be modeled as a dimension attribute. If the number is a continuously valued measurement representing a business event and is used primarily for calculations or aggregations, it belongs in a fact table as a measure.