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.

rhankey
23 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.

11 Replies

rhankey
Contributor III
23 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
  Contributor
  23 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
    Contributor III
    23 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
Contributor
21 days ago
Please give KUDOS to my idea on Idea Stream: https://community.onestreamsoftware.com/idea/PlatformCore/validation-error-for-orphan-members/44523
- rhankey
  Contributor III
  21 days ago
  As I noted in my prior response, constraints will generate validation errors if the transformed member does not belong to a permitted parent. That will solve your problem without a line of code needing to be written, and IMHO using constraints to prevent garbage data or data going to invalid intersections is a best practice.
IrinaDragusanu
Contributor
21 days ago
Please , also give KUDOS to my second Idea on Idea Stream related to this topic: https://community.onestreamsoftware.com/idea/PlatformCore/metadata-builder-xml-file-to-not-create-the-members-in-orphans-if-the-parent-doe/44525
- rhankey
  Contributor III
  21 days ago
  Have you looked at the content of a metadata XML file? There are two sections in an XML file, a Members section and a Relationships section. The two are added or processed independently of one another. The same goes with how members and relationships are added "under the hood" via the api. You have to add a member first, before you can add zero or more relationships for that member right away or at some later date. Until a relationship is added, the new member "appears" under Orphans. One can even add a relationship to an orphaned ancestor. With what you are proposing, you would have problems adding members, or removing the last member relationship of a member.
  It sounds like the root of the problem you are having is that you have a process that adds members to OS, but doesn't always know to what parent that added member should be assigned. If you do not want members landing in Orphans, then change your process to only add members for which you do know the parent they need to be assigned. Using OS constraints should also be used to ensure only valid combinations are permitted when validating - just because the member is not under orphans does not mean that it should be used with a particular row of source data.
  - IrinaDragusanu
    Contributor
    21 days ago
    Hi rhankey,
    
    Thank you for your detailed response and for clarifying how the metadata XML file processes members and relationships separately. I understand the logic: the first part of the file creates the member, and the second part assigns the member to a parent using the Relationships section.
    
    However, my concern arises during the metadata load process in the Application Load area. If there is an error with the relationship (i.e., the specified parent does not exist), the system displays an error, but the member is still created under Orphans. Would it not be possible to include an option in the Metadata Builder process to automatically delete members from Orphans if no valid parent is identified at load time? Creating new members in Orphans allows data to be loaded to them via Workflow, which then cannot be consolidated and may result in out-of-balance issues.
    
    From our company’s perspective, there is no clear benefit to retaining Orphan members—especially newly created ones with no data. The only scenario where we might need to keep Orphans is for historical purposes if data already exists under them and deletion is not an option. Otherwise, proactively removing unattached members would help maintain data and metadata integrity.
    
    Thank you again for your insights—they are logical and fair. Currently, we have constraints set to ‘Root’ for all UD1 members. Based on your experience, which constraint settings would be most effective for validation to prevent the issues above?

Forum Discussion

Orphan members - validation error required

11 Replies

Related Content

Hierarchy validation

Parameters - Manual Updates Required

Extender: automate Import, Validate, Load

Composite validation

Journal validation