Slowly changing dimensions or avoid that problem?
Posted by thomasivarssonmalmo on April 24, 2007
- Type one: No history, overwrite all attributes in the dimension, except the technical key, with the latest changes each time you update your data warehouse.
- Type two: For attributes/columns that are important for keeping historical changes you simply add a new version of an existing customer or product. The old version and the new version have time stamps that tells if it valid or not.
- Type three: Add a new attribute/column for an attribute that is important for historical changes.
Why is history important? If you have a key account or salesperson that change district that person/key account will also change sales history for districts. Salespeople and districts are in the same dimension. Sales can go up or down for districts depending on that salespeople change district. With type one you will face this problem but with type two you will add a new version of the same salesperson with a new technical key (surrogate key) and time stamps that tell when this combination of salesperson and district was relevant.
The problem with type two is that you data and cubes will be fragmented over time and it will be harder to follow each salespersons performance.
Is there a better approach? For history,yes. Simply never have attributes/columns in the same dimension if there is a dynamic relationsship over time between the attributes. Make a Salesperson or key account separate dimensions and also one separate district dimension. Let them have their own foreign keys with the fact table. The downside of this approach is that you will not to be able to drill-down from district to salesperson. And you will not have autoexist between the attributes in the same dimension(Check Books On Line about autoexist).
SSIS have a SCD(slowly changing dimension wizard) that can be interesting to test but avoid it in production. It is as bad as the old data driven query task in DTS2000 because it use row by row inserts. This means that it is slow and useless except for education.