Most implementations of data warehouse incorporate Parent-Child hierarchy model. This is the most flexible model in terms of defining complicated and lengthy hierarchy structures. Parent-Child Hierarchies are usually built using link tables in order to support multiple versions and time travel. There are two broad types of hierarchy structures within Parent-Child model with subtle variations. Based on scenario at hand, the right structure is chosen.
- Leaf Node Only Model: In this model, each hierarchy node is associated with its respective parent node. The node at topmost level is the only record without a link to parent node. Employee hierarchy is the perfect example of this structure. If the employee dimension has 100 records, the constructed hierarchy will have only 100 nodes including leaf and .
- Intermediate Nodes Model: The hierarchy is constructed with intermediate groupings. Records consist of both leaf nodes and group/intermediate nodes, where the group nodes help in classifying and linking the respective leaf nodes. Majority of hierarchies fall under this category, for example Cost Center, Profit Center, GL Account, Product, Customer etc. Another way to look at leaf and group nodes is by looking at the data, wherein data is actually posted against leaf nodes only. During reporting the actual data is grouped according to the hierarchy constructed based on all the nodes. Dimension table will
Hierarchies are very critical from reporting perspective and all BI projects have varying degrees of implementing this functionality. Defining the hierarchy is typically undertaken by business teams, which are vetted by stakeholders and then communicated to IT teams. There is heavy dependency on data stewards to maintain the relationships correctly and ensure data quality.
Hierarchy re-organization is a generic term for the process adopted by organizations that tweak hierarchy on regular basis. At times changes to hierarchy is periodic in nature, e.g. employee being promoted or transferred. In majority of cases, changing a hierarchy is initiated and finalized by business teams. An example here would be the event of global expansion, which will trigger hierarchy changes to a company Profit Centers, GL Accounts, Organizational Units etc.
Re-organization events are common occurrence and data capture architecture accommodates this by classifying hierarchy data as slowly changing dimension, which enables time travel in reporting.
NOTE: In SQL, there is no direct way to build the entire hierarchy using a single command. Oracle provides CONNECT BY & START WITH as part of SQL Plus library, which can construct the entire hierarchy in a single statement. NOTE 2: SAP BW architecture has hierarchy as built-in feature. The design approach uses link tables with a variant of intermediate grouping model.