Handling Missing Dimension Keys

Dimensions should typically include an "Unknown" or "Not Available" member for facts whose dimensional key cannot be resolved, such as when source data is missing or unmatched, or when a dimension record has not yet arrived. This is important because storing a NULL foreign key in the fact table means the fact will not match the dimension in a standard equality join, potentially causing it to be silently excluded from reports.

The warehouse should standardize the surrogate keys used for these default members, rather than allowing different conventions across dimensions. Negative integers such as -1 for "Unknown" and -2 for "Not Available" are commonly used because they cannot collide with a standard positive-integer surrogate-key sequence.

The date dimension is a special case. Since its key is typically a meaningful value such as YYYYMMDD, the warehouse cannot simply use the same negative surrogate-key convention. Instead, it should include a designated sentinel date to represent an unknown, unavailable, or not-yet-determined date, such as 19000101.

Avoid NULL Attributes

NULLs in dimension attributes must also be avoided because different databases handle grouping and constraining on NULLs inconsistently. Instead, substitute NULLs with descriptive strings like "Not Available" or "Unknown".