Data Warehouse Patterns - SQL Server Integration Services Design Patterns - page 325

the sales system that do not yet have the required dimension rows from the forecasting
system.
At this point, if the decision is made to handle this issue inline, there are a few dif-
ferent methodogies we can use. These are described next.
Use the Unknown Member
The fastest and simplest pattern to address the issue of missing dimension members is
to push fact records with missing dimension data into the fact table while marking that
dimension value as unknown. In this case, the fact records in question would be imme-
diately available in the data warehouse; however, all of the unknowns would, by de-
fault, be grouped together for analytical purposes. This pattern generally works best
when the fact record alone does not contain the required information to create a unique
new dimension record.
This design pattern is fleshed out in Figure 11-9 . Using the No Match output of the
Lookup transform, we're sending the fact records not matched to an existing [Item] di-
mension member to an instance of the Derived Column transform, which sets the value
of the missing dimension record to the unknown member for that dimension (in most
cases, represented by a key value of -1). The matched and unmatched records are then
brought back together using the Union All transform.
Figure 11-9 . Using Unknown Member for missing dimension member