4.2.1 Overview of Hierarchy Flattening Process

The Hierarchies are maintained in the Dimension Management component of OFSAA Infrastructure. (In the Common Object Maintenance menu, navigate to Master Maintenance and select Dimension Management, and then select Hierarchies).

The Hierarchy Flattening process is used to move hierarchy data from the parent-child storage data structure to a level based storage data structure. In the Hierarchy Dimension Population Management model, hierarchy data for any hierarchies created on seeded or user-defined dimensions is stored within dimension specific hierarchy tables for the respective dimensions. The Hierarchy Flattening process copies this data to the REV_HIER_FLATTENED table in the BI data model after flattering is completed.

For example:

The hierarchy data of one or more Product Hierarchies created on the Product dimension (a seeded dimension) is stored in the DIM_PRODUCTS_HIER table.

Similarly, assuming there is a user-defined dimension (for example, Legal Entity) and a hierarchy has been defined on this dimension, then the hierarchy data is stored in the DIM_LE_HIER table (assuming this is the hierarchy table created for this hierarchy).

The hierarchy data in the preceding example is moved to REV_HIER_FLATTENED in the BI data model by the hierarchy flattening process.

Database components used by this transformation are:

• REV_BATCHHIERFLATTEN – Oracle database function.
• REV_HIER_TRANSFORMATON_BIAPPS – Oracle database Package called by the preceding function.

  Some of the features of the Hierarchy Flattening process are:

• You have the choice to process a single hierarchy or all hierarchies belonging to a particular dimension as part of a single execution.
• Any change made to the hierarchy using the Hierarchy Management maintenance window changes the flagFLATTENED_ROWS_COMPLETION_CODE in REV_HIER_DEFINITIONS to Pending. This improves processing efficiency as the Flattening process will avoid hierarchies that have not been modified.