SSAS中事实表中的数据如果因为一对多或多对多关系复制了多份，在维度上聚合的时候还是只算一份

2022-11-18 11:24:05

SSAS事实表中的数据，有时候会因为一对多或多对多关系发生复制变成多份，如下图所示：

图1

我们可以从上面图片中看到，在这个例子中，有三个事实表Fact_People_Money（此表用字段Money记录了每个人的薪水，比如上图中就记录了PeopleID为1的人的薪水为1000）, Fact_PeopleVehicle（此表为人车关系表，记录了人和车之间的多对多关系，比如上图中就记录了PeopleID为1的张三拥有三辆车，分别为宝马、大众和奔驰）, Fact_Vehicle_Color（此表为车与颜色关系表，记录了车与颜色之间的多对多关系，比如上图中就记录了宝马、奔驰和大众三辆车都是红色）, 另外有三个维度表DIM_People（此表记录人的数据信息，比如上图中记录了ID为1的张三）, DIM_Vehicle（此表记录车的数据信息，比如上图中记录了ID为1、2、3的三辆车宝马、大众、奔驰）， DIM_VehicleColor（此表记录车颜色的信息，比如上图中记录了ID为1的红色）

这三个事实表和三个维度表一共组成了两个Cube中的多对多关系：

多对多1：由于事实表Fact_People_Money和事实表Fact_PeopleVehicle通过维度表DIM_People连接是一对多关系，维度表DIM_Vehicle和事实表Fact_PeopleVehicle之间也是一对多关系，所以事实表Fact_People_Money和维度表DIM_Vehicle在Cube中可以设置为多对多关系，其中中间事实表是Fact_PeopleVehicle。
多对多2：由于从多对多1关系中我们知道了维度表DIM_Vehicle和事实表Fact_People_Money之间是多对多关系，而维度表DIM_Vehicle和维度表DIM_VehicleColor之间通过事实表Fact_Vehicle_Color关联也是多对多关系，因此我们可以在Cube中将维度DIM_VehicleColor和事实表Fact_People_Money设置为多对多关系，其中中间事实表是Fact_Vehicle_Color。

在Cube中我们可以设置上面图中的维度关系如下：

图2

那么现在问题来了，在上面的图1中我们看到事实表Fact_People_Money中PeopleID为1、Money为1000的记录，在事实表Fact_PeopleVehicle中对应了三条记录分别为宝马、大众和奔驰。而从事实表Fact_Vehicle_Color的数据中我们可以知道宝马、大众和奔驰三辆车都对应维度表DIM_VehicleColor中ID为1的记录，所以这三辆车都是红色。那么直接通过Sql语句将DIM_VehicleColor inner join Fact_Vehicle_Color inner join DIM_Vehicle inner join Fact_PeopleVehicle inner join DIM_People inner join Fact_People_Money后，我们将得到三条记录，如下Sql语句所示：

 select *

 from [dbo].[Fact_People_Money] inner join [dbo].[DIM_People] on [Fact_People_Money].DIM_PeopleID=[DIM_People].ID

 inner join [dbo].[Fact_PeopleVehicle] on [DIM_People].ID=[Fact_PeopleVehicle].DIM_PeopleID

 inner join [dbo].[DIM_Vehcile] on [Fact_PeopleVehicle].DIM_VehicleID=[DIM_Vehcile].ID

 inner join [dbo].[Fact_Vehicle_Color] on [DIM_Vehcile].ID=[Fact_Vehicle_Color].DIM_VehicleID

 inner join [dbo].[DIM_VehicleColor] on [Fact_Vehicle_Color].DIM_VehicleColorID=[DIM_VehicleColor].ID

 where [DIM_VehicleColor].VehicleColor=N'Red'

图3

执行上面的Sql语句得到了三条记录，表示的是拥有红色车的人，其实这三条记录都是同一个人也就是DIM_People 维度表中的张三，但是由于张三拥有三辆车，所以将所有表 inner join 之后得到了三条记录，如果直接聚合这三条记录然后sum事实表Fact_People_Money中的Money字段，将会得到拥有红色车的人薪水总共是3000，如下面语句和图片所示：

 select sum([Fact_People_Money].[Money]) as [TotalMoney]

 from [dbo].[Fact_People_Money] inner join [dbo].[DIM_People] on [Fact_People_Money].DIM_PeopleID=[DIM_People].ID

 inner join [dbo].[Fact_PeopleVehicle] on [DIM_People].ID=[Fact_PeopleVehicle].DIM_PeopleID

 inner join [dbo].[DIM_Vehcile] on [Fact_PeopleVehicle].DIM_VehicleID=[DIM_Vehcile].ID

 inner join [dbo].[Fact_Vehicle_Color] on [DIM_Vehcile].ID=[Fact_Vehicle_Color].DIM_VehicleID

 inner join [dbo].[DIM_VehicleColor] on [Fact_Vehicle_Color].DIM_VehicleColorID=[DIM_VehicleColor].ID

 where [DIM_VehicleColor].VehicleColor=N'Red'

图4

但很明显这是错误的，因为这3000其实是将同一个人张三的薪水乘以了三倍得到的，而从事实表Fact_People_Money中我们可以知道张三的薪水是1000，所以像这样单纯的将所有表inner join起来聚合算拥有红色车的人的薪水总和是不正确的。

那么我们可以从最上面图1中看到Cube中多对多2这个关系，将维度DIM_VehicleColor和事实表Fact_People_Money连接起来了，为多对多维度关系，那么如果用MDX语句，将维度DIM_VehicleColor中的红色去切割事实表Fact_People_Money中的度量值Money，来计算Cube中拥有红色车的人的薪水总和该是多少，会是什么结果呢？ MDX语句和执行结果如下所示：

select [Measures].[Money] on 0,

non empty [DIM Vehicle Color].[Vehicle Color].&[Red] on 1

from [Local DW]

图5

可以看到Cube并没有像前面的Sql一样将拥有红色车的人张三的薪水乘以了三变为3000，而是正确的得到了结果1000。说明Cube在计算多对多和一对多关系的时候，考虑到了事实表数据实际可能会翻倍的情况，根据事实表Fact_People_Money中的PeopleID字段做了distinct处理，最终得到了正确的结果，相当于如下Sql语句：

 select sum([Money]) as [TotalMoney]

 from

 (

     select distinct  [Fact_People_Money].DIM_PeopleID,[Fact_People_Money].[Money]

     from [dbo].[Fact_People_Money] inner join [dbo].[DIM_People] on [Fact_People_Money].DIM_PeopleID=[DIM_People].ID

     inner join [dbo].[Fact_PeopleVehicle] on [DIM_People].ID=[Fact_PeopleVehicle].DIM_PeopleID

     inner join [dbo].[DIM_Vehcile] on [Fact_PeopleVehicle].DIM_VehicleID=[DIM_Vehcile].ID

     inner join [dbo].[Fact_Vehicle_Color] on [DIM_Vehcile].ID=[Fact_Vehicle_Color].DIM_VehicleID

     inner join [dbo].[DIM_VehicleColor] on [Fact_Vehicle_Color].DIM_VehicleColorID=[DIM_VehicleColor].ID

     where [DIM_VehicleColor].VehicleColor=N'Red'

 ) as t

码农公寓

相关文章