SCD type 2 with parent-child hierarchy aggregation issue

Question

We have a staff dimension that has a self-reference for managers (Parent-Child relationship) that we built the hierarchy on it.

DimStaff table:

 | SurrogateKey | BusinessKey | Employee Name | ManagerBusinessKey |  StartDate  |  EndDate  |
 |      1       |      1      |   Manager1    |        NULL        |  2013-01-01 | 2099-01-01|
 |      2       |      2      |   Manager2    |        NULL        |  2013-01-01 | 2099-01-01|
 |      3       |      3      |   Employee1   |        1           |  2013-01-01 | 2014-01-01|
 |      4       |      3      |   Employee1   |        2           |  2014-01-02 | 2099-01-01| 

Fact Table:

 | StaffKey | DateKey  | Measure1 |
 |    3     | 20130405 | 10       |
 |    4     | 20140203 | 20       |

Now, with this data set as an example, the requirement is to

1- Be able to drill down through the hierarchy

 Manager1
    ->   Employee1  
             ->   Measure1=10
 Manager2
    ->   Employee1  
             ->   Measure1=20

2- Aggregate the values for each hierarchy level when one person is selected

Employee1    ->   Measure1=30

How can we go about doing that? (the problem is we built it but the second requirement doesn't work because the cube accepts the two states of Employee1 as two separate enities and wont aggregate them.)

Answer 1

I sounds like your Employee Name has been defined on an attribute which uses SurrogateKey as the KeyColumns property. I would define a new attribute which has BusinessKey for KeyColumns and Employee Name for the NameColumn.

Answer 2

Try below SQL to get the answer

DECLARE @DimStaff table(
SurrogateKey INT,
BusinessKey INT,
EmployeeName Varchar(30),
ManagerBusinessKey INT, 
StartDate DATE,
EndDate DATE
);

Insert into @DimStaff
    (SurrogateKey, BusinessKey, EmployeeName, ManagerBusinessKey, StartDate, EndDate)
Values
    (1,1,'Manager1', NULL, '2013-01-01', '2099-01-01'),
    (2,2,'Manager2', NULL, '2013-01-01', '2099-01-01'),
    (3,3,'Employee1', 1, '2013-01-01', '2014-01-01'),
    (4,3,'Employee1', 2, '2014-01-02', '2099-01-01')

DECLARE @FactTable table(
StaffKey  INT,
DateKey   DATE,
Measure1  INT
);

INSERT INTO @FactTable 
    (StaffKey, DateKey, Measure1)
Values
    (3, '2013-04-05', 10 ),
    (4, '2013-02-03', 20 )

select t1.EmployeeName as Manager, t2.EmployeeName as Employee, ft.Measure1 as Measure from @DimStaff t1 
inner join @DimStaff t2 on t1.BusinessKey = t2.ManagerBusinessKey
inner join @FactTable ft on ft.StaffKey = t2.SurrogateKey

select t1.EmployeeName as Employee, sum (ft.Measure1) as TotalMeasure from @DimStaff t1 
inner join @FactTable ft on ft.StaffKey = t1.SurrogateKey
group by t1.EmployeeName