1. User Guide
  2. Dimension Loading Process
  3. Hierarchy Transformation

4.1 Hierarchy Transformation

Hierarchy Flattening Transformation is used to move the hierarchy data from the parent-child storage structure in OFS PFT AMHM model to a level based storage structure in OFS EFPA. In the OFS PFT AMHM model, hierarchy data for any hierarchy created on seeded or user-defined dimensions using the AMHM is stored within hierarchy tables of respective dimensions. These are moved to the REV_HIER_FLATTENED table of OFS EFPA after flattening by the Hierarchy flattening process.

For example, the hierarchy data of one or more Product Hierarchies created on the Product dimension (a seeded dimension) are stored in the DIM_PRODUCTS_HIER table.

The hierarchy data in the preceding example would be moved to REV_HIER_FLATTENED in the OFS EFPA 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 Transformation are:

  • The user has the choice to process a single hierarchy or all hierarchies belonging to a particular dimension as part of a single execution.
  • Any changes made in the hierarchy using the AMHM Hierarchy Maintenance screen will change the FLATTENED_ROWS_COMPLETION_CODE flag in REV_HIER_DEFINITIONS table to 'Pending'. This improves the processing efficiency since the Transformation process will avoid hierarchies that have not been modified.