Measure Type Dimensions

Sometimes when a fact table has a long list of facts that is sparsely populated in any individual row, it is tempting to create a measure type dimension that collapses the fact table row down to a single generic fact identified by the measure type dimension (an Entity-Attribute-Value pattern).

We generally do not recommend this approach.

Although it removes all the empty fact columns, it multiplies the size of the fact table by the average number of occupied columns in each row, and it makes intra-column computations much more difficult. This technique is acceptable when the number of potential facts is extreme (in the hundreds), but less than a handful would be applicable to any given fact table row.

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

WARNING

While this eliminates NULL columns, calculating Sales - Discount now requires complex pivoting or self-joins instead of a simple column subtraction.