🧩Dimension Table

NEW

A table that stores descriptive attributes and hierarchies used to filter, group, and label facts in analytic queries.

DESCRIPTION

Dimension tables provide the context for measurements in a dimensional model. They contain textual attributes, hierarchies, and surrogate keys that join to fact tables. Good dimensions are denormalized, wide, and designed for understandability and query performance. Techniques like slowly changing dimensions capture historical attribute changes. Conformed dimensions are shared across marts to ensure consistency. Common dimensions include date, product, customer, and geography. Dimensions support descriptive analytics, drill-downs, and flexible slicing and dicing. They are central to Kimball-style star schemas widely used in BI.

ALTERNATIVE DEFINITIONS

Wikipedia

A dimension is a structure that categorizes facts and measures in order to enable users to answer business questions.

Highlights categorization of facts and measures for business analysis.

Source

Kimball Group

Dimension tables are wide, denormalized tables that contain the descriptive attributes used in dimensional models and are the entry points for filtering and grouping facts.

Emphasizes denormalization and role in filtering/grouping facts.

Source

Microsoft Learn

In a star schema, dimension tables hold descriptive information that is used to slice and filter measures in related fact tables.

Places dimensions within the star schema context for BI.

Source

RELATED TERMS

Star Schema Snowflake Schema Slowly Changing Dimension Surrogate Key Data Granularity Dimension Hierarchy Kimball Methodology Data Mart

COMPETING TERMS

Fact

RELATIONS

• Broader: Table

KNOWLEDGE AREAS

Data Warehousing & BI Data Modeling Data Architecture

TARGET AUDIENCE

Analytics Engineer Data Modeler BI Specialist Data Architect Data Analyst Data Steward

EXTERNAL SOURCES

• Wikipedia - Dimension (data warehouse)
• Kimball Group - Foundational guidance on dimensions
• Microsoft - Star schema and dimensions guidance
• AWS Redshift - Designing dimension tables

TOP QUESTIONS

Why are Dimension Tables denormalized?

Denormalization optimizes query performance and usability, reducing joins and simplifying analytics for BI tools and users.

Category: Best practices

What is the grain of a Dimension Table?

Grain defines the level of detail for each row, such as product or customer. It must be consistent and explicit.

Category: Requirements

How do Slowly Changing Dimensions affect a Dimension Table?

They manage attribute history. Type 2 adds new rows with new surrogate keys; Type 1 overwrites in place.

Category: Management

Why use surrogate keys in a Dimension Table?

They decouple from volatile natural keys, simplify joins, and support historical changes reliably.

Category: Relevancy

What are common mistakes with Dimension Tables?

Undefined grain, excessive normalization, and missing hierarchies hinder performance and usability.

Category: Mistakes

How do Conformed Dimensions relate to a Dimension Table?

They are shared dimension tables used across facts and marts to ensure consistent analysis.

Category: Relationship

When should a Snowflake Schema be used for Dimension Tables?

Use snowflaking sparingly for very large or hierarchical dimensions where storage and maintenance justify extra joins.

Category: Opinion

What testing is needed for Dimension Tables?

Validate uniqueness of surrogate keys, referential integrity, hierarchy completeness, and attribute value consistency.

Category: Testing

How do BI tools leverage a Dimension Table?

They use dimensions to filter, group, and label measures, enabling slice-and-dice and drill-down analyses.

Category: Example