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
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!
Here's an example of recursive tree traversal that will build in-memory a searchable parent child list representation of your dimension's members, with the code I posted earlier, you can get to your requirement:
private MemberNode BuildMemberNodeTree(DimPk dimPk, int memberId)
{
try
{
if (dimPk == null || memberId == DimConstants.Unknown)
return null;
// Get the root member
Member rootMember = BRApi.Finance.Members.GetMember(si, dimPk, memberId);
if (rootMember == null)
return null;
var rootNode = new MemberNode(rootMember);
// Get child members
List<oscMember> children = BRApi.Finance.Members.GetChildren(si, dimPk, memberId);
Parallel.ForEach(children, new ParallelOptions { MaxDegreeOfParallelism = 16 }, child =>
{
var childSubtree = BuildMemberNodeTree(dimPk, child.MemberId);
if (childSubtree != null)
{
lock (rootNode.Children)
{
rootNode.Children.Add(childSubtree);
}
}
});
return rootNode;
}
catch (Exception ex)
{
BRApi,ErrorLog.LogError($"BuildMemberNodeTree failed at member ID {memberId}: {ex.Message}", ex);
return null;
}
}