sql - 如何最好地处理缓慢变化的维度(SCD2)中的历史数据更改

Question

表格:

我工作的公司的尺寸变化缓慢(员工数据)，已使用Kimball方法进行了存储。包含此数据的维度表具有一个主键(int identity employee_key，在其他表中用作替代)，一个自然键(employee_id)，有效日期范围(valid_date和invalid_date)以及随时间跟踪的各种SCD1和SCD2数据元素。这是一个简化的示例:

employee_key | employee_id | valid_date | invalid_date | employee_name | employee_role
1            | 1001        | 1/1/2015   | 6/1/2015     | Bob           | DBA
2            | 1001        | 6/2/2015   | NULL         | Bob           | Developer
3            | 1002        | 1/1/2015   | NULL         | Jill          | DBA

在上面的示例中， employee_key是主键(代理)，而 employee_id是自然键。希望其他值可以自我解释。该表反射(reflect)出:

Bob是一名DBA，从2015年1月1日开始到2015年6月1日结束。

Bob是一位开发人员，始于2015年6月2日，目前担任该职位。

Jill是从2015年1月1日开始的DBA，目前担任该角色。

现在，我们还有大量的事实表引用了该维度。一个这样的事实表包含员工记录的所有时间，并且细化到一天。我们实际上并不关心这些表的结构，只是它们使用代理键链接到我们的员工维度，并且它们通常包含很多行(介于10M-200M之间)。这是包含已记录时间的事实表的示例:

calendar_dt | employee_id | employee_key | time_code | hours
1/1/2015    | 1001        | 1            | 1234      | 2.25
1/1/2015    | 1001        | 1            | 21        | 3.50
1/2/2015    | 1001        | 1            | 21        | 8.00
...
6/1/2015    | 1001        | 1            | 21        | 4.00  

通过代理键 employee_key链接到员工维度具有重要的商业目的-无需使用 BETWEEN运算符即可进行准确的历史报告，而无需进行昂贵的联接。例如，可以说鲍勃在2015年6月1日记录的时间归因于他的DBA角色，鲍勃在2015年2月6日记录的时间归因于他的开发者角色。

据我所知，这是一个有点标准的Kimball实现。

问题:

此实现不能很好地处理数据校正。假设在前面的示例中，HR告诉我们Bob的有效日期范围为2015年5月1日至2015年6月1日，他转为分析师角色，但他们未能将其输入到系统中。这给我们带来了一个主要问题:我们需要将 employee_key = 1的行拆分为有效日期/无效日期的两行。此外，我们需要找到现在错误地引用 employee_key = 1的所有位置并进行更新。这里是问题:

我们需要对许多巨大的表进行昂贵的更新操作。每次需要进行更正时，我们都无法承担此责任。

维度行拆分需要手动完成，使表处于数据输入错误或有效/无效日期范围重叠的风险。

拆分行违反了一条重要规则:主键是不可变的，一旦分配就永远不会更改。

解决方案:

我可以想到许多解决此问题的方法，但没有一种是优雅的:

承担更新代理 key 数据的噩梦。也许可以按正常时间表进行更正，从而减少了我们运行此更新所需的次数。

将“员工维度”表转换为“每员工每天行”表。这样做的好处是允许对employee_id和calendar_dt进行自然键联接。它还使该键不可变，并允许识别适当的替代键值，而无需在尺寸表中查找它。无论维度表如何更改，事实表将始终引用正确的行。这具有将我们的100,000行维表转换为20M第一行的主要缺点。

还有什么其他解决方案？我不可能是唯一遇到此问题的人...救救我!

警告:

我们假设数据将永远不需要时间元素(粒度将始终处于日级别)。

我们假设employee_id值永远不会改变(是的，我知道这是一个危险的假设)。

Answer 1

您的DW有一个要求:“可以随时追溯更改员工信息”，因此您的DW设计必须适应这一点。

解决这个问题的方法可能很多，但是想到的最简单的方法(对于类似的情况已经为我工作了)是引入一个新的类型2集成表:master_employee_time。该表将仅使用业务 key 维护原始时间记录数据的版本历史记录。

employee_time_key | employee_id | valid_date | invalid_date | time date | time code | hours
1                 | 1001        | 1/1/2015   | NULL         | 1/1/2015  | 1234      | 2.25
2                 | 1001        | 1/1/2015   | NULL         | 1/1/2015  | 21        | 3.5
3                 | 1001        | 1/2/2015   | NULL         | 1/2/2015  | 21        | 8

注意:此表可能需要特殊的更新条件，具体取决于您可以访问多少时间表数据，也许将type2更改限制为过去一年的time_dates，然后根据需要手动处理任何较旧的更新。

拥有该表后，您可以通过将master_employee和master_employee_time与类似的内容组合在一起，来每次加载时重新创建当前的事实表

insert into fact_employee_time
select
  t.calendar_dt, e.employee_id, e.employee_key, t.time_code, t.hours 
from
  master_employee_time t
  inner join master_employee e on t.employee_id = e.employee_id
where
  --limit to 'current' time recordings
  t.invalid_date is null  
  and
  --get the employee record active for the time recordings day
  e.valid_date <= t.time_date  
  and
  (e.invalid_date is null OR e.invalid_date >t.time_date)

[评论]
这也使您能够在更新时间记录的同时，为那些不可避免的“为什么我的一月数字发生变化”的需求保留了可审核的历史记录，而这些需求来自那些要求“我必须能够改变历史数据”的人。 。
[/评论]