Slowly Changing Dimensions - SQL Server Integration Services Design Patterns - page 349

less you are dealing with a very active dimension, most SCD process-
ing batches will contain a small number of rows.
Large dimension: You are working against large dimensions but are
only processing a small number of change rows. You will want to
avoid operations that cause full table scans of your dimension.
Because of these target scenarios, the SCD transform does not cache the existing
dimension data (like a Lookup transform does) and performs all of its comparisons row
by row against the destination table. Although this allows the transform to avoid full
scans of the destination dimension and reduces memory usage, it does affect perform-
ance when you are processing a large number of rows. If your scenario does not match
one of thsee, you might want to consider using one of the other patterns in this chapter.
If it does match, or if you prefer using out-of-the-box components over third-party
solutions (or you would just like to avoid hand-crafted SQL statements required for the
Merge pattern), consider applying the optimizations listed at the end of this pattern.
Running the Wizard
Unlike other SSIS data flow components, when you drop the SCD transform onto the
design surface in Business Intelligence Development Studio (BIDS), a wizard pops up
and walks you through the steps of setting up your SCD processing.
The first page of the wizard ( Figure 13-1 ) allows you to select the dimension you'll
be updating and the column or columns that make up the business key (also known as
the natural key).