“Hierarchy” as the term suggests, is a system of arranging/ranking a collection of items as per predefined rules. The primary need for hierarchy in a report or dashboard arises from the fact that business activities, processes and stakeholders of any subject area are organized and executed along a hierarchy. Couple of most common examples are Time and Geography as represented in. Few other hierarchies encountered frequently in BI space are Employee, Cost Center, Profit Center, Business Unit, GL Account, Product, BOM, Customer etc.
A dimension table containing master data and attributes typically has a flat structure with the key field followed by attributes. In a report or dashboard, the attributes are used for slice and dice or drill down and roll up operations. In the example above, Location attribute is an ideal candidate for hierarchy. Defining and maintaining a hierarchy will enable reporting along a structured approach, which mimics how activities are in real world. Hierarchy provides an easy and controlled method of drilldown and rollup of fact data.
Hierarchy data logic can be handled either by ETL and pre-populated within the data mart or handled in the reporting layer at run-time. Implementing the complete hierarchy logic in reporting layer is costly in terms of time, complexity and resources required. Most common approach in Data Warehouse Environments have hierarchy data modeled and populated by ETL into dimension tables, which is directly used by reporting tools to construct the tree like structure in front end.
Two prominent approaches of building hierarchies are adopted today.
- Level based hierarchies
- Parent Child linked hierarchies
One critical success factor in a BI project is to address the issue of Hierarchy Re-Organization. All businesses are dynamic and are constantly in state of change and hierarchies must be aligned with business decisions. Geo Hierarchy for the examples below gives an overview of some common events that result in hierarchy re-org
- Target Inc, the worlds second largest retailer decides to open a dozen stores in India. This is and example of growing into new markets, where the hierarchy grows in terms of the number of levels as well as nodes.
- Target Inc has 40 stores in India and decides to bifurcate the operations into East Zone and West Zone of 20 stores each. Here, the hierarchy bottom level remains the same, but a new parent level has been introduced.
- Target Inc found that the stores are not balanced in terms of size between zones. The organization has decided to assign select stores from east to west zone thereby having more stores in west zone compared to east. Here, the parent references have be re-assigned.
- Target Inc is expanding into other neighboring countries of India. From reporting perspective, the subsidiaries are setup under arm of the parent company with each region managing their own set of stores. This is an example of a growing un-balanced hierarchy.
There are several such scenarios and many are unique to different industry domains. A BI system’s ability to immediately capture such events and reflect them in near real time is another critical success factor for BI Adoption.
Time travel is another pre-requisite in hierarchies, which is dealt as a separate topic. Since hierarchies change often, reporting historical data as per current norms will not convey 100% truth.
NOTE: SAP BW and HANA offers hierarchies built in as part of modelling tasks. The data model is created and kept ready for data to be loaded as part of configuration. Reporting tools (e.g. BEx) use the hierarchy data directly to construct the tree grouping in runtime.