数据仓库1.3数仓分层建模

1、数仓分层

分层

数据集市与数据仓库的区别

数据集市：狭义ADS层； ⼴义上指DWD DWS ADS 从hadoop同步到RDS的数据

数仓命名规范

表命名

ODS层命名为ods_表名

DWD层命名为dwd_dim/fact_表名DWS层命名为dws_表名 DWT层命名为dwt_表名ADS层命名为ads_表名临时表命名为xxx_tmp

⽤户⾏为表，以log为后缀。

数据源_to_⽬标_db/log.sh

⽤户⾏为脚本以log为后缀；业务数据脚本以db为后缀。数量类型为bigint

⾦额类型为decimal(16, 2)，表⽰：16位有效数字，其中⼩数部分2位字符串(名字，描述信息等)类型为string主键外键类型为string时间戳类型为bigint脚本命名

数据源_to_⽬标_db/log.sh

⽤户⾏为脚本以log为后缀；业务数据脚本以db为后缀。表字段类型

数量类型为bigint

⾦额类型为decimal(16, 2)，表⽰：16位有效数字，其中⼩数部分2位字符串(名字，描述信息等)类型为string主键外键类型为string时间戳类型为bigint

2、数仓理论

1.范式理论

范式的定义：符合某⼀种级别的关系模式集合，表⽰⼀个关系内部各属性之间的联系的合理化程度。通俗地讲，范式可以理解为⼀张数据表的表结构，符合的标准级别、规范和要求。1）优点

采⽤范式，可以降低数据的冗余性。

为什么要降低数据冗余性？

⼗⼏年前，磁盘很贵，为了减少磁盘存储。

以前没有分布式系统，都是单机，只能增加磁盘，磁盘个数也是有限的⼀次修改，需要修改多个表，很难保证数据⼀致性3）缺点

范式的缺点是获取数据时，需要通过Join拼接出最后的数据。4）分类

⽬前业界范式有：第⼀范式(1NF)、第⼆范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。

函数依赖

完全函数依赖： 共同决定，任何单独⼀个推测不出来。

部分函数依赖： 只依赖于其中⼀个，⼀半传递函数依赖： a->b->c(c不能得到a)

三范式区别

第⼀范式：

1NF核⼼原则：属性不可切割； 商品| 数量 可切割

第⼆范式：

2NF核⼼原则： 不能存在部分函数依赖

　　联合主键(学号, 课名)，但姓名并不完全依赖于（学号，课名）；　　　　变成完全函数依赖即可

第三范式：

3NF不能存在传递函数依赖

学号->系名->系主任，但系主任不能推出学号； 　　把它拆开两张表

2. 关系建模与维度建模

数据处理⼤致可以分成两⼤类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应⽤，主要是基本的、⽇常的事务处理，例如银⾏交易。

OLAP是数据仓库系统的主要应⽤，⽀持复杂的分析操作，侧重决策⽀持，并且提供直观易懂的查询结果。⼆者的主要区别对⽐如下表所⽰。

对⽐属性读特性写特性使⽤场景数据表征数据规模OLTPOLAP每次查询只返回少量记录对⼤量记录进⾏汇总随机、低延时写⼊⽤户的输⼊批量导⼊⽤户，Java EE项⽬内部分析师，为决策提供⽀持最新数据状态随时间变化的历史状态GBTB到PB

关系建模

关系模型严格遵循第三范式（3NF），较为松散、零碎，物理表数量多，⽽数据冗余程度低。由于数据分布于众多的表中，这些数据可以更为灵活地

被应⽤，功能性较强。关系模型主

要应⽤与OLTP系统中，为了保证数据的⼀致性以及避免冗余，所以⼤部分业务系统的表都是遵循第三范式的。

维度模型主要应⽤于OLAP系统中，通常以某⼀个事实表为中⼼进⾏表的组织，主要⾯向业务，特征是可能存在数据的冗余，但是能⽅便的得到数据。

