I would like to create a hierarchy with 3 levels of lists: 1. Entity > 2. Direction > 3. Department. In the level "2. Direction", I want items to belong to several Entities. It is the same for Department level (a Department can belong to several Directions). I need to create a data hub and then a spoke model.
In the picture below you can find an example of my problem:
I don't really know how to populate my 2.Direction list in the spoke model. In my data hub, I've created 3 flat lists: Entity, Direction and Department. I don't know how to roll up my list 2.Direction into my 1.Entity list when it comes to the spoke model. I was thinking about using a numbered list for both Direction and Department in my spoke model? I don't know how I can export these items from the data hub to the spoke model (I can import them manually, but that is not the point).
Great question and one that occurs a lot. I have two suggestions:
(recommended) You can make each direction unique, like Marketing|Sales and Retail|Sales. In the system module for direction you can parse the code and create a line item with FINDITEM to a separate flat list that contains the direction values, for example Sales. Then you can use SUM or LOOKUP on the Sales Direction..
Alternatively, you can create an intersection module, some refer to this as a Calculation module (DISCO) that handles many to many mappings. One list contains the entities, One contains the direction. You can create a module that has both lists and use a boolean to indicate the relationships (this creates a lot of sparsity), or you can create a separate list that contains only the valid combinations.
It is possible to create as many hierarchies as is required. However, a child can not have multiple parents within the same hierarchy.
In your data hub create a module dimensioned by each list. Add line items for code, name and parent code. Use formula to populate code and name. You will need to manually allocate each item to a parent by entering its respective code against each list item.
Create a saved view for each module where you only show the code, name and parent code. You will need a view each time you need to allocate a new parent. Create a parent code for each distinct hierarchy. We will use this to create the various hierarchy lists in the spoke model.
Create another saved view where you do not include the parent. We use this to create a set of flat lists in the spoke model.
Create all the lists you need in the spoke model; the flat lists and each layer of hierarchies. For the hierarchies add a properties to the lists and dimension these by the three flat lists. These will be used to map across the hierarchies.
Import all your lists into the spoke model as flat lists first by selecting the 'Connect to Anaplan,' option when selecting to import and list.
Find the saved view from the selection and map the line items in the source view to the items in the list. Import using code if you have one other use name.
Repeat this process for each saved view for the remaining hierarchy lists ensuring that you also map the code into the properties.
Once all import actions are run you should have a full suite of hierarchies containing the various parents and mapping into the corresponding flat lists contained within the properties.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA