Dimension Anti-Patterns

While dimensional modeling provides clear, standardized patterns for data structures, it's equally important to recognize common pitfalls. This page covers two prominent dimension anti-patterns that frequently emerge during modeling but should generally be avoided.

Abstract Generic Dimension

An abstract generic dimension combines different business entities into one dimension simply because they share a broad category. For example, a single person dimension might contain employees, customers, and vendors because they are all people.

This should generally be avoided when the entities have different attributes and business meanings.

person_key	person_type	name	hire_date	credit_score	vendor_terms	job_title
P-100	Employee	Alice	2019-03-01	NULL	NULL	Analyst
P-101	Customer	Bob	NULL	720	NULL	NULL
P-102	Vendor	Carol	NULL	NULL	Net 30	NULL

This creates many NULL values, requires person_type filters, and makes the model harder to understand. It can also lead to accidental mixing of unrelated populations.

Shared Dimensions Can Be Valid

A shared dimension is appropriate when it represents a genuine common entity with consistent meaning.

For example, a location dimension can contain:

Pincode | City | State | Country

and be referenced by Store, Warehouse, and Customer dimensions:

Store      ──┐
Warehouse  ──┼──> Location
Customer   ──┘

This is valid because City, State, and Country have the same meaning regardless of which entity references the location.

However, entity-specific attributes such as store_type, warehouse_capacity, or customer_address_type should remain in their respective dimensions.

Rule: Share a dimension when it represents a common business entity with consistent meaning. Don't combine distinct entities merely to achieve abstraction.

A shared Location dimension is therefore not the same problem as a generic Person dimension. Also, a pincode may cover multiple physical addresses, so it does not necessarily represent a unique physical location.

Measure Type Dimension

When a fact table has a long list of measures and any individual row populates only a few, it is tempting to collapse the row into a single generic measure_value column identified by a measure type dimension - the Entity-Attribute-Value pattern applied to a fact table.

Conventional (wide) design:

date_key	customer_key	sales	discount	shipping	returns	tax
20260101	C-100	100.0	5.0	NULL	NULL	NULL

Measure type design:

date_key	customer_key	measure_type_key	measure_value
20260101	C-100	MT-Sales	100.0
20260101	C-100	MT-Discount	5.0

This approach is generally not recommended. It removes the empty columns, but at three costs:

• Row multiplication. One row becomes one row per populated measure, with every key column repeated on each.
• Arithmetic across measures. sales - discount is a column subtraction in the wide design. Here it needs a self-join or a pivot, and so does every other derived metric.
• Loss of column meaning. measure_value mixes currency, counts and percentages, so SUM(measure_value) is meaningless without a measure-type filter, and additivity can no longer be reasoned about per column.

The pattern is only acceptable when the number of potential measures is extreme - hundreds - and only a handful apply to any given row. Clinical observations are the standard example: a lab catalogue holds thousands of tests, and a visit records a few.

NOTE

The argument against wide sparse tables is weaker on columnar platforms, where NULL columns compress to almost nothing and unreferenced columns are never read. The row-multiplication and arithmetic costs remain regardless.