How to Delete Dimension Members Used in Hierarchy Using the drmDataLoader (Doc ID 2093287.1)

Last updated on JUNE 07, 2016

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.3.5.1.0 and later
Oracle Financial Services Funds Transfer Pricing - Version 6.1.0.3 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Funds Transfer Pricing (FTP)
Data Relationship Management (DRM)

Symptoms

On OFSAAI 7.3.5.1.0 / FTP 6.1.0.3, when attempting to delete a dimension member used in a hierarchy, an error is thrown and the member will not delete.

ACTUAL BEHAVIOR

After successfully loading a dimension structure using DRM approach and disabling one of the members and removing it from the hierarchy it is no longer possible to remove the disabled member by the Dimension Loader function, the error thrown "Cannot delete a member that is used as part of hierarchy".

EXPECTED BEHAVIOR

The dimension loader function should be able to delete a disabled and removed dimension member.
- If this is not a product limitation then the Dimension Loader function should be able to remove the disabled member or not fail to load.
- If there is a product limitation then there should be a workaround.

The issue can be reproduced at will with the following steps:

After loading a dimension hierarchy using the DRM method we have a structure with 7 members, members 1, 2 and 3 are configured as Leaf_Only=N whereas members 4, 5, 6 and 7 are configured as Leaf_Only=Y in Staging _B_INTF table. The hierarchy is built with member 1 as the top element, members 2 and 3 are children of member 1, members 4 and 5 are children of member 2 whereas members 6 and 7 children of member 3.

1. Using the DRM way of loading OFSAA Dimensions load in the appropriate tables the corresponding data for a hierarchy with 7 elements as follows members: 1, 2 and 3 configured as Leaf_Only=N in Staging _B_INTF table, members 4, 5, 6 and 7 are configured as Leaf_Only=Y
2. The hierarchy is built with member 1 as the root element, members 2 and 3 are children of member 1, members 4 and 5 are children of member 2 and members 6 and 7 children of member 3.
3. Load data into dedicated tables - Staging 4 tables -> Processing 4 tables -> Hierarchy Flattening -> View -> then the SCD Process loads into the DIM_ Result table
4. Disable member 7 and remove it from the hierarchy. It is no longer present in the Staging B, TL and HIER tables.
5. Try loading a new hierarchy but use the same identified for the member as the one disabled.
6. The Dimension Loader function fails with error "Cannot delete a member that is used as part of hierarchy" even though the member is disabled and removed from the hierarchy.

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