Hans Klüser

 

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:

WITH version AS
(
  SELECT 
    importdate,
    rn = ROWNUMBER() OVER
         (PARTITION BY 1 ORDER BY importdate DESC)
  FROM myHierarchy
)
SELECT * FROM myHierarchy AS H
INNER JOIN version AS V
ON
  V.importdate = H.importdate AND
  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).

WITH version AS
(
  SELECT
    fiscalyear,
    importdate, 
    rn = ROWNUMBER() OVER 
         (PARTITION BY fiscalyear ORDER BY importdate DESC)
  FROM myHierarchy
)
SELECT
 
V.fiscalyear,
  H.*
FROM myHierarchy AS H
INNER JOIN version AS V
ON
  V.importdate = H.importdate AND
  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.