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.
Are you looking for a case in which one child can belong to multiple parents , if yes then check this post;
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.