Forum Discussion

photon's avatar
photon
Contributor
3 days ago

determining parent by hierarchy

tldr: What's a performant, reliable way to determine a member's parent within a given hierarchy?

I have been developing a custom extender rule to automate OneStream metadata updates based on our master metadata source. One of the last remaining tasks to solve is how to correctly execute member moves. As OneStream can have a given member exist multiple times in the same dimension, the only way to execute a move in OneStream is by specifying "move from parent A to parent B." Parent B is quite easy to determine as our metadata source provides this information per-hierarchy and does not allow duplicates but it does not tell us parent A, or even if there was a move at all.

This means I have to validate that the specified member 1) is under the same parent that our metadata source says it should be and 2) isn't anywhere else in the hierarchy.

However, in OneStream, I know of no way to query the parent(s) of a member but only those within a specific hierarchy. I've never needed to do this in a BR before but I have definitely been stung by this limitation while working with QVs in the past. This makes it feel like a common-enough need that there's probably a way to do it that I'm just unaware of. If not, I'll have to code it myself and I could use some insight into a performant and logically-sound way to do it.

So far, the only approach I can imagine is this:

  1. BR API GetDescendants to pull all members in a hierarchy.
  2. Verify the child exists in the hierarchy.
  3. BR API GetParents to pull all parents of the child.
  4. Iterate through each parent to see which one(s) exists in the hierarchy.
  5. If there's only one and it differs from the one provided by our metadata source, move it. If there are multiple, oh bother.

However, I foresee a lot of unpleasant little surprises in developing this. I also worry about performance as there's a Cartesian product of the number of descendants times the number of parents. Even if it's speedy, it still seems like a very indirect route to take for what is conceptually simple.

What's the better way to do this?

Extenders

17 Replies

Sort By
  • photon's avatar
    photon
    Contributor
    2 days ago

    A few of you really went out of your way to offer some insight and I appreciate that. Ultimately, my "proof of concept" code is quite simple. It needs some extra validation and I'll build in an email summary as well but it looks like this:

    Dim entityDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si,"AllEntities")
Dim childId As Integer = BRApi.Finance.Members.GetMemberId(si,dimTypeId.Entity,"Loc432")
Dim hierId As Integer = BRApi.Finance.Members.GetMemberId(si,dimTypeId.Entity,"ManagerHierarchy")
Dim parentRelationships As List(Of Relationship) = BRApi.Finance.Members.ReadParentRelationshipsNoCache(si,dimTypeId.Entity,childId)
Dim correctParentId As Integer = BRApi.Finance.Members.GetMemberId(si,dimTypeId.Entity,"Bob")

For Each objRelationship In parentRelationships
	If BRApi.Finance.Members.IsDescendant(si,entityDimPk,hierId,objRelationship.RelationshipPk.ParentId) AndAlso Not objRelationship.RelationshipPk.ParentId.Equals(correctParentId)
		Dim objRelOptions As New RelationshipPositionOptions 'this feels superfluous but the following line fails if I pass Nothing here
		BRApi.Finance.MemberAdmin.CopyOrMoveRelationships(si,entityDimPk,New List(Of RelationshipPK) From {objRelationship.RelationshipPk},correctParentId,False,objRelOptions)
	End If
