This chapter provides information about Time Dimension Population in the Oracle Financial Services 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 is used to specify how to rollup the child member balances (for example, 3 month 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:
· Database function FN_DIM_DATES
· 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.0.0.0 and Oracle Financial Services Data Foundation Application Pack Installation and Configuration Guide Release 8.1.0.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.0.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 OFSDF Data Model.
You can execute the function from the Operations (formerly Information Command Center (ICC) framework) module of OFSAAI.
This component for OFSDF 8.1.0.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'.
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 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:
6. Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window.
7. Click Add (+) button from the Task Details grid. The Task Definition window is displayed.
8. Enter the Task ID and Description.
9. Select Transform Data component from the drop down list.
10. 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'.
11. Click Save. The Task definition is saved for the selected Batch.
12. 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. |
The Batch execution status can be monitored through Batch Monitor section of OFSAAI Operations module. The status messages in batch monitor are:
· N - Not Started
· O - On Going
· F - Failure
· S – Success
Access the execution log on the application server in the following directory:
ftpshare/logs/<Run_Date>/FSDFINFO/LOAD DATA
The file name contains the batch execution ID. The error log table in the atomic schema is as follows:
You can access the database level operations log by querying the FSI_MESSAGE_LOG table. Filter the Batch Run ID column for identifying the relevant log.
NOTE |
Check the .profile file in the installation home if you are unable to find the above mentioned path. |