Forum Discussion

IrinaDragusanu's avatar
IrinaDragusanu
Contributor
25 days ago
Solved

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 ...
Analytic Blend
Confirmation Rules
Data Integration
Data Management
Import
REST Api
Workflow
  • rhankey's avatar
    rhankey
    24 days 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.

     

rhankey's avatar
rhankey
Contributor III
24 days 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
    Contributor
    24 days 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 III
      24 days 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.

       

      • IrinaDragusanu's avatar
        IrinaDragusanu
        Contributor
        24 days ago

        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.