具有父子层次结构聚合问题的SCD类型2

Question

我们有一个员工维度，它具有管理人员的自引用(父子关系)，我们在其上构建了层次结构。

DimStaff表:

 | 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| 

事实表

:

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

现在，以这个数据集为例，要求是

1-能够深入到层次

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

2-当一个人被选中时，汇总每个层次结构级别的值

Employee1    ->   Measure1=30

我们该怎么做呢?(问题是我们构建了它，但第二个需求不起作用，因为多维数据集接受Employee1的两个状态作为两个独立的实体，并且不会聚合它们。)

Answer 1

听起来您的Employee Name已经在使用SurrogateKey作为KeyColumns属性的属性上定义了。我将定义一个新属性，其中KeyColumns为BusinessKey, NameColumn为Employee Name。

Answer 2

试试下面的SQL来得到答案

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