数据仓库和类型2 SCD和/或每日快照事实表

Question

我很难决定哪一个更适合我的方案。此场景中涉及的维度包括：

1. 雇员维数 ╔═════════════╦════════════╦════════╦════════╦═══════════╦══════════╦════════╗EmployeeKey║EmployeeID║DeptID║StartDate║EndDate║Active║╠═════════════╬════════════╬════════╬════════╬═══════════╬══════════╬════════╣║1║1║1║9000║20150901║20150930║FALSE║║2║2║3║5000║20150901║NULL║TRUE║╚═════════════╩════════════╩════════╩════════╩═══════════╩══════════╩════════╝║3║1║1║9500║20150930║NULL║真║
2. 部门维度 ╔═══════════════╦══════════════╦══════╦══════╦═══════════╦═════════╦════════╗║DepartmentKey DepartmentID║名称║Sepc║StartDate║EndDate║Active║╠═══════════════╬══════════════╬══════╬══════╬═══════════╬═════════╬════════╣║1║1║XXXX║AWK║20150901║NULL║真║║2║2║YYYY║AUTO║20150901║NULL║TRUE║║3╚═══════════════╩══════════════╩══════╩══════╩═══════════╩═════════╩════════╝║3║ZZZZ║AMD║20150901║NULL║真║║
3. 雇员实况表(每天)
4. 部门概况表(每日)

我的问题

1. 在了解部门维度包含活动的700万条记录和部门维度包含活动的50万条记录的情况下，实现每日快照事实表的正确和最充分的方法是什么？
2. 是否足够每天填充事实员工和事实部门并每天重复记录？

我正在使用Server 2014数据库，预计将使用OLAP

Answer 1

为什么你想每天对你的事实进行快照？事实应该有所有的数据。事实中的日期键应该足以处理基于日期的查询。

此外，我不建议将员工和维度作为事实--他们直视前方的维度。一个事实应该是衡量一些我在这里看不到的东西。围绕在某个部门工作的员工所做的一些流程/事务建立您的事实。将此事实链接到两个维度和日期维度。如果要保留事务中更改的历史记录，请启用维度SCD。

Answer 2

可能的布局是为每个维度都有一个SCD2表。

这里是一个部门的例子

​

注意，我添加了一个数值版本属性来唯一地定义版本的顺序。列VALIDFROM_DATE对应于用于构建维度的快照的有效性。通常，还会添加VALIDTO日期以优化访问--为打开(最后)版本提供一些虚拟的高值。

还请注意validfrom_date和start_date之间的设计区域。前者是您获得信息的时间戳(从快照)，后者是逻辑有效性。在2013年8月20日获得的示例中，将创建一个新部门的信息。第二个版本表示一个部门的重命名，第三个版本显示该部门于2015年9月1日关闭。

这样的维度通常通过ETL作业定期处理快照来维护，识别更改并生成维度。

从事实表(即具有事务引用部门的表)中，维度引用DEPARTMENTKEY和可选的有效日期(以匹配正确的版本)。

您还可以设置维度的当前视图(使用)。这很简单，可以通过视图或使用查询的物化视图来完成，如下所示：

 with dept as (
 select 
   DEPARTMENTKEY,  
   min(VALIDFROM_DATE) over (partition by DEPARTMENTKEY) created_date,
   VALIDFROM_DATE last_change_date, DEPARTMENTID, NAME,
   FIRST_VALUE(NAME) over (partition by DEPARTMENTKEY order by VERSION) as INITIAL_NAME,
   SEPC, START_DATE, END_DATE, IS_ACTIVE,
   row_number() over (partition by DEPARTMENTKEY order by VERSION desc) as rn
 from Department_history)
 select 
   DEPARTMENTKEY, CREATED_DATE, LAST_CHANGE_DATE, DEPARTMENTID, NAME, INITIAL_NAME, SEPC, START_DATE, END_DATE, IS_ACTIVE
 from dept
 where rn = 1
 order by DepartmentKey

您可以从历史表的所有日期中获益--请参阅属性CREATED_DATE和INITIAL_NAME (您可以以非常低的成本实现优雅的SCD3 (新列))。

​

​

更新有效到日期

如上所述，由于实际原因，历史维度offen实现了VALIDTO。此列在ETL作业中维护，每日快照的一个可能解决方案是将其设置为

• 开放版本的虚拟泻湖日
• 在下一个版本的VALIDTO日期之前的一天

请注意，还有其他选项，但此模式的最大优点是，您始终可以使用此谓词查询适当的版本。

 where to_date('01012015','ddmmyyyy') between validfrom_date and validto_date

我在这里使用以下查询添加VALIDTO日期

 create table department_history2 as 
 with dept as (
 select  
 DEPARTMENTKEY, VERSION, VALIDFROM_DATE, 
 lead(VALIDFROM_DATE-1,1,to_date('31122500','ddmmyyyy')) over (partition by DEPARTMENTKEY order by VERSION) as VALIDTO_DATE,
 DEPARTMENTID, NAME, SEPC, START_DATE, END_DATE, IS_ACTIVE
 from Department_history
 )
 select * from dept

更新查询每日计数

要从历史维度重构每日计数，您必须首先创建时间维度的相关部分(每天一条记录)，而不是将其加入历史维度。最后执行聚合。

这里是一个从30.8.2015开始的4天减压的例子。

 with snapshots as (
 select to_date('30082015','ddmmyyyy') -1 + rownum transaction_date from dual connect by level <= 4),
 deps_snaps as (
 select 
  transaction_date, DEPARTMENTKEY, VERSION, VALIDFROM_DATE, VALIDTO_DATE,      DEPARTMENTID, NAME, SEPC, START_DATE, END_DATE, IS_ACTIVE
 from   department_history2, snapshots
 where transaction_date between  validfrom_date and validto_date
 )
 -- aggregate
 select transaction_date, count(*) active_dept_count
 from deps_snaps
 where is_active = 'Y'
 group by transaction_date
 order by transaction_date;

 transaction_date     active_dept_count
 30.08.2015 00:00:00    2
 31.08.2015 00:00:00    2
 01.09.2015 00:00:00    1
 02.09.2015 00:00:00    1

这种方法的最大优点是，如果一个部门每3年更换一次(大公司的典型间隔)，维度的记录将不会增加5* 365倍，但在5年内只会增加大约两倍。