🗂️Dimension

NEW

Also known as: Conformed Dimension

A descriptive structure that categorizes and provides context for facts and measures, enabling filtering, grouping, and slicing in analytics.

DESCRIPTION

Dimensions contain business descriptors like customer, product, or date used to analyze measures in fact tables. They include attributes and often hierarchies (e.g., Category→Subcategory→Product). Surrogate keys link dimensions to facts, insulating from source volatility. Conformed dimensions are shared across marts to ensure consistent analysis. Dimensions may be slowly changing to maintain history. Good dimensional design enhances usability and performance of BI queries.

ALTERNATIVE DEFINITIONS

Wikipedia

In data warehousing, a dimension is a structure that categorizes facts and measures to enable users to answer business questions.

Focuses on categorization for analytical questions.

Source

Microsoft Learn

A dimension is a collection of related attributes that provide descriptive information for analyzing measures in a cube.

Emphasizes attributes and their role in analysis models.

Source

RELATED TERMS

Star Schema Snowflake Schema Hierarchy Attribute Slowly Changing Dimension Surrogate Key OLAP Data Granularity Bridge Table

COMPETING TERMS

Fact

RELATIONS

• Narrower: VC Dimension

KNOWLEDGE AREAS

Data Warehousing & BI Data Modeling

TARGET AUDIENCE

BI Specialist Data Analyst Analytics Engineer Data Modeler Data Engineer

EXTERNAL SOURCES

• Wikipedia - Dimension (data warehouse)
• Kimball Group - SCD design patterns
• Microsoft - Dimensions in analysis services
• Tableau - Difference between measures and dimensions

TOP QUESTIONS

How do dimensions differ from facts?

Dimensions describe the context (who, what, when, where), while facts store numeric measurements linked to those dimensions.

Category: Misunderstanding

What is a conformed dimension?

A dimension shared across multiple facts or marts with consistent keys and definitions, enabling cross-subject analysis.

Category: Example

When should I use surrogate keys in dimensions?

Use them to decouple from source keys, support SCDs, and maintain integrity across systems.

Category: Requirements

How do hierarchies in dimensions help analysis?

They enable drill-down and roll-up (e.g., Year→Quarter→Month), improving navigation and aggregations.

Category: Example

What are common dimension design mistakes?

Too many low-value attributes, missing hierarchies, and not handling duplicate business keys or SCD types.

Category: Mistakes

How do slowly changing dimensions work?

They track historical changes to attributes using types (e.g., Type 1 overwrite, Type 2 add new row).

Category: Example

How big can dimensions become?

Most are small, but some (e.g., customer) can be large; ensure indexing and surrogate key design scale.

Category: Opinion

What is a junk dimension?

A consolidated dimension for miscellaneous, low-cardinality flags and indicators to simplify facts.

Category: Example

How do I validate dimension quality?

Check key uniqueness, attribute completeness, SCD correctness, and relationships to facts.

Category: Testing