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.