How to model SCD Type 2 dimension which also has a parent child relationship in SSAS


  • Hi

    I have gone though couple of links on this topic and one of them has suggested changing the Type 2 to Type 1. However, I would like to know if there is any other solution to this.

    Consider AdventureWorksDW2012's employee dimension. It has a parent child hierarchy and its a SCD type 2 dimension. If I have to create the same employee dimension in SSAS what is the way I have to model it?

    If I just accept the parent child hierarchy to keep intact the recursive relationship between employees and then a change happens to the subordinate, same subordinate will be listed twice under the manager.

    If I try to model my dimesion to cater SCD Type 2 then the recursive nature is lost. Only one level of organisation structure is shown. I cannot see to whom the manager is reporting to.

    Please post any ideas you can think of.

    Tuesday, June 25, 2013 9:50 AM

All replies

  • Are you looking for a case in which one child can belong to multiple parents , if yes then check this post;


    Wednesday, June 26, 2013 8:51 AM
  • No that doesnt help..

    I need something which is a combination of Parent-child hierarchy and SCD Type 2.

    Wednesday, June 26, 2013 9:44 AM
  • Can you provide the sample and the expected result set so we can help you in more appropriate manner.


    Wednesday, June 26, 2013 10:21 AM
  • I have given an example below. We have a parenct child hierarchy and also the dimension is SCD type 2. The expected result is when we drill down on the employee dimension from Manager number: 42 I should be able to see all the managers below me like 28,24 etc and the lowest level should be 2 not 1.

    Wednesday, June 26, 2013 1:16 PM