5.1.6 Executing the Dimension Load Procedure

You can execute this procedure either from SQL*Plus or from within a PL/SQL block or from the Batch Maintenance window within OFSAAI framework.

1. To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner. The function requires four parameters: Batch Run Identifier, As of Date, Dimension Identifier, Synchronize flag (Optional).
2. The syntax for calling the procedure is:
   function fn_drmDataLoader(batch_run_id varchar2, as_of_date varchar2, pDimensionId varchar2, pSynchFlag char default 'Y', force_member_delete char default 'N')
   where BATCH_RUN_ID is any string to identify the executed batch.
   AS_OF_DATE in the format YYYYMMDD.
   pDIMENSIONID dimension id.
   pSynchFlag this parameter is used to identify if a complete synchronization of data between staging and dimension table is required. The default value is 'Y'.

   Note:

   With Synch flag N, data is moved from Stage to Dimension tables. Here, an appending process happens. You can provide a combination of new Dimension records plus the data that has undergone change. New records are inserted and the changed data is updated into the Dimension table. With Synch flag Y, the Stage table data will completely replace the Dimension table data. There are a couple of checks in place to ensure that stage_dimension_loader is equipped with similar validations that the UI provides. The Data Loader does a Dependencies Check before a member is deleted. The validation checks, if there are members used in the Hierarchy that are not present in the DIM_< DIM >_B table. This is similar to the process of trying to delete a member from the UI, which is being used in the Hierarchy definition. You are expected to remove or delete such Hierarchies from the UI before deleting a member.
   For Example:
   Declare num number;Begin num := fn_drmDataLoader ('INFODOM_20100405','20100405' ,1,'Y','N');End;
3. To execute the procedure from the OFSAAI Batch Maintenance, create a new Batch with the Task as TRANSFORM DATA and specify the following parameters for the task:
   Datastore Type: Select appropriate datastore from list
   Datastore Name: Select appropriate name from the list
   IP address: Select the IP address from the list
   Rule Name: fn_drmDataLoader
   Parameter List: Dimension ID, Synchronize Flag
   The fn_drmdataloader function calls STG_DIMENSION_LOADER package which loads data from the stg_<dimension>_hier_intf to the dim_<dimension>_hier table.
   From Release 8.0, RUNIT.sh utility is available to resave the UMM Hierarchy Objects. The data for AMHM hierarchies which is stored in dim_<dimension>_hier table is changed due to the fn_drmdataloader function, so the RUNIT.sh utility is executed to refresh the UMM hierarchies which have been implicitly created due to the AMHM hierarchies. This file resides under ficdb/bin area.
   To run the utility directly from the console:
   Navigate to $FIC_DB_HOME/bin of OFSAAI FIC DB tier to execute RUNIT.sh file
   The following parameter needs to be provided:
   INFODOM- Specify the information domain name whose hierarchies are to be refreshed. This is the first parameter and mandatory parameter
   USERID- specify the AAI user id who is performing this activity. This is second parameter and mandatory as well
   HIERARCHY- specify the hierarchy code to be refreshed. In case multiple hierarchies need to be refreshed the same can be provided and tilde (~) separated values. This is third parameter and non-mandatory parameter
   For example: ./RUNIT.sh,<INFODOM>,<USERID>,<CODE1~CODE2~CODE3>

   Note:

   In case the third parameter is not specified, then all the hierarchies present in the infodom will be refreshed.
   To run the utility through the Operations module:
   Navigate to the Operations module and define a batch.
   Add a task by selecting the component as RUN EXECUTABLE.
   Under Dynamic Parameter List panel, specify ./RUNIT.sh,<INFODOM>,<USERID>,<CODE1~CODE2~CODE3> in the Executable field.
   After saving the Batch Definition, execute the batch to resave the UMM Hierarchy Objects