Dealing with Hierarchies is not always the easiest task in your datawarehouse. There is a series of questions in regard to dimensional model, unknown-members, historisation, and cube-usability.
Therefore I start this little series with following parts:
Part 1: One or two dimensions for master data and hierarchy structure
Part 2: Unknown members in an unified hierarchy dimension
Part 3: Historisation of hierarchy-structure
Part 4: How to flatten hiearchies the smart way in a view
Let’s go ahead with part 1!
When designing a datawarehouse dimension for parent-child hierarchies you have to decide to use one single dimension for the hierarchie-structure and the master data of the leaves or two seperate dimensions. When using just one dimension I will speak of an unified dimension.
Both ways have pros and cons so it depends on your specific situation.
The main questions you have to look on are:
- Does your source system seperate master-data und hierarchie structure
- Is your hierarchie complete or do you have leaves which are not included in the hierarchie
- Do you use different hierarchies structures on the same measures
- Do you have delta mechanisms for the related measures
- Do you have plan items on leaves and nodes in your hierarchy
- Do you have a dimension for calcalutions in your cube
Does your source system seperate master-data und hierarchie structure
When you have only one source for both structure and master data you don’t have the need to seperate it in your dimensional model in general. But in many projects, especially with SAP as source systems, master-data and hierarchy structure are seperated. Most of time it’s easier to stay with the seperation, but it’s not always the best way.
Is your hierarchie complete or do you have leaves which are not included in the hierarchie
If your hierarchie does not include all possible leaves the reason can be purpose or fault. If there are related measures you have to decide wether they should be seen in your reports (e.g. in an unknown hierarchy) or not. If not (and you can ensure they’ll never be) you can simply use your unknown member, otherwhise one way to solve the problem will be descriped in part 2: unknown members in an unified hierarchy dimension.
Do you use different structures on the same measures
In many cases with e.g. cost center or cost element reporting you have different hierarchy structures to answer different questions. In these cases it is much easier to seperate master data and hierarchy structure to simplify your lookups when processing your measures. You can still unify and flatten the dimensions in your datamart views as described in part 4 of this blog-series.
Do you have plan items on leaves and nodes in your hierarchy?
If you have plan items to be included in your reports and they can be related as well to leaves as to nodes it is an indicator for using one unified dimension. It simplifies your measure handling and reduces complexity especially when you have changing versions and different granularity of plan items between different years.
Do you have a dimension for calculation in your cube?
In combination with the previous question it gets very complex when you have plan items on leaves and nodes and you have to make a comparison of target achievement in the course of different years. Therefore I would prefer a unified dimension.
When you have only one hierarchy version it can be very useful to unify master data and hierarchy structure in one dimension even though the entities are seperated in your source system. Some of the upcoming tasks when deciding to do so will be solved in the following posts of this series.