Introduction
There are many instances where it will be helpful to view data rolled up to different hierarchies. Data could be collected for items rolling up to the primary hierarchy, but users would like to report that data against an alternate hierarchy.
Example
In the example below, an organization collected revenue data from sales representatives (“Sales Rep L2”), which rolls up to regions (“Regions L1”). With its primary hierarchy, it can report the sales representatives’ revenue by region. However, it would also like to report on revenue across the product lines of the representatives.
The primary hierarchy is set up as below:
- “Regions L1”
- “Sales Rep L2”
- In this list, the parent hierarchy is set to “Regions L1”.
Revenue data is collected in module “Revenue by Sales Rep-Region” with dimensions “Sales Rep L2” and “Year”.
An alternate hierarchy for sales representatives is set up as below to report by product lines.
- “ALT Product Lines L1”
- “ALT Sales Rep L2”
- This list consists of all the same members of “Sales Rep L2” along with their unique identifier, which in this case is the Code.
- In this list, the parent hierarchy is set to “ALT Product Lines L1”.
A system module is set up to map the “Sales Rep L2” of the primary hierarchy to “ALT Sales Rep L2” of the alternate hierarchy using the unique name of each sales representative.
A module is set up to report revenue by the alternate sales representative hierarchy. In the report below, the organization can review revenue by the product lines of the representatives.
Limitations and Considerations
- This method assumes Sales Rep has a 1:1 relationship with Product Line, meaning each Sales Rep only sells one Product.
- Manual or automated maintenance is required to update ALT Sales Rep L2 if there are any changes to the Sales Rep L2 list.
- For lists with complex relationships, consider building a mapping table between the lists, data module dimensioned by both lists, or an allocation table to create the alternate hierarchy and reporting modules.
Conclusion
The example above demonstrates one method of building an alternate hierarchy from an existing hierarchy and data in the model. Based on an organization's data availability, organizational structure, and business requirements, alternate hierarchies can be utilized to drive valuable insights from existing information.
