[IN PROGRESS] Reporting with Alternate Hierarchies

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”.

Lisa_YH_Chen_0-1605683021652.png

Lisa_YH_Chen_1-1605683021676.png

Lisa_YH_Chen_2-1605683021689.png

Revenue data is collected in module “Revenue by Sales Rep-Region” with dimensions “Sales Rep L2” and “Year”.

Lisa_YH_Chen_3-1605683021699.png

Lisa_YH_Chen_4-1605683021709.png

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”.

Lisa_YH_Chen_5-1605683021715.png

Lisa_YH_Chen_6-1605683021724.png

Lisa_YH_Chen_7-1605683021731.png

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.  

Lisa_YH_Chen_8-1605683021740.png

Lisa_YH_Chen_9-1605683021747.png

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.

Lisa_YH_Chen_10-1605683021755.png

Lisa_YH_Chen_11-1605683021760.png

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. 

0 Kudos
Comments

Hi Lisa, great article! Does it make sense to include import/process actions to fully build out the alternate hierarchy, instead of just using a reporting hierarchy via the module calculations? Thanks for sharing this best practice!

About the Author
Labels (2)