Dealing with Hierarchies Part 3: Historisation of hierarchy-structure

Technical Value


Historisation of hierarchy structure is not required in all projects especially not from the beginning.

But most of time it’s too late to implement it afterwards because source systems do not have any scd2 information about the structure of the hierarchy.

This is the reason why we store daily snapshots of the structure in our staging area or replication layer. We simply add an additional key column <importdate> and use it to get a clean daily data.

A simple cte gives as a clean slice:


  1. WITH version AS      
  2. (        
  3.   SELECT 
  5.     importdate,        
  6.     rn = ROWNUMBER() OVER        
  7.          (PARTITION BY 1 ORDER BY importdate DESC)        
  8.   FROM myHierarchy        
  9. )        
  10. SELECT * FROM myHierarchy AS H        
  11. INNER JOIN version AS V        
  12. ON        
  13.   V.importdate = H.importdate AND        
  14.   rn = 1

Even if we don’t need it from the original requirement it saves much time and effort in the future.

Another aspect to consider is that in the majority of cases changes in the structure are corrections or additions which don’t have an obvious datefrom-criteria.

Because of this we decided to use exactly one structure for each fiscal year and all changes within the years are regarded as corrections (scd1 within fiscal year).


  1. WITH version AS      
  2. (        
  3.   SELECT        
  4.     fiscalyear,        
  5.     importdate, 
  6.     rn = ROWNUMBER() OVER 
  7.          (PARTITION BY fiscalyear ORDER BY importdate DESC)        
  8.   FROM myHierarchy        
  9. )        
  10. SELECT        
  13. V.fiscalyear,        
  14.   H.*        
  15. FROM myHierarchy AS H        
  16. INNER JOIN version AS V        
  17. ON        
  18.   V.importdate = H.importdate AND        
  19.   rn = 1

In our datamarts/cube-dimensions we provide one current version of the dimension to view historical data with the current hierarchy structure and one historical version of the dimension where all facts are related by their normal business key and their original fiscal year.

In the next part of this series i describe how you flatten a parent-child hierarchy in a view which is quite easy to adapt on nearly all parent-child hierarchies.

The flattened hierarchy is needed in order to provide year-to-year comparison on all nodes even when structure is changing at any level.

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden