4.5 Multiple Hierarchies

The current flow of hierarchy data movement from AMHM tables to Reporting dimension tables considers the following objects:

• Four AMHM tables (<Dimension>_B/TL/ATTR/HIER)
• REV_HIER_FLATTENED table
• SETUP_MASTER table
• Dimension specific view
• SCD to load data from View to Reporting Dimension table

The out-of-the-box (OOTB) product has been configured to support data movement of not only one hierarchy but also to support more than one hierarchy. The information mentioned specifies the points at a high-level that one has to consider to support more than one hierarchy.

The following steps describe the data movement in two hierarchies (Product dimension is considered as an example):

1. Create two hierarchies for Product dimension in AMHM (For example 200183, 301741).
2. Run Hierarchy Flattening transformation to load the flattened structure of both in REV_HIER_FLATTENED.
3. Load SETUP_MASTER table with two entries identified by V_COMPONENT_DESC values PRODUCT_HIER1 and PRODUCT_HIER2.

   Table 4-10 SETUP_MASTER

   V_COMPONENT_CODE	V_COMPONENT_DESC	V_COMPONENT_VALUE
   PRODUCT_HEIR	PRODUCT_HEIR1	301741
   PRODUCT_HIER2	PRODUCT_HIER2	200183

4. Modify the view definition DIM_PRODUCT_V to include both hierarchies. Find below both the OOTB and the modified view definitions.
   • DIM_PRODUCT_V
   • DIM_PRODUCT__MULTI_HIER_V

   In both the hierarchies, level 10 to level 1 represent the first hierarchy and level 20 to level 11 represent the second hierarchy. Both the hierarchies share leaf nodes.

   Use the DIM_PRODUCT__MULTI_HIER_V.sql, which is a sample view on the product dimension. It can be used in the SCD process to move multiple alternate hierarchies defined in the AMHM to the flattened tables used for BI analytics.

   This sample view considers two alternate hierarchies. You can extend this for additional hierarchies that may be required. Also, the sample is based on the Product dimension but can be modified for other dimensions by replacing the relevant tables.