Dimension Loader - drmDataLoader Updates Last Modified Date for All Dimension Members
(Doc ID 2091455.1)
Last updated on MAY 24, 2023
Applies to:
Oracle Financial Services Analytical Applications Infrastructure - Version 7.3.5.1.0 and laterInformation in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Analytical Applications (OFSAA)
Slowly Changing Dimensions (SCD)
Oracle Financial Services Profitability Management (PFT)
Oracle Financial Services Funds Transfer Pricing (FTP)
Oracle Financial Services Asset Liability Management (ALM)
Oracle Business Intelligence Enterprise Edition (OBIEE)
Oracle Financial Services Asset Liability Management Analytics (ALMBI)
Oracle Financial Services Enterprise Financial Performance Analytics (EFPA/PFTBI) previously known as Oracle Financial Services Profitability Analytics
Reporting
Oracle Financial Services Enterprise Performance Management (EPM)
Symptoms
On OFSAAI 7.3.5.1.0 / 6.1.0.3 when running the drmDataLoader with pSynchFlag = 'Y', the LAST_MODIFIED_DATE field on all the DIM_<dim_name>_B, DIM_<dim_name>_TL,DIM_<dim_name>_HIER) tables are updated even for dimension members where there is no change. Expect that the date field should not be updated if there is no change in the dimension member.
Standard dimension process Flow -
1. Staging 4 tables (STG_<dim_name>_B_INTF, STG_<dim_name>_TL_INTF, STG_<dim_name>_ATTR_INTF, STG_<dim_name>_HIER_INTF) to Processing 4 tables (DIM_<dim_name>_B, DIM_<dim_name>_TL, DIM_<dim_name>_ATTR, DIM_<dim_name>_HIER) using the Dimension Loader Process
2. Hierarchy Flattening Process
3. SCD Process from the view to DIM_<dim_name> Results table
The issue is with LAST_MODIFIED_DATE field in the processing dimension tables. The dimension loader process is updating this field for all the runs even when there is no change in the data. This field is updated with system date. This impacts the SCD process (step 3 above), since this field D_LAST_MODIFIED_DATE is present in the result table and configured as COL_TYP=DA and Type 2 SCD in SYS_STG_JOIN_MASTER table. Due to this, for every run, the members are getting closed logically and new records are opened from the SCD even though there is absolutely no change in the data.
EXPECTED BEHAVIOR
Expect the LAST_MODIFIED_DATE to remain the same for dimension members which are not changed.
The issue can be reproduced at will with the following steps:
1. Load all dimension members to Staging Table.
2. Run fn_drmDataLoader with pSynchFlag char default 'Y' for one month.
3. Rerun fn_drmDataLoader with pSynchFlag char default 'Y' for second month with same members unchanged.
4. Find LAST_MODIFIED_DATE is updated for all members.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |