The Hub Model Hierarchy Management course method to turn a ragged hierarchy into a balanced hierarchy.
Have you ever turned a ragged hierarchy into a balanced hierarchy using the method highlighted in the Hub Model Hierarchy Management (305) course? It is an effective way of balancing a ragged hierarchy, but there is a fair amount of formula development and testing. The basic process is as follows:
Start with a Flat list of your ragged hierarchy.
Create a module containing information about the flat list
Create a module used to build the balanced hierarchy
Create Views within the Build module for all the levels in the balanced hierarchy
Create import processes to build each composite list of the hierarchy
After following this process, I developed a quick and easy tool to help with steps 2 and 3 above. I will describe how to use the tool in this blog post. Be forewarned that there are quite a few pictures here. I also want to note that the community post on 305 content by Rob Marshall has information on balancing ragged hierarchies.
The post can be seen here:
The tool is just a simple spreadsheet with some formulas defined. You simply enter the name of the flat list and the number of levels, and the formulas for the modules in step 2 and step 3 above are created. It looks like this:
The formulas are not complicated, but they are long, so having this tool saves time. And once the process is defined maintenance of the flat list is very easy. Just update for flat list and rerun the process.
Let’s dive further into the basic process and see how the tool can help.
1. Start with a Flat list of your ragged hierarchy
You will start with this flat list already developed. Ideally, this can be imported from a source system. For the purpose of this demonstration, we will be using a simple Products list called Products Flat. Within the flat list, we need a column for the Parent and the Parent Code. This is a view of it in Excel:
If this list looks familiar to anyone, it is taken from the ASO Sample database (if you know what that is 😊), with a few minor changes.
The flat list will need three properties. It must have these properties defined with these exact names for the tool to work properly:
Display Name [The format type is text]
Parent ID [The format type is text]
Parent Name [The format type is list, with the list being this flat list]
See below:
The list is a numbered list with the list Display Name Property set to Display Name. Here is what the list looks like in the model. Note the Parent is empty as this is a flat list.
Now that I have my flat list, I can enter the name in the spreadsheet tool. I also know that this hierarchy has six levels from top to bottom. I will enter that information into the spreadsheet.
That’s it as far as entering data in the spreadsheet. I can now use the information generated to build the two modules.
2. Create a module containing information about the flat list
Now we are ready to build the first module. I currently do not have any modules defined:
So, I am going to create the first module. I need to use the names generated in the tool:
This module will only be dimensioned by the flat list and line items. I can copy the line items from the tool and then paste them in to the list of line items:
Next, we need to update the format of the line items. They are listed in the tool:
Once we have set the Formats, simply copy in the formulas from the tool:
That’s it. The first module is complete and should look like this:
3. Create a module used to build the balanced hierarchy
Now we are on to the second module. We will follow the same process.
Use the name defined in the tool
Dimension by the flat list
Copy in the Line Items listed from the tool
Set the Format as defined in the tool
Copy in the formulas from the tool
And all set. Our module is complete and looks like this:
Apologies for the many screenshots; there are many columns to show in this module.
All the heavy lifting has been done for these modules! The rest of this post will simply run through rest of the process as it is described in the 305 training.
4. Create Views in the Build module for all levels in the balanced hierarchy
I like to simply name these the same as the module name with “Level x” appended. We are building this from top to bottom; so the first view for Level 1 will only have the member and the code. Levels 2 through 6 will have the level, the code, and the parent, as described in the 305 training. Here is a look at the Level 6 view:
5. Create import processes to build each composite list of the hierarchy
We need to create the composite lists for building the hierarchy. They will be named Product 1 through Product 6, with their parent hierarchies defined.
These are all currently empty. I will run through the import process for each, using the Views created in step 5. There should be no errors.
I can now create a Process to run all the import steps:
And finally, here a view of the balanced hierarchy:
Yes, this is a very simple example. But the number of the members in the flat list are not a factor is putting this together. The more critical factor is the number of levels in the hierarchy. In the example, there were 6 levels, so in the second module there are 5 parents defined along with the 6 ragged and 6 balanced levels.
Please drop us a line if you have any questions or would like to see more of the tool.
Comments