Parent Child Hierarchies

You can define a column of your dimension in Dimodelo as a ‘parent’ column. For example, if you had an Employee Dimension, and a Employees were organised in a ragged hierarchy based on an Employee to Employee ‘managed by’ relationship, then the column that contains an Employee’s ‘Manager Employee Id’ should be identified as the ‘parent’ column. The pattern will generate a Parent surrogate key for the selected parent column in the Dimension and maintain the relationships between Parent and Child rows automatically at the surrogate key level. The parent surrogate key contains the surrogate key of the dimension row that represents the Employee that is the manager of an Employee. It is important to maintain this relationship at the surrogate key level, so that history of relationships can be maintained.

If the SCD type of the Parent column is set to SCD Type 2, then the history of parent-child relationships over time will be kept. This means that a member can appear in more than 1 place in the parent child hierarchy. For example, in an organizational chart parent-child dimension, as a person moves between roles and managers, they will appear in more than one place in the organization chart. From a Cube/Tabular perspective, if you browsed the Employee Dimension parent-child hierarchy, you may see the same Employee multiple time in the Hierarchy. However if you were to introduce a measure into a pivot table, because this is a type 2 change, facts will be reported correctly. For example, if a sales person reported to Manager A , and then reported to Manager B, the aggregate sales for Manager A would include the sales made by the sales person while they reported to them, and the  aggregate sales for Manager B would include the sales made by the sales person after the salesperson changed to report to Manager B.

When defining the parent-child hierarchy in a SSAS Cube use the surrogate key and parent surrogate key as the basis of the hierarchy.

At present the pattern only supports single attribute parents. That is, the business key of the Dimension is a single attribute.

The video below demonstrates defining a parent child hierarchy in Dimodelo. It builds on the previous derived staging topic.