数仓-维度模型之维度迟到问题处理详解

数仓-维度模型之维度迟到问题处理详解

摘要：在数据仓库项目中，从贴源层（ODS）更新到数据仓库层（DW）时，出现了拉链形式的维表数据更新不及时的情况，从而导致事实表中的该维度列值为空或旧值。需要根据维表数据更新的时间，将事实表中数据纠正。

关键词：数据仓库 离线数仓 维度迟到 退链 ODS DW 拉链表 缓慢变化 SCD2

问题示例

问题背景：数据源中有商品表、用户表和订单表，业务流程为从数据源加载数据到ODS层，之后在DW层进行维度建模，分为商品维度表（缓慢变化），用户维度表（缓慢变化），订单事实表（事务、增量）。

问题：业务上，商品苹果单价在2021-03-25由5更改为10，但是数据源中的商品表更新日期为2021-04-05，滞后于业务，检查发现之后，将数据源中的最近更新日期纠正为2021-03-25。在常规的数仓更新操作中，无法处理该错误，因此需要对数仓中的数据进行修正。

===========================================================================================

修正前表数据：

ODS层

商品表

商品id	商品名称	商品单价	最近更新日期
1	苹果	10	2021-04-05
2	香蕉	2	2021-03-04
3	葡萄	15	2021-03-04

用户表

用户id	用户名称	用户手机号	最近更新日期
1	张三	123	2021-01-04
2	李四	234	2021-01-04
3	梅有钱	345	2021-01-04
4	刘首富	456	2021-01-04

订单表

订单id	用户id	商品id	商品数量	订单日期
1	1	1	3	2021-03-10
2	2	1	4	2021-03-20
3	3	1	5	2021-04-03
4	1	1	6	2021-04-20

DW层

商品维度表

商品代理键	版本号	商品id	商品名称	商品单价	生效日期	失效日期
0	1	NULL	NULL	NULL	2021-01-01	9999-12-31
1	1	1	苹果	5	2021-01-01	2021-04-05
2	1	2	香蕉	2	2021-01-01	9999-12-31
3	1	3	葡萄	15	2021-01-01	9999-12-31
4	2	1	苹果	10	2021-04-05	9999-12-31

用户维度表

用户代理键	版本号	用户id	用户名称	用户手机号	生效日期	失效日期
0	1	NULL	NULL	NULL	2021-01-01	9999-12-31
1	1	1	张三	123	2021-01-04	9999-12-31
2	1	2	李四	234	2021-01-04	9999-12-31
3	1	3	梅有钱	345	2021-01-04	9999-12-31
4	1	4	刘首富	456	2021-01-04	9999-12-31

订单事实表

订单id	用户代理键	商品代理键	商品数量	订单日期
1	1	1	3	2021-03-10
2	2	1	4	2021-03-20
3	3	1	5	2021-04-03
4	1	1	6	2021-04-20

修正步骤：

经分析可得，需要修正的表为ODS层的商品表和DW层的商品维度表、订单事实表。

1.更新ODS层的商品表

按照常规的更新步骤，从数据源全量同步商品表，更新后数据如下：

苹果的最近更新日期得到更正。

商品表

商品id	商品名称	商品单价	最近更新日期
1	苹果	10	2021-03-25
2	香蕉	2	2021-03-04
3	葡萄	15	2021-03-04

2.回退DW层的订单事实表

订单事实表，通过商品代理键与商品维度表联表查询，对于涉及到的商品信息，删除订单日期大于2021-03-24的行，删除后数据如下：

订单id为3和4的订单行被删除。

订单事实表

订单id	用户代理键	商品代理键	商品数量	订单日期
1	1	1	3	2021-03-10
2	2	1	4	2021-03-20

3.回退DW层的商品维度表

商品维度表是拉链表，因此需要退链操作，将涉及到的商品信息回溯到错误发生前，即2021-03-24。

1> 商品维度表中，对于涉及到的商品信息，删除生效日期大于2021-03-24的行，删除后数据如下：

商品代理键为4的商品信息被删除。

商品维度表

商品代理键	版本号	商品id	商品名称	商品单价	当前是否有效	生效日期	失效日期
0	1	NULL	NULL	NULL	是	2021-01-01	9999-12-31
1	1	1	苹果	5	否	2021-01-01	2021-04-05
2	1	2	香蕉	2	是	2021-01-01	9999-12-31
3	1	3	葡萄	15	是	2021-01-01	9999-12-31

2>商品维度表中，对于涉及到的商品信息，将失效日期大于2021-03-24的行的失效日期置为9999-12-31，更新后数据如下：

商品代理键为2的商品信息失效日期被修改。

商品维度表

商品代理键	版本号	商品id	商品名称	商品单价	当前是否有效	生效日期	失效日期
0	1	NULL	NULL	NULL	是	2021-01-01	9999-12-31
1	1	1	苹果	5	是	2021-01-01	9999-12-31
2	1	2	香蕉	2	是	2021-01-01	9999-12-31
3	1	3	葡萄	15	是	2021-01-01	9999-12-31

4.更新DW层的商品维度表

在第3步中，商品维度表回退到了2021-03-24，在此重新更新，更新后数据如下：

商品维度表

商品代理键	版本号	商品id	商品名称	商品单价	当前是否有效	生效日期	失效日期
0	1	NULL	NULL	NULL	是	2021-01-01	9999-12-31
1	1	1	苹果	5	否	2021-01-01	2021-03-25
2	1	2	香蕉	2	是	2021-01-01	9999-12-31
3	1	3	葡萄	15	是	2021-01-01	9999-12-31
1234	2	1	苹果	10	是	2021-03-25	9999-12-31

5.更新DW层的订单事实表

在第2步中，订单事实表回退到了2021-03-24，在此重新（增量）插入涉及到的商品的2021-03-24之后的订单数据，更新后数据如下：

订单事实表

订单id	用户代理键	商品代理键	商品数量	订单日期
1	1	1	3	2021-03-10
2	2	1	4	2021-03-20
3	3	1234	5	2021-04-03
4	1	1234	6	2021-04-20

===========================================================================================

小结

以上为单一维度迟到的处理方式，当商品表和用户表都有迟到情况时，处理方式类似。另外，在此基础上，还有一种情况，事实表为“无事实的事实表”，且为拉链表，则需要在第2步对该事实表进行退链操作，在第5步之后，事实表更新的生效日期为前一天，因此需要将失效日期为今天的行的失效日期改为正确日期，并将生效日期为今天的行的生效日期改为正确日期。

注意：

1.涉及到手工修正的情况比较复杂，请务必在更新或删除时，加限制条件。如果无限制条件，则对所有数据进行回退或插入操作。

2.无事实的事实表是指，在某些情况下，需要记录某事物的状态变化，且变化字段较多但不适宜作为维度表时，可以建立事实表记录其状态变化。特点：字段不包含度量值；包含时间字段。例1，在人力项目中，将岗位、职务、企业等组合作为岗位状态维度，将用户信息作为维度，建立用于记录员工每天的岗位状态的事实表，每天记录一次，为缩减数据量，一般采用拉链表做处理，包含岗位状态代理键、员工信息代理键、生效时间、失效时间等字段。模型建立之后，即可查询任意日期时某员工的岗位状态/个人信息，或统计任意日期某岗位的人数占比等指标。例2，由于订单事实表只记录了有订单的情况，无法得知在促销期间有多少商品未出售，因此需要建立记录每日商品的事实表。当需要查询未出售商品是，将商品事实表和订单事实表关联，取差集，即可得出期间未售出的商品。