OCDM: Hierarchical Changes
Slow Changing Dimension (Type I or II) is dealt with following the best practice standards in Data Warehousing. The goal here is to change the name “UNITED STATE OF AMERICAS” to “United States”.
Legend:
- Green nodes are existing (unchanged) nodes.
- Red nodes are nodes which are inactive, closed (using Effective To Date).
- Dark Blue nodes are newly created open nodes.
- Blue nodes are modified (changed) nodes.
The Attribute/Hierarchy change data routines SCD2 change mode, track changes and matain history
- Only the old Node USA/”UNITED …” is closed (not descendants as well)
- New Node USA/”United States” is opened at CNTRY level.
- Child nodes of earlier node USA/”UNITED …” are updated/“moved” to the new parent node USA/”United States” effectively moving all other existing descendants under new node.
Advantages:
- Data explosion avoided as a result of attribute changes and/or hierarchical changes (even) at higher levels of hierarchy.
- Each child level table contains the Natural Key of parent as well as the Surrogate Key of the parent level making it possible to report against a Point-in-Time (As Was Reporting) state of the hierarchy.
Disadvantage: By default, higher levels of hierarchy are presented/stated through the latest/most-current state of hierarchy (As Is Reporting).