Slowly Changing Dimension Alternative to SSIS Wizard
Hello, I am looking for a solution for processing slowly changing dimensions (types 1 & 2) WITHOUT using the SSIS transformation wizard. The wizard peforms so poorly that I often wonder if anyone ever really uses it on large datasets. I've searched for alternatives and found several. However, I'd like to solicit opinions from the collective braintrust here as to what most folks think works best. Regards, Tom
Currently, I have, as an example, a customer table. Using the SCD transformation wizard, my business keys are region and customer ID. The bulk of the remaining fields are are type 2 updates. We use row effective date, row end date, and row update date to mark the changes. The historical fields are address, city, state, customer terms, credit limit, DBA Name (our only type1 update). The primary key on the table is CustomerKey INT IDENTITY NOT NULL. The wizard gets quite bogged down as soon as it tries to digest over 20,000 rows. Non-clustered indexing helps, but it's still a dog.