Mapping Drilling Hierarchies

In dimensional modeling there are various types of hierarchy that range from simplistic to complex hierarchies with multiple level starting points. These complex hierarchies are often referred to as ragged hierarchies. Ragged hierarchies can occur because some of your source data systems may capture data at a very granular level whilst another system may capture or output data at summary levels.

You may also be using third party data and wish to combine it with your in house data. When the data is consolidated it may not be possible to populate all levels of the hierarchy with the same coverage if at all.

In these situations it can be useful to undertake a mapping exercise with an outcome similar to that shown in the diagram. This should occur before modelling the hierarchies in dimension tables and the ETL build.