Previous | Page 4 of 4 | Next

OCDM: Hierarchical Changes

The PKG_OCDM_GEO_FLEX_TO_NAME package contains two procedures which are used for both hierarchy creation as well as for hierarchy maintenance (modifications to structure and/or data). They allow to convert a Flexible Hierarchy to a Named Hierarchy (Snowflake):

• create_snf_tables used to create named hierarchy (snowflake) tables:
  • Drops/creates named hierarchy tables making up the snowflake dimension hierarchies
  • Creates the tables from top to the bottom level for each hierarchy linking each level with its parent level
  • Needs to be called when there is a structural change to any hierarchy/level (e.g.: new hierarchy / new level / new level attribute etc.)
  • Named Hierarchy (Snowflake) table contains:
    • Constraints for FKs between levels of hierarchy
    • Constraints required for SCD handling
    • Constraints enforcing data integrity relating to Surrogate Key / Natural Key columns etc.
• poplt_snf_tables used to populate named hierarchy (snowflake) tables:
  • Populates the named hierarchy tables (created by create_snf_tables) with dimensional data present as metadata in the flexible hierarchy tables
  • Truncates and loads the named hierarchy tables to keep the named hierarchy tables in sync with the data (metadata) present in the flexible hierarchy tables.
  • Validates the flex hierarchy data performing data integrity checks:
    • Skip Node check: to identify unbalanced hierarchy
    • Hanging Node check: to identify nodes without children at higher levels of hierarchy
    • Issues appropriate Error/Warning messages

Previous | Page 4 of 4 | Next