36.4 Executing the Time Dimension Population Transformation

You can execute the function from the Operations (formerly Information Command Center (ICC) framework) module of OFSAAI.

This component for OFSDF 8.1.2.0.0 has been seeded with the Batch ID FSDFINFO_DATA_FOUNDATION_SCD, which can be executed from Batch Execution section of OFSAAI. In the Parameter List, enter the Start Date and End Date. For example: '19940101', '19941231'.

You can load DIM_DATES for a fiscal year for ONE jurisdiction at a time. However, if the dates are populating incorrectly for the selected Jurisdiction, you should revisit the values entered in the DIM_FINANCIAL_YEARS TABLE and then repopulate the DIM_DATES.

You can also define a new Batch and an underlying Task definition from the Batch Maintenance window of OFSAAI. For more information on defining a new Batch, refer to section How to Define a Batch.

To define a new task for a Batch definition:

1. Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window.
2. Click Add (+) button from the Task Details grid. The Task Definition window is displayed.
3. Enter the Task ID and Description.
4. Select 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 fn_DimDates from the drop down list of available transformations. (This is a seeded Data Transformation which is installed as part of the OFSDF solution installer. If you do not see this in the list, contact My Oracle Support)
   • Parameter List – Enter the Start Date and End Date.
     • Start Date – This is the starting date, from which the Transformation will populate DIM_DATES table. This date should be specified in 'YYYYMMDD' format.

       For example, '20081131'.

     • End Date - This is the end date, to which the Transformation will populate DIM_DATES table. This date should also be specified in 'YYYYMMDD' format.

       For example, '20091231'.

6. Click Save. The Task definition is saved for the selected Batch.
7. Execute the batch.

You can execute a Batch definition from the Batch Execution section of OFSAAI Operations module. The function can also be executed directly on the database through SQLPLUS Details are:

Function Name: FN_DIM_DATES

Parameters: P_BATCH_RUN_ID, P_AS_OF_DATE, P_ST_DT, and P_ED_DT Sample Parameter Values: 'Batch1', '20091231', '20081131', and '20091231'

Note:

Execute this DT for each year for which data is present in the source table.