For dimension tables, what is the generally accepted way to track changes in the tables so that I can use them for a Slowly Changing Dimension. IMO, there are a few options.
1. Use the SCD Wizard in SSIS. I've used this, and it is OK. However, I don't know that I like the idea of how it performs...just checking the values in my dimension table w/ the values from my ODS table....
2. Programmatically update a field when a record is updated so that I know to add it to my SCD logic. The pain with this is that it adds a layer of complexity somewhere, whether it be inside of a trigger or inside of program code.
3. Using replication. I've been toying with this idea, although it has its drawbacks. When a record is updated/inserted/deleted from a replicated table, replication on the subscriber (depending onhow you have it setup) runs a procedure(s) to update the record on the db to replicate the changes. I've considered adding logic to these procedures to just update a field in a table. The problem with that is what would happen if the snapshot was reinitialized. To ensure data is not lost in that case, another table would need to be on the subscriber that could keep track/reconcile these data changes.
Anyways, enough of the rambling. Id love to hear comments from others as to how they have done this, anything I may have missed, recommendations, etc.
Thanks,
Tim
Start Free Trial