Next
Return Nothing

    I'll turn the sample values into variables and use For loops on the datatable I query from our metadata source but this is all the heavy lifting.

    Edit: Essentially, if Loc432 exists in the Manager hierarchy under any parent but the one specified in source, move it. As it's written at the moment, it assumes the hierarchy is already correct otherwise and only the position of this member is outdated. It doesn't address any oddities like a duplicate members, any of the members not existing in the dimension at all, being unable to find the ID for the correct parent, general failures at any point, or really anything else at all. However, when things are right and expected, it works. It should be useful as a starting point.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 days ago

    I would update the use case for this requirement:
    The requesting process or user to include the parent of the member being moved.

     Without the parent in the request you're being asked to perform magic.  

    e.g.:
    public void ProcessMemberMove(Member memberToMove, string currentParent, string newParent)...




    • photon's avatar
      photon
      Contributor
      2 days ago

      That would be lovely but the corporate metadata team and platform don't track/provide moves (or renames, but that's a different issue.) I only get the current state and it's expected that OneStream match it. I can do it for all the other properties because their current values don't matter, I can just overwrite them. This requires me to be specific and determine the current value, first, which OneStream apparently can't do. Seems odd.

      Years of hammering on hierarchical data shoved into relational tables has taught me that the right way to do this in SQL is to include a column in the table/view itself that shows the full hierarchy as an nvarchar: root\Managers\Sally\Loc432 (or something like that.) This allows you to do simple pattern matching along the lines of "Select parent From Members where child = Loc432 and path like 'root\Managers\*'"

      I would love to find an API to do this as I trust bigbrain enterprise developers to come up with a better way than my amateur hacking and I really don't want to resort to a custom view (and then querying that view) but it may be the only practical way. Otherwise, I'm back to my early musings or maybe some kind of recursive sorcery.

      The one thing I can do as a stopgap is at least do basic detection of it being wrong. If the list of parents contains the parent that source indicates, hooray. If not, add it to the email notification. It would be a manual fix but at least it would be something.

      • RobbSalzmann's avatar
        RobbSalzmann
        Valued Contributor II
        2 days ago

        If what you have to work with is Current State and Expected State, then your best option is to discard Current State and build the Expected State.  The problem is OneStream wont allow this when data is present.  This presents you with a challenge: explain to the business their process lacks necessary information for you to complete a request.

        You can find the parent by searching the children or descendants of a member and some clever LINQ.  (This is your self written API).

        As far as storing a hierarchy in relational, I see this mistaken logic everywhere in the form of "flattening" the hierarchy into either many columns, or one column with the entire ancestry list e.g. 'root\Managers\Sally\Loc432'.  I would suggest this is not necessary.  The only information needed to reconstruct any tree based hierarchy is two columns: parent and child.  that's it.  A standard recursive tree traversal algorithm will quickly and efficiently generate your parent-child list and the same algo will create the new tree where you want it.  You can also query this structure for things like descendants, children, parents, etc.

        For efficiency, I would skip using a relational table for this and do it all in memory using simple collections.:

        As far as an API, you'll have to write your own - this what I do.  The BRApi can be adopted, round peg through a square hole style, to fit this requirement.

  • sameburn's avatar
    sameburn
    Contributor III
    2 days ago

    Hi photon

    Have you considered using Application Control Manager (ACM) for this? 

    Thanks 

    Sam

    • photon's avatar
      photon
      Contributor
      2 days ago

      I dislike ACM for a variety of reasons but, even if we used it, it wouldn't remove the restriction of needing to know which parent to move it from.

      • sameburn's avatar
        sameburn
        Contributor III
        2 days ago

        For source initiated requests ACM will process a request based on your source data and your target dimension e.g Adds, Moves, etc. Which it sounds like you are trying to build manually. You only need to pass in the parent for user initiated ACM requests...

        But just a suggestion. 

  • rhankey's avatar
    rhankey
    Contributor II
    2 days ago

    If I understand correctly, the goal is to move a member from hierarchy A to hierarchy B.  If so, then build a dictionary of A.DescendantsInclusive members.  Then when moving the member, simply remove the relationship for any parent found in your dictionary.

    • photon's avatar
      photon
      Contributor
      2 days ago

      Not quite. Let's say I have a management hierarchy and a geographical hierarchy. After some corporate restructuring, the member now exists in a new part of the management hierarchy but remains in the same place in the geography hierarchy. I need to precisely determine the parent from the management hierarchy (parent A) and move it to the new parent.

      • rhankey's avatar
        rhankey
        Contributor II
        2 days ago

        Same concept.  Build a dictionary of Management.DescendantsInclusive.  When moving the member within Management, remove the relationships for any parents in the Management dictionary.  I suggested the dictionary approach on the presumption you have a bunch of members to move.  If you only have one or two members, there is an IsDescendantds() function too.