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 m...
Forum Discussion
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)...
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.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.The "tree traversal algorithm" I've seen used is a recursive CTE that just loops through the parent/child to build that "flattened" field dynamically in a view. That allows us ignorant mortals to write SQL against it without even knowing how to spell CTE while also not polluting the table with excessive columns or stale values. Still, I really didn't want to go this route and it looks like I won't need to. Thank you for all the ideas though!