关系模型虽然冗余少，但是在⼤规模数据，跨表分析统计查询过程中，会造成多表关联，这会⼤⼤降低执⾏效率。通常我们采⽤维度模型建模，把相关各种表整理成两种：事实表和维度表两种。

维度建模

在维度建模的基础上⼜分为三种模型：星型模型、雪花模型、星座模型。

星型模型：(快)

　　只有1层，数据表中只有1个维度表； 星型模式的核⼼是⼀个⼤的中⼼表（事实表），⼀组⼩的附属表（维表）。

雪花模型：(灵活)

　　多级多个维度表，⽐较靠近3NF； 雪花模式是星型模式的扩展，其中某些维表被规范化，进⼀步分解到附加表（维表）中。星座模型：（可能是雪花也可能是星型）

　　数据仓库由多个主题构成，包含多个事实表，⽽维表是公共的，可以共享，这种模式可以看做星型模式的汇集，因⽽称作星系模式或者事实星座模式。

　　多个事实表(⼀个项⽬中⼤概5-6个)　　事实表-维度(共享)-事实表

3.维度表和事实表

维度表

维度表：⼀般是对事实的描述信息。每⼀张维表对应现实世界中的⼀个对象或者概念。 例如：⽤户、商品、⽇期、地区等。维表的特征：

维表的范围很宽（具有多个属性、列⽐较多）跟事实表相⽐，⾏数相对较⼩：通常< 10万条内容相对固定：编码表时间维度表：

⽇期ID

2020-01-012020-01-022020-01-032020-01-042020-01-05day of weekday of year21324365季度

11111节假⽇元旦⽆⽆⽆⽆

事实表

事实表中的每⾏数据代表⼀个业务事件（下单、⽀付、退款、评价等）。“事实”这个术语表⽰的是业务事件的度量值（可统计次数、个数、⾦额等）。

维度表：时间、⽤户、商品、商家。事实表：250块钱、⼀瓶

每⼀个事实表的⾏包括：具有可加性的数值型的度量值、与维表相连接的外键、通常具有两个和两个以上的外键、外键之间表⽰维表之间多对多的关系。

事实表的特征：

⾮常的⼤

内容相对的窄：列数较少（主要是外键id和度量值）经常发⽣变化，每天会新增加很多。

1）事务型事实表

以每个事务或事件为单位，⼀般指随着业务发⽣不断产⽣的数据。特点是⼀旦发⽣不会再变化；⽐如，交易流⽔，操作⽇志，出库⼊库记录等。例如⼀个销售订单记录，⼀笔⽀付记录等，作为事实表⾥的⼀⾏数

据。⼀旦事务被提交，事实表数据被插⼊，数据就不再更改，其更新⽅式为增量更新。　　每⽇新增 订单详情表（⽤户和商品信息）、⽀付流⽔表(增量)

同步策略（因为数据不会变化，⽽且数据量巨⼤，所以每天只同步新增数据即可，所以可以做成每⽇增量表，即每⽇创建⼀个分区存储。）

2）周期型快照事实表

周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每⽉的销售额，或每⽉的账户余额等。例如购物车，有加减商品，随时都有可能变化，但是我们更关⼼每天结束时这⾥⾯有多少商品，⽅便我们后期统计分析。随着业务的发⽣（时间）⽽变化

　　订单表 （订单状态）--> 新增和变化

这类表从数据量的⾓度，存每⽇全量的话，数据量太⼤，冗余也太⼤。如果⽤每⽇增量的话⽆法反应数据变化。

每⽇新增及变化量可以⽤，包括了当⽇的新增和修改。⼀般来说这个表，⾜够计算⼤部分当⽇数据的。但是这种依然⽆法解决能够得到某⼀个历史时间点（时间切⽚）的切⽚数据。 所以要⽤利⽤每⽇新增和变化表，制作⼀张拉链表，以⽅便的取到某个时间切⽚的快照数据。所以我们需要得到每⽇新增及变化量。

