数据仓库-缓慢更改具有多对多关系的维度 [英] Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships
问题描述
举个例子，假设我有一个事实表，其中包含二维和一个度量
As an example, let's say I have a fact table with two dimensions and one measure
FactMoney表
ProjectKey int
ProjectKey int
PersonKey int
PersonKey int
现金金额
两个维度的定义如下:
DimProject(类型为0的尺寸-即静态)
ProjectKey int
ProjectKey int
ProjectName varchar(50)
ProjectName varchar(50)
DimPerson(类型2的尺寸变化缓慢)
PersonKey int
PersonKey int
PersonNaturalKey int
PersonNaturalKey int
PersonName varchar(50)
PersonName varchar(50)
EffectiveStartDate日期时间
EffectiveStartDate datetime
EffectiveEndDate日期时间
EffectiveEndDate datetime
当前位
到目前为止非常简单.现在，我将介绍人员类别"概念.
Pretty straightforward so far. Now I'll introduce a Person Category concept.
DimCategory
CategoryKey int
CategoryKey int
CategoryName varchar(50)
CategoryName varchar(50)
并在DimPerson和DimCategory之间建立M2M关系
And build an M2M relationship between DimPerson and DimCategory
BridgePersonCategory
PersonKey int
PersonKey int
CategoryKey int
CategoryKey int
所以-人们可以有1..n个类别.
So - people can have 1..n categories.
我的问题是-由于Person是一个变化缓慢的维度，当一个人的名字更改时，我们添加一个新的person行并更新我们的生效日期，并且它是当前标记，没什么大不了的.
My problem is - as Person is a slowly changing dimension, when a person's name changes, we add a new person row and update our effective dates and is current flags, no big deal.
但是我们如何处理此人的类别?每当出现新的人员版本时，我们是否需要向桥表添加更多行?
But what do we do with the person's categories? Do we need to add more rows to the bridge table every time a new person version pops up?
作为推论，如果一个人的类别发生变化，这是否意味着我们需要在person表中创建一个新行?
And as a corollary, if a person's categories change, does that mean we need to create a new row in the person table?
推荐答案
关于您的主要问题:我想说您需要在类别表中添加类别(可能是从老人行中复制它们).因此，您可以继续对处于新(更改)状态的人员进行分类.
About your main question: I would say that you need to add the categories in the category table (probably copying them from the old person row). So you can continue to classify the person in the new (changed) state.
关于类别更改:我宁愿不添加人员行，而是在类别表中添加初始有效期和到期日期.这样，每个类别都可以单独更改.但是您要注意不要进行时间点查询，因为这样可能会夸大类别
About a change of category: I would prefer to do not add a person row but add an initial-validity and an expiration date in the category table. In this way each category could be indipendently changed. But you need to be careful for not point-in-time query as you could overcount the categories
这篇关于数据仓库-缓慢更改具有多对多关系的维度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
