Kimball维度建模与Inmon范式建模

发表于 更新于 分类于

近来感觉自己对于建模这块虽然看了一些书,也实际做过模型,但是没有做到很好的将看到的知识归纳总结起来,不能形成一个标准的架构,仍需学习

维度建模

1
2
3
4
Kimball维度建模是以需求为导向,将已有数据按需求拆分成不同的表需求
数据表分为维度表-事实表两类
数据源利用ETL转化为事实表和维度表导入数据集市
以星型模型/雪花模型/星座模型构建数据仓库

事实表

1
2
3
必然存在的一些数据,采集的日志文件,订单表等
一堆主键的集合,每个主键对应维表中的一条记录,客观存在
根据主键确定需要使用的数据

维度表

1
2
维度可以看做所分析数据的一个量,以一个合适的角度创建的表
像时间,地域,终端,用户等角度

维度建模模式

1
2
3
4
5
6
星型模式
    以事实表为中心,所有的维度表直接连在事实表上
雪花模式
    维表可以拥有其他的维表,一般不使用这种模式,单事实表可以是这种情况
星座模式
    多张事实表之间可以共享某些维度表,一般是这种模式进行建模

怎么维度建模

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
选择业务过程
    维度建模紧贴业务,必须以业务为根基进行建模
    在整个业务流程中选取我们需要建模的业务
    根据运营提供的需求以及日后的易扩展性进行选择业务
    像电商行业分为商家,买家,平台,客服,需求是订单量,订单人数,购买情况等
    那么业务过程就选择商家-买家的数据

声明粒度
    一个买家对应一个买家ID,一个身份证号,多个手机号,多个收货地址,多个商家ID
    那用户粒度就是身份证粒度,更细的粒度有手机号粒度,商家粒度,存在一对一的就是
    同一个事实表必须具有相同的粒度,不要混用多种不同的粒度
    在给定业务过程获取数据时,从原子粒度开始也就是最细粒度开始设计,原子粒度可以承受无法预期的用户查询
    这里,太细粒度查询可能较差,所以需求明确时针对需求进行上卷汇总粒度,需求不明确,建立原子粒度
    
确认维度
    维度是作业业务分析的入口和描述性标识
    如果一列是对具体值的描述,是一个文本常量,某一约束和行标识的参与者,这一般是维度属性
    确保维度表中不能出现重复数据,维度主键唯一

确认事实
    事实表是用来度量的,基本上都是数量值表示,每行对应一个度量
    每行中的数据是一个特定级别的细节数据,也就是粒度
    同一事实表中所有度量必须具有相同的粒度
    最实用的事实就是数值类型和可加类事实

电商实例
源数据
用户信息表
用户ID	昵称	姓名	性别	联系方式	地区	用户等级ID	创建时间	修改时间	是否注销
1001	小忆控	张翠花	女	15034951345	310	2	2021/4/3 13:11	2021/4/4 15:16	0
1002	池木	李建国	男	17834686673	311	1	2021/4/3 14:11	2021/4/4 15:26	0
1003	君勿笑	刘素芬	女	15687876464	312	1	2021/4/3 15:11	2021/4/4 16:16	0
1004	原野	王富贵	男	15013145210	313	3	2021/4/3 15:23	2021/4/4 17:16	1

城市信息表
城市ID	省	市
0310	河北	邯郸
0311	河北	石家庄
0312	河北	保定
0313	河北	张家口

用户等级表
用户等级ID	价值属性
1	重要价值用户
2	一般价值用户
3	一般发展用户

用户订单表
订单ID	用户ID	订单状态	商品ID	商品总额	下单时间	支付金额	支付类型
OR11001	1001	未支付	CO31243	122.00	2021-04-04 10:12:05	0.00	
OR11002	1002	已支付	CO54353	86.50	2021-04-04 11:12:05	86.50	支付宝
OR11003	1003	已支付	CO43253	46.00	2021-04-04 14:16:05	0.00	助力免费
OR11004	1002	已支付	CO34235	210.00	2021-04-04 11:12:05	210.00	支付宝

建模后
支付成功订单事实表
订单ID	用户ID	商品ID	商品总额	下单时间	支付金额	支付类型
OR11002	1002	CO54353	86.50	2021-04-04 11:12:05	86.50	支付宝
OR11003	1003	CO43253	46.00	2021-04-04 14:16:05	0.00	助力免费
OR11004	1002	CO34235	210.00	2021-04-04 11:12:05	210.00	支付宝

用户维度表
用户ID	姓名	性别	联系方式	地区	用户等级ID	创建时间	修改时间
1001	张翠花	女	15034951345	0310	2	2021-04-03 13:11:01	2021-04-04 15:16:00
1002	李建国	男	17834686673	0311	1	2021-04-03 14:11:01	2021-04-04 15:26:01
1003	刘素芬	女	15687876464	0312	1	2021-04-03 15:11:01	2021-04-04 16:16:00

城市信息维度表
城市ID	省	市
0310	河北	邯郸
0311	河北	石家庄
0312	河北	保定
0313	河北	张家口

