3.3.3 Create the Business Unit Hierarchy

If any Level of Aggregation (LOA) is defined by enabling the Business Unit dimension, then it is mandatory to create a Business Unit Hierarchy where all the business units that are selected in the LOAs are at the leaf level. In DimensionManagement, in the HierarchyMaintenance section, create a hierarchy for the Business Unit Dimension. For more information on creating a hierarchy, see the HierarchyMaintenance section in the Oracle Financial Services Analytical Applications Infrastructure User Guide.

After the hierarchy is created, the below SQL statement must be executed in the atomic schema:

INSERT INTO FSI_M_IIA_AGGR_DIMENSION_DTLS (LEVEL_OF_AGGR_ID, DIMENSION_ID, HIERARCHY_ID, DIM_MEMBER_ID)

SELECT DIM_MAP.LEVEL_OF_AGGR_ID, DIM_MAP.DIMENSION_ID, DIM_MAP.HIERARCHY_ID, DIM_MAP.DIM_MEMBER_ID

FROM ( SELECT DISTINCT DM.LEVEL_OF_AGGR_ID, (SELECT DIMENSION_ID FROM REV_DIMENSIONS_B WHERE MEMBER_B_TABLE_NAME = 'DIM_BUSINESS_UNIT_B' AND MEMBER_COL='BUSINESS_UNIT_ID') DIMENSION_ID, &HIERARCHY_ID HIERARCHY_ID, DBUB.BUSINESS_UNIT_ID DIM_MEMBER_ID

FROM FSI_IIA_AGGR_NON_HIER_DIM_MAP DM

INNER JOIN DIM_BUSINESS_UNIT DBU ON DBU.N_BUSINESS_UNIT_SKEY = DM.DIM_CD AND DM.DYNA_NAME = 'BIUT'

INNER JOIN DIM_BUSINESS_UNIT_B DBUB ON DBUB.BUSINESS_UNIT_CODE = DBU.V_BUSINESS_UNIT_CODE) DIM_MAP

WHERE NOT EXISTS (SELECT IAD.LEVEL_OF_AGGR_ID FROM FSI_M_IIA_AGGR_DIMENSION_DTLS IAD WHERE IAD.LEVEL_OF_AGGR_ID = DIM_MAP.LEVEL_OF_AGGR_ID AND IAD.DIMENSION_ID = DIM_MAP.DIMENSION_ID AND IAD.DIM_MEMBER_ID = DIM_MAP.DIM_MEMBER_ID)

/

When prompted for the HIERARCHY_ID value, enter the ID of the Business Unit Hierarchy.