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

It is important to note that this design pattern should also include a supplemental
process (possibly consisting of just a simple SQL statement) to periodically attempt to
match these modified facts with their proper dimension records. This follow-up step is
required to prevent the fact data in question from being permanently linked to the un-
known member for that dimension.
Add the Missing Dimension Member
Using this design pattern, you can add missing dimension records on the fly as part of
the fact package using as much dimension data as is provided by the fact data. In this
scenario, the fact records are immediately available in the data warehouse, just like
they were for the previous design pattern, but this methodology has the added benefit
of matching the fact record to its proper dimension member. In most cases, this allows
you to immediately associate the correct dimension data rather than group the un-
matched data into the unknown member bucket.
Like the previous pattern, this method does come with a couple of caveats. First of
all, the fact record must contain all of the information to 1) satisfy the table constraints
(such as NOT NULL restrictions) on the dimension table, and 2) create a uniquely iden-
tifiable dimension row using the business key column(s). Also, since we're deriving
the newly added dimension member from the incoming fact records, in most cases,mit
can be reasonably assumed that the incoming fact data will not completely describe the
dimension member. For that reason, you should also complement this design pattern
with a process that attempts to fill in the missing dimension elements (which may
already be addressed as part of a comprehensive slowly changing dimension strategy).
As shown in Figure 11-10 , here we use a methodology similar to the previous ex-
ample. However, instead of simply assigning the value of the unknown member using
the Derived Column transform, we leverage an instance of the OLE DB Command
transform to insert the data for the missing dimension record in the fact table into the
dimension table. The SQL statement is shown in the callout, and in the properties of
the OLE DB command, we map the placeholders (indicated by question marks) to the
appropriate values from the fact record.