Forum Discussion

IrinaDragusanu's avatar
IrinaDragusanu
New Contributor III
2 days ago

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:

  1. 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.
  2. Is there a way to delete Orphan members with a BR?
  3. 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.

Analytic Blend
Confirmation Rules
Data Integration
Data Management
Import
REST Api
Workflow

4 Replies

  • rhankey's avatar
    rhankey
    Contributor II
    23 hours ago

    BR's can do just about anything, including deleting Orphans.  Like any other member, you cannot delete an Orphan through the user interface or a BR that contains data in the Cube, Stage, Journal or Annotations.  And in the event extensibility is being used, you have to delete the extended members before you attempt to delete the inherited parent.  As a best practice, I "Remove Relationship" of any members I no longer want, and periodically run a utility to delete the Orphans.  Should I have "Removed Relationship" in error, I can retrieve the member from Orphans - sort of like an Undo option.

    I am unaware of any way of preventing orphans from being created while importing a Metadata XML file.  The process "dumbly" imports whatever list of members and relationships are contained in the XML file, which will create the contained dimension(s), member(s) and/or relationships if they do not already exist, and update those that do exist.

    It sounds like the source system that is "pushing" members into OS knows when it doesn't know what the correct parent is otherwise it would generate an error trying to add a relationship to a non-existent parent.  For those members the source system is adding without a relationship, perhaps the source system should default to adding a relationship to an "Unassigned" parent, so the OS admin has better visibility as to the members that need to be reviewed and if accepted, manually moved to an appropriate parent.  This is the usual approach I use when I'm auto-adding missing members during the Import/Transform/Validate process via an event handler.  For some builds, I assign the auto-added member to correct parent if I can determine such, else add to Unassigned.  For other builds, I'll add all auto-added members under Unassigned.  I don't generally like adding new members under Orphans, as I use Orphans more as placeholder for members that are to be deleted, and form which I can recover the member should I find out later that it is in fact needed.  Basically, in my books, anything in Orphans is deletable, and periodically, I'll run a Delete Orphans utility to get rid of those that have no data.

     

    • IrinaDragusanu's avatar
      IrinaDragusanu
      New Contributor III
      23 hours ago

      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.

      • rhankey's avatar
        rhankey
        Contributor II
        22 hours ago

        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.