Hierarchies is a series of many-to-one relationships and can be of different levels. A Region hierarchy consists of: Region → Country → State → City → Street. Hierarchies follow top-down or bottom-up approach.
Logical dimensions or dimension hierarchies are created in BMM layer. There are two types of dimensional hierarchies that are possible −
- Dimensions with level-based hierarchies.
- Dimension with Parent-Child hierarchies.
In level-based hierarchies, members can be of different types and members of the same type come only at single level.
In Parent-Child hierarchies, all members are of the same type.
Dimensions with Level-based Hierarchies
Level-based dimension hierarchies can also contain parent-child relationships. The common sequence to create level-based hierarchies is to start with grand total level and then working down to lower levels.
Level-based hierarchies allows you to perform −
- Level-based calculated measures.
- Aggregate navigation.
- Drill down to child level in dashboards.
Each dimension can only have one grand total level and it doesn’t have a level key or dimension attributes. You can associate measures with grand total level and default aggregation for these measures are grand total always.
All lower levels should have at least one column and each dimension contains one or more hierarchies. Each lower level also contains a level key which defines unique value at that level.
Types of Level-based Hierarchies
Unbalanced Hierarchies
Unbalanced hierarchies are those where all the lower levels don’t have the same depth.
Example − For one product, for one month you can have data for weeks and for other month you can have data available for day level.
Skip Level Hierarchies
In skip-level hierarchies, few members don’t have values at higher level.
Example − For one city, you have state → country → Region. However for other city, you have only state and it doesn’t fall under any country or region.
Dimension with Parent-child Hierarchies
In parent-child hierarchy, all the members are of the same type. The most common example of parent-child hierarchy is the reporting structure in an organization. Parent-child hierarchy is based on a single logical table. Each row contains two keys – one for the member and another for the parent of the member.