Ragged and Balanced Hierarchy help. Many to Many relationship with Ragged Hierarchy.
I have been reviewing the knowledge around Ragged and Balanced hierarchy. I have a bit of a unique situation and need some help from the Community.
Attached is a file that breaks down the flat file I have put together of a relationship between entity-owner.
Each entity has an owner. That owner can also be an entity- and roll up to another owner. Eventually the hierarchy will reach the highest level which rolls up to the "ultimate" owner.
Is there a way for me to find automatically calculate what level each entity is on in the hierarchy in my flat list in the file? I.e. "B" is level 3, "D" is level 2, "F", is level 1. How would I go about calculating this in Anaplan?
I am working with a client that has no ETL tool available in their budget and all transformations will need to happen in Anaplan in the Data Hub.
See image below for visual representation (typo, the top "F", is supposed to be "G").
Thanks everyone for the reading material and suggestions. These are very helpful. Coincidentally, another colleague of mine built a near replica model for the exact use case I'm after. He showed me a robust way to build a hierarchy, one that seemed counter-intuitive at first, but turned out to be ingenious.
I will work to put it together in a best practice article very soon. Some of the solutions here were relevant but not quite exactly what I was looking for.