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".
Late Arriving Dimensions
Sometimes a fact arrives before the corresponding dimension data is available.
For example, an online order may arrive in the data warehouse before the customer's details have been received from the customer system. The order still needs to be loaded immediately, even though the customer cannot yet be identified.
In this situation, a placeholder dimension row is created using the available natural key, while the other descriptive attributes are set to generic values such as "Unknown."
For example:
| Customer_Key | Customer_ID | Customer_Name | City |
|---|---|---|---|
| 9999 | C12345 | Unknown | Unknown |
The order fact can then reference this placeholder customer:
| Order_ID | Customer_Key | Order_Amount |
|---|---|---|
| O1001 | 9999 | ₹2,500 |
When the customer's actual information arrives later, the placeholder dimension row is updated with the correct attributes using a Type 1 overwrite. The existing fact row does not need to be changed because it already points to the correct customer dimension row.
NOTE
Late-arriving dimension data can also occur when a historical Type 2 change is discovered after the related facts have already been loaded. In this case, a new Type 2 dimension row must be inserted with the correct effective dates, and the affected fact rows may need to be restated to reference the correct historical dimension version.