4.4 Parent-Child Hierarchy

OBIEE can handle Hierarchies in Parent-Child format as well. For this, the Level based Hierarchy of AMHM has to be converted to a Parent-Child Hierarchy to support Hierarchies in reports. Parent-Child hierarchy is applicable for:

• Reporting Line (DIM_REP_LINE)
• General Ledger (DIM_GL_ACCOUNT)
• Organization Structure (DIM_ORG_STRUCTURE)

After the above dimension tables are loaded, their respective Parent-Child Relation tables also must be populated. The following are the dimension tables with their respective Parent-Child Relation tables:

• DIM_REP_LINE: REP_LINE_PARENT_CHILD_RELATION
• DIM_GL_ACCOUNT: GL_PARENT_CHILD_RELATION
• DIM_ORG_STRUCTURE: ORG_STR_PARENT_CHILD_RELATION

The following DTs populate the Parent-Child Relation tables:

1. FN_REP_LINE_PARENT_CHILD

   The database components used to populate REP_LINE_PARENT_CHILD_RELATION are:

   • Database function FN_REP_LINE_PARENT_CHILDWR
   • Database function FN_REP_LINE_PARENT_CHILD, which is called by the function FN_REP_LINE_PARENT_CHILDWR mentioned above.

   Process PR_DIM_REP_LINE triggers the DT and loads the REP_LINE_PARENT_CHILD_RELATION table.

   Parameter List: Hierarchy Name and Folder Name

   Example: 'Repline Hierarchy','EPM61SEG'

   The following query retrieves the Hierarchy Name and the Folder Name:

   select distinct FODTL.SHORT_DESC, FODB.FOLDER_NAME
   from fsi_m_object_definition_tl FODTL, fsi_m_object_definition_bFODB
   where FODTL.object_definition_id in
   (select object_definition_id
   from fsi_m_object_definition_b
   where table_name = 'DIM_REPORTING_LINE_HIER')
   and FOLDER_NAME = '<OFSAA segment name>';

   There is a foreign key (FK) reference from table FCT_MGMT_REPORTING (N_REP_LINE_CD column) to table DIM_REP_LINE (N_REP_LINE_CD column). This foreign key, FK_FCT_MGMT_REPORTING_3 should be disabled if the Data Transformation (DT) FN_REP_LINE_PARENT_CHILD has to run again, as this DT deletes the DIM_REP_LINE table and reloads again. You can enable the FK after the successful execution of the DT.

2. FN_GL_PARENT_CHILD

   The database components used to populate GL_PARENT_CHILD_RELATION are:

   • Database function FN_GL_PARENT_CHILDWR
   • Database function FN_GL_PARENT_CHILD, which is called by the function FN_GL_PARENT_CHILDWR mentioned above.

   Process PR_DIM_GL_ACCOUNT triggers the DT and loads the GL_PARENT_CHILD_RELATION table.

   Parameter List: NULL

3. FN_ORG_PARENT_CHILD

   The database components used to populate ORG_STR_PARENT_CHILD_RELATION are:

   • Database function FN_ORG_PARENT_CHILDWR
   • Database function FN_ORG_PARENT_CHILD, which is called by the function FN_ORG_PARENT_CHILDWR mentioned above.

   Process PR_DIM_ORG_UNIT triggers the DT and loads the ORG_STR_PARENT_CHILD_RELATION table.

   Parameter List: NULL

   Note:

   Whenever there are changes to any of the three dimensions mentioned above, the corresponding Parent-Child relation DT should be executed. The OBIEE reports and the Parent-Child relation tables require the Parent ID to be null for the root node of the respective Hierarchy in the corresponding dimension table.