SSIS SCD Wizard Disadvantages
The SCD Wizard is definitely a step in the right direction. It makes handling complex SCD processing relatively easy and quick – at least when compared with constructing your own equivalent logic using Lookups, Conditional Splits, and other components. However, after using it for one or two projects, you’ll probably run in to one or more of these issues – I’ll only briefly describe them, because if you’ve used it, you probably know them better than I do:
- Re-running the Wizard in order to alter the behaviour of the SCD processing completely destroys that section of the Data Flow.
- It’s slooow on larger dimensions, especially when used in a scale-out/remote scenario (where the IS server and DBMS containing the dimension are on different boxes) due to the Wizard doing an uncached RBAR round-trip lookup for every row of the source.
- Column value comparison is always case-sensitive, trailing-space-sensitive, and culture-sensitive.
- No explanation is given for why certain rows get sent to certain outputs in order to debug data. Items shown as “new” or “changed” may be so due to trailing spaces, case, collation, implicit data type conversion issues on any one of the keys or attributes – but none of that is fed back to you.
- Only OLE DB Connection Managers are accepted to reference the dimension table – and only some OLE DB providers are permitted.
- The “current row” must always be identifiable by keeping the “expiry” date NULL – or by manually altering the components created by the Wizard, which get destroyed if the Wizard is re-run. A better practice is to put far-future dates in the expiry column, leading to easier queries. (See The Microsoft Data Warehouse Toolkit p241 - Mundy, Thornthwaite, Kimball.)
- Not possible to “retire” dimension members (rows), due to the “Change Stream” architecture.
- No support for managing (generating) surrogate keys.
- No support for “special” members – such as the “unknown” member – per Kimball Method best practices.
- No support for auditing changes is included.
Take advantage of the improvements over the SCD Wizard
present in the Kimball Method SCD component.