4.1.3 Execute the Hierarchy Flattening Transformation

You can execute the Data Transformation from the Operations module of OFSAAI.

The Hierarchy Flattening Transformation for OFS EFPA is seeded with the following processes:

• PR_DIM_PRODUCT_HIER
• PR_DIM_ORG_UNIT_HIER
• PR_DIM_COMMON_COA_HIER
• PR_DIM_GL_ACCOUNT_HIER

You can define a new RUN out of these processes and enter Dimension ID and Hierarchy ID in Parameter List.

To define a new task for a Batch definition, follow these steps:

1. Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window.
2. Click the Add icon (+) from the Task Details grid.

   The Task Definition window is displayed.

3. Enter the Task ID and Description.
4. Select the TRANSFORM DATA component from the drop-down list.
5. Select the following from the Dynamic Parameters list:
   • Datastore Type: Select the appropriate datastore type from the list.
   • Datastore Name: Select the appropriate datastore name from the list.
   • IP address: Select the IP address from the list.
   • Rule Name: Select BATCH_HIERTRANSFORMATION from the drop-down list of available transformations. (This is a seeded Data Transformation which is installed as part of the OFS EFPA application Installer. If you don't see this in the list, contact Oracle Support.)
   • Parameter List: Enter Dimension ID and Hierarchy ID.
     For the Parameter List, the values are:

     • Dimension ID: Execute the following query in the database to find the value, and use the value in the Dimension ID column for the dimension name/description to be processed.

       Select b.dimension_id,t.dimension_name, and t.description from rev_dimensions_b b inner join rev_dimensions_tl t on b.dimension_id = t.dimension_id and t.dimension_name like '<dimension name>'

       Replace <dimension name> in the preceding query with the Dimension Name you find in the UI (Financial Services Application > Master Maintenance > Dimension Management) for the dimension on which the Hierarchy you want to flatten.

     • Hierarchy ID: If all the hierarchies belonging to a dimension are to be processed, then provide null as the parameter value. Else, provide the System Identifier of the hierarchy that needs to be transformed. Execute the following query in the database, only if a single hierarchy has to be processed, and use the value in HIERARCHY_ID column as a parameter for the hierarchy to be processed.

       select b.object_definition_id,short_desc,long_desc from fsi_m_object_definition_b b inner join fsi_m_object_definition_tl t on b.object_definition_id = t.object_definition_id and b.id_type = 5

       For OFS EFPA, it is assumed that only one Hierarchy is processed at a time.

       Example: If all the hierarchies for GL Account dimension must be processed, the parameter list should be given as: '2', null

       Where '2' is the Dimension ID for the seeded dimension GL Account.

       Example: If a particular hierarchy with code 1000018112 must be processed, the parameter list should be given as follows: '2', '1000018112'

       Where '1000018112' is the code obtained by executing the preceding query in the database.

6. Click Save.

   The Task definition is saved on the selected Batch.

   You can execute the batch from the Batch Execution window by choosing the Batch created following the steps mentioned in the preceding sections. For more details, see the OFS Analytical Applications Infrastructure User Guide.

Hierarchy Transformation can also be executed directly on the database through SQLPLUS. The details are:

• Function Name: REV_BATCHHIERFLATTEN
• Parameters: BATCH_RUN_ID, MIS_DATE, PDIMENSIONID, and PHIERARCHYID
• Sample Parameter Values: 'Batch1', '20091231', '2', '1000018112'

Note:

Execute the Hierarchy Transformation Batch only when a new Hierarchy is defined or an existing Hierarchy is modified.