How to Delete Dimension Members Used in Hierarchy Using the drmDataLoader
Last updated on NOVEMBER 06, 2017
Applies to:Oracle Financial Services Analytical Applications Infrastructure - Version 220.127.116.11.0 and later
Oracle Financial Services Funds Transfer Pricing - Version 18.104.22.168 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)
On OFSAAI 22.214.171.124.0 / FTP 126.96.36.199, when attempting to delete a dimension member used in a hierarchy, an error is thrown and the member will not delete.
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".
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.
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