当维度数据发生变化时,有多种处理方式,一般会用缓慢变化维类型2来处理,也就是当维度属性发生变化时,新生成1行,同时添加 开始日期,结束日期 。
比如: 业务系统中的 用户表:
userId | mobile | regDate |
111 | 12345 | 2020/1/1 |
222 | 11111 | 2020/2/1 |
用户111在3月2号登录系统,修改手机号为 12222,那么缓慢变化维类型2来处理后,数据仓库中的 用户表:
userId | mobile | regDate | startDate | endDate |
111 | 12345 | 2020/1/1 | 2020/1/1 | 2020/3/1 |
222 | 11111 | 2020/2/1 | 2020/2/1 | 9999/12/31 |
111 | 22222 | 2020/1/1 | 2020/3/2 | 9999/12/31 |
可以看到用户111新增加了一条记录,开始日期和结束日期记录了这条数据生效日期。
1、要达到这种效果,有什么前提条件?
在网上很多讲数据仓库的文章,或者维度建模的书里没有提到,要用 缓慢变化维类型2来处理,至少业务系统里要记录了 用户表的变更日志 或者变更流水,比如 用户在某一天,可能手机号改了2次,那么这2次修改会对应2条日志 或 记录(比如:user_update_log表)。
如果在业务系统没有记录这种变化,如果有 mdm主数据管理系统,记录了这种变化情况,也是可以的。
但如果既没有在业务系统内记录变更流水,也没有mdm系统,那就比较尴尬,这种情况下只能从 主数据变更文档中提炼变化,生成变化流水,在进行后面的处理。
2、分析要求维度变化的粒度是什么?
上面讲的实际上是业务过程,用户可以修改自己的信息,完成业务操作后,业务系统会记录 某人在何时做了什么变化。
上面说到如果一天修改了2次,就会有2条记录,但实际分析的时候就按照天来看数据,不需要细分到一天中的上下午 或者分小时来看,所以 维度属性一天内变化多次,我们只取最后一次修改的值。
比如 :用户 111,在3月2号上午,把手机号从 12345 改成了 123456,到了下午又改成了 22222,那么在业务系统里这个用户的mobile是22222,而到了数据仓库中,这个用户的mobile也是22222,一天内的中间结果,我们不需要记录。
3、如何生成维度变化数据?
这里假设数据都是放到hive表中的,注意使用的是 hive中的sql语法(hql):
create table ods.user(
userid int,
mobile string,
regDate string,
comment '用户表'
partitioned by (dt string)
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as orc
location '/ods/user';
)
create table ods.user_update_log(
userid int,
mobile string,
regDate string
comment '用户更新日志表'
partitioned by (update_date string)
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as orc
location '/ods/user_update_log';
)
缓慢变化维度表(拉链表)
create table dim.user_arv(
userId int ,
mobile string,
regDate string,
startDate string,
endDate string
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as orc
location '/dim/user_arv';
)
通过如下的sql,生成 渐变维度表 user_arv
insert into dwd.user_arv
select *
from
(
select
d.userId,
d.mobile,
d.startDate,
case when d.endDate = '9999-12-31' then {$current_date}-1 else d.endDate end as endDate
from dwd.user_arv d
left join ods.user_update_log as o
on d.userId = o.userId
and b.userid is not null
and o.update_date = {$current_date}
union
select
d.userId,
d.mobile,
{$current_date} startDate, --生效日期
'9999-12-31' as endDate
from dwd.user_arv d
) t
4、那么维度表新增一条数据,对事实表有什么影响呢?
(1)对于传统数据仓库来说,用户表结构为:
ID,userId,mobile,regDate,startDate,endDate
其中 ID为新增加的代理键来作为主键,在业务系统中,当维度数据发生变化后,新增了一条数据, 之后业务系统中会记录ID值 作为外键,来关联 用户表。
但是这种方式,在实际处理时,会有一定的问题, 就是开发业务系统时,一般不会考虑后续数据仓库的建设要求,虽然会记录变更日志,但在用户表不会生成新的记录,用户表里永远是用户最新的信息。
那这种情况下,就要用下面的方法来处理。
(2)如果在系统不记录代理键ID,可以在etl里先把原始业务数据抽取过来后,按照如下方式生成事实表:
insert into dwd.order
select
u.ID, --作为外键,关联用户表
...
from ods.原始业务表 t
inner join 用户表 u
on t.业务日期 >= u.开始日期 and t.业务日期 <= u.结束日期
and t.userId = u.userId
(3)不增加代理键
表结构如下:
userId,mobile,regDate,startDate,endDate
不用增加ID作为代理键来作为新的主键,严格来讲主键就是 userId、startDate、endDate这3个字段的组合,在事实表中也还是用 userId 来关联用户表,但是需要增加关联条件:
select ...
from 事实表 t
inner join 用户表 u
on t.业务日期 >= u.开始日期 and t.业务日期 <= u.结束日期
and t.userId = u.userId
5、此外,业务系统中也可以使用类似的设计(不增加代理键的方式)
比如,数据从客户端抽取上来后,其中包括原始价格,但这个价格不是厂商的价格,所以,要按照抓取的 产品id+业务日期,在价格组数据中查找对应有效期内的价格,价格组的表结构如下:
prodId,price,startDate,endDate
可以按照这个方式做关联,取得产品的价格字段:
select
a.prodId,
b.price --匹配到的价格组
...
from 抓取数据 a
left join 价格组 b
on a.prodId = b.prodId
and a.业务日期 between b.startDate and b.endDate