3）累积型快照事实表

累计快照事实表⽤于跟踪业务事实的变化。例如，数据仓库中可能需要累积或者存储订单从下订单开始，到订单商品被打包、运输、和签收的各个业务阶段的时间点数据来跟踪订单声明周期的进展情况。当这个业务过程进⾏时，事实表的记录也要不断更新。

订单id ⽤户id 下单时间打包时间发货时间签收时间3-83-83-93-10订单⾦额

①实体表：⽤户表、商品表--->全量；⼀个个实实在在的个体；

　　⼀般是指⼀个现实存在的业务对象，⽐如⽤户，商品，商家，销售员等等。

　（同步策略）实体表数据量⽐较⼩：通常可以做每⽇全量，就是每天存⼀份完整数据。即每⽇全量。

②维度表(码表--编号的解释表)：对应的业务状态；商品⼀级分类表、商品⼆级分类表、商品三级分类表.等都是；全量表　　⽐如地区表，订单状态，⽀付⽅式，审批状态，商品分类等等

同步策略（维度表数据量⽐较⼩：通常可以做每⽇全量，就是每天存⼀份完整数据。即每⽇全量。

说明：1）针对可能会有变化的状态数据可以存储每⽇全量。2）没变化的客观世界的维度（⽐如性别，地区，民族，政治成分，鞋⼦尺码）可以就存⼀份固定值。)

③事务型事实表；⼀般指随着业务发⽣不断产⽣的数据。特点是⼀旦发⽣不会再变化；⽐如，交易流⽔，操作⽇志，出库⼊库记录等。　　每⽇新增 订单详情表（⽤户和商品信息）、⽀付流⽔表(增量)

同步策略（因为数据不会变化，⽽且数据量巨⼤，所以每天只同步新增数据即可，所以可以做成每⽇增量表，即每⽇创建⼀个分区存储。）

④周期型事实表：随着业务的发⽣（时间）⽽变化　　订单表 （订单状态）--> 新增和变化

这类表从数据量的⾓度，存每⽇全量的话，数据量太⼤，冗余也太⼤。如果⽤每⽇增量的话⽆法反应数据变化。

每⽇新增及变化量可以⽤，包括了当⽇的新增和修改。⼀般来说这个表，⾜够计算⼤部分当⽇数据的。但是这种依然⽆法解决能够得到某⼀个历史时间点（时间切⽚）的切⽚数据。 所以要⽤利⽤每⽇新增和变化表，制作⼀张拉链表，以⽅便的取到某个时间切⽚的快照数据。所以我们需要得到每⽇新增及变化量。

4.数据仓库建模

数仓建模经典书籍：

《数据仓库⼯具箱--第3版》　　　　　　---维度建模权威指南

1.选择业务过程

　　星型模型(⼀层维度表围绕事实)　　左事实表 + 维度表 2.声明粒度

　　dwd层确定维度和事实 3.确定维度4.确定事实

dws层维度退化

dwt主题层

ads指标结果层

ODS层

1）HDFS⽤户⾏为数据

2）HDFS业务数据

3）针对HDFS上的⽤户⾏为数据和业务数据，我们如何规划处理？（1）保持数据原貌不做任何修改，起到备份数据的作⽤。

（2）数据采⽤压缩，减少磁盘存储空间（例如：原始数据100G，可以压缩到10G左右）（3）创建分区表，防⽌后续的全表扫描

DWD层

DWD层需构建维度模型，⼀般采⽤星型模型，呈现的状态⼀般为星座模型。维度建模⼀般按照以下四个步骤：

选择业务过程→声明粒度→确认维度→确认事实（1）选择业务过程

在业务系统中，挑选感兴趣的业务线，⽐如下单业务，⽀付业务，退款业务，物流业务，⼀条业务线对应⼀张事实表。如果是中⼩公司，尽量把所有业务过程都选择。

如果是⼤公司（1000多张表），选择和需求相关的业务线。（2）声明粒度

数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。

声明粒度意味着精确定义事实表中的⼀⾏数据表⽰什么，应该尽可能选择最⼩粒度，以此来应各种各样的需求。典型的粒度声明如下：

订单当中的每个商品项作为下单事实表中的⼀⾏，粒度为每次。每周的订单次数作为⼀⾏，粒度为每周。每⽉的订单次数作为⼀⾏，粒度为每⽉。

如果在DWD层粒度就是每周或者每⽉，那么后续就没有办法统计细粒度的指标了。所以建议采⽤最⼩粒度。（3）确定维度

维度的主要作⽤是描述业务是事实，主要表⽰的是“谁，何处，何时”等信息。

确定维度的原则是：后续需求中是否要分析相关维度的指标。例如，需要统计，什么时间下的订单多，哪个地区下的订单多，哪个⽤户下的订单多。需要确定的维度就包括：时间维度、地区维度、⽤户维度。维度表：需要根据维度建模中的星型模型原则进⾏维度退化。（4）确定事实

此处的“事实”⼀词，指的是业务中的度量值（次数、个数、件数、⾦额，可以进⾏累加），例如订单⾦额、下单次数等。

在DWD层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。事实表可做适当的宽表化处理。事实表和维度表的关联⽐较灵活，但是为了应对更复杂的业务需求，可以将能关联上的表尽量关联上。如何判断是否能够关联上呢？在业务表关系图中，只要两张表能通过中间表能够关联上，就说明能关联上。

订单

订单详情⽀付加购收藏评价退款

优惠券领⽤时间√√√√√√√√⽤户√√√√√√√√地区√√√ 商品 √ √√√√ 优惠券 √活动√ 编码 度量值件数/⾦额件数/⾦额⾦额

件数/⾦额个数个数

件数/⾦额个数

⾄此，数据仓库的维度建模已经完毕，DWD层是以业务过程为驱动。DWS层、DWT层和ADS层都是以需求为驱动，和维度建模已经没有关系了。

DWS和DWT都是建宽表，按照主题去建表。主题相当于观察问题的⾓度。对应着维度表。

DWS层

DWS层统计各个主题对象的当天⾏为，服务于DWT层的主题宽表。

（1）问题引出：两个需求，统计每个省份订单的个数、统计每个省份订单的总⾦额

（2）处理办法：都是将省份表和订单表进⾏join，group by省份，然后计算。相当于类似的需求重复计算了两次。 那怎么设计能避免重复计算呢？

地区宽表的字段设计为：下单次数、下单⾦额、⽀付次数、⽀付⾦额等。只需要和每个事实表⼀次join。（3）总结：

需要建哪些表：以维度为基准，去关联对应多个事实表

宽表⾥⾯的字段：是站在不同维度的⾓度去看事实表，重点关注事实表聚合后的度量值。

（4）DWS层宽表包括：每⽇设备⾏为、每⽇会员⾏为、每⽇商品⾏为、每⽇活动统计、每⽇地区统计。

DWT层

DWT层统计各个主题对象的累积⾏为。

（1）需要建哪些表：和DWS层⼀样。以维度为基准，去关联对应多个事实表

（2）宽表⾥⾯的字段：我们站在维度表的⾓度去看事实表，重点关注事实表度量值的累积值、事实表⾏为的⾸次和末次时间。

例如，订单事实表的度量值是下单次数、下单⾦额。订单事实表的⾏为是下单。我们站在⽤户维度表的⾓度去看订单事实表，重点关注订单事实表⾄今的累积下单次数、累积下单⾦额和某时间段内的累积次数、累积⾦额，以及关注下单⾏为的⾸次时间和末次时间。

（4）DWS层宽表包括：每⽇设备⾏为、每⽇会员⾏为、每⽇商品⾏为、每⽇活动统计、每⽇地区统计。

ADS层

对电商系统各⼤主题指标分别进⾏分析。