Dimension Loader - drmDataLoader Updates Last Modified Date for All Dimension Members (Doc ID 2091455.1)

Last updated on JUNE 01, 2017

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.3.5.1.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Analytical Applications (OFSAA)
Slowly Changing Dimensions (SCD)

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. The date field should not be updated if there is no change in the dimension member.

ACTUAL BEHAVIOR
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.

The loading dimensions using the Dimension Loader and SCD Process.

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

Issue -
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 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 (As per the out of the box inserts for this table, please see below). Due to this, for every run, the members are getting closed logically and new records are opened even though there is absolutely no change in the data.

4 D_LAST_MODIFIED_DATE DA D_LAST_MODIFIED_DATE 2 N DATE

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms