Level based hierarchies are simple to create and maintain. Due to its simple architecture, this design comes with several small shortcomings. This is adopted for dimension data that is not complex to maintain or simple in reporting requirements. The example here demonstrates how the hierarchy is structured within the dimension table as attributes. Each attribute typically holds a higher level entity. Reporting tools can construct a top-down hierarchy based on granularity of the attributes. At times it is not possible to have all the leafs within a hierarchy assigned, in which case the ETL logic can be written to either group under “Others” or “Unassigned” so that reporting is not affected for time being.
This design works well when the depth of levels are fixed and do not change e.g. 4 levels in this case. Any changes to the depth of hierarchy literally revamps the entire data model for dimension table as well as all ETL data flows.
As the name suggests, this type of design can have only one active hierarchy defined for each dimension. This is a huge constraint because two important functionalities are not addressed
- What if there are multiple hierarchies for the same dimension? e.g. Calendar Year vs Fiscal Year or Sales vs Manufacturing of Products
- How to handle time travel i.e hierarchy changes over period of time?
Level hierarchies need not be integrated with the dimension table as shown in the example above. Another popular design separates the hierarchy data and populates in separate table called hierarchy link table. This leads to possible snow flaking of the dimension table, but brings many advantages that overshadow its disadvantages.
NOTE: SAP BW does not support this functionality out of the box, instead uses the parent child approach. SAP HANA on the other hand supports this as one option during design time.
Business Objects (BOBJ) Web Intelligence supports this type of hierarchy provided it is defined in the data modeling (meta data) layer.