Orphan members - validation error required
Hello dear community members,
Background:
We have had situations with month end workflow load validation errors which were fixed with UD1 mass upload. Few UD1 members ended up in Orphans, as the ...
Forum Discussion
Hi. Thank you for your reply. I am aware from other posts on the community that DeleteOrphans Utility is risky to use. If the Utility only delete unused members, than it will help us clean exactly what is not necessary. Any chance you could share how to do this please? Furthermore, Shouldn't the Load, Validate, load step give an error when trying to load to an Orphaned member? I remember we use to get a validation error if a member would be orphaned and used in the load file.
I guess different people will have different opinions, but in my opinion, programmatically deleting orphans has much lower risk than deleting non-orphaned members. You cannot manually or programmatically delete a member that still contains data, so there is a bit of a safety net whether the member is an orphan or not. However, deleting a non-orphaned member deletes any relationships it had too (sometimes in other hierarchies you hadn't considered), which if deleted in error means you are left scrambling to try to put things back together. The best practice I drill-in at every build I've been a part is not use Delete member for non-orphaned members, but rather use Remove Relationship to get rid of members from the non-Orphaned hierarchies, and then sometime down the road you can either manually or programmatically delete the Orphans. In the interim, you have a means of recovering the member from Orphans. So, that is why I personally believe programmatically deleting orphans is the far less risky approach. If you just need to get rid of some junk member from non-orphans, then fine, Delete Member, but otherwise only delete from Orphans.
The out of box Import/Transform/Validate process doesn't care if the member is in orphans or not. Validate is merely checking the row got assigned to an existing member of each dimension. An orphaned member does still exist and permits data to be loaded to it and may have historical data. An orphaned member just doesn't have any parent relationships. Validation isn't looking to see if each member has a relationship to at least one parent. I suppose you could write your own Transformation Rule Event Handler to fail a row if a member was in Orphans.
I normally use constraints very heavily, which will catch the orphan situation you are having issue with. You could set the constraint to the Top (or whatever appropriate parent) member of the dim. At that point, the Validation will fail as the orphaned member will not be a member of the Top member.
Hi. Thank you for your answer. The Event Handler option sounds what we need. With my basic BR skills, I will look into it and hopefully find the right coding piece of information to add to get a validation error for Orphans. Your help is very appreciated. Thanks.