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 Parent did not exist yet in OneStream at the members mass upload step. The validation error disappeared and the workflow was complete. However, this created an Out of Balance and it was difficult to retrieve the issue.
Questions/potential solutions help required:
- Would it be possible for the month end Workflow load Validation error to ignore Orphan members and still get an error even when we create members and end up by mistake in Orphans.
- Is there a way to delete Orphan members with a BR?
- Is there a way to stop the Metadata builder xml file to create Orphans when a parent is missing from OneStream?
Thank you in advance for any idea/suggestion/tip/hint.
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.