This section provides information about populating Time Dimension Transformation in the Data Foundation application and step-by-step instructions to use this section.
Business data commonly represents information as of a point in time (for example, a balance as of a point in time) or as of a particular span of time (for example, income for the month of March). The rollup of a particular balance depending on their nature could be a simple additive rollup wherein the child member balances are added up to arrive at the parent node balance (for example, Ending Balance) or non-additive rollups wherein a node formula are used to specify how to roll up the child member balances (for example, 3 months rolling average).
Topics:
· Overview of Time Dimension Population
· Tables Used by the Time Dimension Population Transformation
· Executing the Time Dimension Population Transformation
· Checking the Execution Status
The Time dimension population transformation is used to populate the DIM_DATES table with values between two dates specified by the user.
The database components, used by the transformations are:
1. Database function FN_DIM_DATES
2. Database procedure PROC_DIM_DATES_POPULATION that is called by the function FN_DIM_DATES mentioned earlier.
The following are the prerequisites for Time dimension population.
1. All the post install steps mentioned in the Oracle Financial Services Advanced Analytical Applications Infrastructure Installation Guide Release 8.1.1.0.0 and Oracle Insurance Data Foundation Application Pack Installation and Configuration Guide Release 8.1.1.0.0 must be completed successfully.
2. Application User must be mapped to a role that has seeded batch execution function (BATPRO).
3. Before executing a Batch, check if the following services are running on the application server:
§ Iccserver
§ Router
§ AM Server
§ Message Server
§ OLAP Server
4. For more information on how to check if the services are up and on and how to start the services if you find them not running, see the Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.1.0.0.
5. Create batches to execute the function. For more details, refer to section How to Define a Batch.
For more details on viewing the structure of earlier tables, see the Oracle Financial Services Analytical Applications (OFSAA) Data Model Document Generation Release 8.1.x or the OIDF Data Model.
You can execute the function from the Operations (formerly Information Command Center (ICC) framework) module of OFSAAI.
This component for OIDF has been seeded with the Batch ID <INFODOM>_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.
NOTE:
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 page of OFSAAI. For more information on defining a new Batch, see the How to Define a Batch section.
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 page.
2. Click Add (+) button from the Task Details grid. The Task Definition page 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 OIDF 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.
To check the SCD batch execution status of Time Dimension Transformation, follow the procedure Check the Execution Status of the SCD Batch.
To verify log files, and check the error messages (if any), follow the procedure Verify Log Files and Check Error Messages.