用户等级维度表
用户等级ID	价值属性
1	重要价值用户
2	一般价值用户
3	一般发展用户

事实表种类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
事务事实表  
表中的一行对应空间或时间上某点的度量事件
就是一行数据中必须有度量字段,什么是度量,就是指标,比如说销售金额,销售数量等这些可加的或者半可加就是度量值
另一点就是事务事实表都包含一个与维度表关联的外键
并且度量值必须和事务粒度保持一致

周期快照事实表
顾名思义,周期事实表就是每行都带有时间值字段,代表周期,通常时间值都是标准周期,如某一天，某周，某月等
粒度是周期,而不是个体的事务,也就是说一个周期快照事实表中数据可以是多个事实,但是它们都属于某个周期内.

累计快照事实表
周期快照事实表是单个周期内数据,而累计快照事实表是由多个周期数据组成,每行汇总了过程开始到结束之间的度量.
每行数据相当于管道或工作流,有事件的起点,过程,终点,并且每个关键步骤都包含日期字段.
如订单数据,累计快照事实表的一行就是一个订单,
当订单产生时插入一行,当订单发生变化时,这行就被修改

无事实的事实表
我们以上讨论的事实表度量都是数字化的,当然实际应用中绝大多数都是数字化的度量,但是也可能会有少量的没有数字化的值但是还很有价值的字段,无事实的事实表就是为这种数据准备的,利用这种事实表可以分析发生了什么

聚集事实表
聚集,就是对原子粒度的数据进行简单的聚合操作,目的就是为了提高查询性能
如我们需求是查询全国所有门店的总销售额,我们原子粒度的事实表中每行是每个分店每个商品的销售额,
聚集事实表就可以先聚合每个分店的总销售额,这样汇总所有门店的销售额时计算的数据量就会小很多

合并事实表
这种事实表遵循一个原则,就是相同粒度,数据可以来自多个过程,但是只要它们属于相同粒度,就可以合并为一个事实表,这类事实表特别适合经常需要共同分析的多过程度量

度量和粒度

1
2
3
4
5
6
7
8
9
10
11
12
什么是度量
    (可累加)商品的销售额,这种累加可以得到商家的总销售额
    (数值型)测量的身高,这种直接累加没有意义,但是求均值是有意义的
    
什么是粒度
    (原子粒度,已经没有办法再分)订单表中每个商品的条目
    (高粒度)一天一个商家一个商品的销售总额
    (高粒度)一天一个商家一个买家的消费总额
    像上面两种高粒度需求,在事实表建立时,粒度就必须到商品级别
    如果需求不需要到商品级
    (高粒度)一天一个商家一个买家的订单总额
    这样我们的事实表设计可以是以订单为粒度,订单下的商品条目就不需要关注

维表技术

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
维度表结构
    单一主键列,主键不单一,和事实表关联会出现数据发散,结果错误

跨表钻取
    上卷,下钻
    
退化维度
    将维度退回事实表中,有些维度除了主键没有其他内容,一般会退回事实表,减少关联次数

多层次维度
    像日期维度的天,周,月,年,有些维度存在不同的层次

维度表空值属性
    一般都需要进行描述性字符串替代空值

日历日期维度
    维度主键为YYYYMMDD格式数据,可以参考日历的节假日信息,这就是典型的日历日期维度

范式建模

1
2
以数据源头为导向,一步步获取尽量符合逾期的数据,会更加强调数据的清洗工作
将数据抽取为实体-关系模型,不强调事实表和维度表的概念

怎么范式建模

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
依旧是电商列子
源数据
用户信息表
城市信息表
用户等级表
用户订单表

建模后
用户实体表
用户ID	姓名	性别	联系方式	地区	用户等级ID	创建时间	修改时间
1001	张翠花	女	15034951345	0310	2	2021-04-03 13:11:01	2021-04-04 15:16:00
1002	李建国	男	17834686673	0311	1	2021-04-03 14:11:01	2021-04-04 15:26:01
1003	刘素芬	女	15687876464	0312	1	2021-04-03 15:11:01	2021-04-04 16:16:00

支付成功订单实体表
订单ID	用户ID	商品ID	商品总额	下单时间	支付金额	支付类型
OR11002	1002	CO54353	86.50	2021-04-04 11:12:05	86.50	支付宝
OR11003	1003	CO43253	46.00	2021-04-04 14:16:05	0.00	助力免费
OR11004	1002	CO34235	210.00	2021-04-04 11:12:05	210.00	支付宝

城市信息实体表
城市ID	省	市
0310	河北	邯郸
0311	河北	石家庄
0312	河北	保定
0313	河北	张家口

订单与用户关系表
订单ID	用户ID
OR11002	1002
OR11003	1003
OR11004	1002

用户与城市信息关系表
用户ID	城市ID
1001	0310
1002	0311
1003	0312

用户与用户等级关系表
用户ID	用户等级ID
1001	2
1002	1
1003	1