离线数仓构建方法指导论(一)

数据仓库之父 Bill Inmon对数据仓库做了定义——面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。

常见的有范式建模、维度建模、Data Vault及实体建模等，每种方法从本质上将是从不同的角度看待业务中的问题。

1、关系型数据库三范式详解

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)。第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

2、范式建模理论基础详解

范式建模法 (Third Normal Form，3NF) 是构建数据模型常用的一个方法，该方法主要由lnmon所提倡，主要解决关系型数据库的数据存储，利用的一种技术层面上的方法。常用于关系型数据库中。

范式,是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则，而在关系型数据库中这种规则就是范式，这一过程也被称为规范化。目前关系数据库有六种范式第一范式 (INF) 、第二范式(2NF)、第三范式(3NF)、Bovce-Codd范式(BCNF) 、第四范式 (4NF) 和第五范式 (5NF)。 在数据仓库的模型设计中，一般采用第三范式。

一个符合第三范式的关系必须具有以下三个条件

a.每个属性值唯一，不具有多义性:

b.每个非主属性必须完全依赖于整个主键，而非主键的一部分，

c.每个非主属性不能依赖于其他关系中的属性，因为这样的话，这种属性应该归到其他关系中去。

根据 Inmon 的观点，数据仓库模型的建设方法和业务系统的企业数据模型类似。在业务系统中，企业数据模型决定了数据的来源，而企业数据模型也分为两个层次，即主题域模型和逻辑模型。同样，主题域模型可以看成是业务模型的概念模型，而逻辑模型则是域模型在关系型数据库上的实例化。

数据仓库的域模型应该包含企业数据模型的域模型之间的关系，以及各主题域定义。数据仓库的域模型的概念应该比业务系统的主题域模型范围更加广。在数据仓库的逻辑模型需要从业务系统的数据模型中的逻辑模型中抽象实体，实体的属性，实体的子类，以及实体的关系等。

lnmon 的范式建模法的最大优点就是从关系型数据库的角度出发，结合了业务系统的数据模型，能够比较方便的实现数据仓库的建模。但其缺点也是明显的、由于建模方法限定在关系型教据库之上，在某些时候反而限制了整个数据仓库模型的灵活性，性能等，特别是考虑到教据仓库的底层数据向数据集市的数据进行汇总时，需要进行一定的变通才能满足相应的需求。

3、E-R实体关系基础及建模案例实践

E-R实体关系基础

（1）ER实体关系模型（Entity-Relationship）是数据库设计的理论基础，当前几乎所有的OLTP（联机事务处理）系统设计都采用ER模型建模的方式,这种建模方式基于三范式。在信息系统中，将事物抽象为“实体”、“属性”、“关系”来表示数据关联和事物描述。

（2）实体（Entity）：实体是一个数据对象，指应用中可以区别的客观存在的事物。例如：商品、用户、学生、课程等。具有自己的属性，一类有意义的实体构成实体集。在ER实体关系模型中实体使用方框表示。

（3）属性：对实体的描述、修饰就是属性，即：实体的某一特性称为属性。例如：商品的重量、颜色、尺寸等。多个属性构成属性集。在ER实体关系模型中属性使用椭圆来表示。对应二维表中的列，每个属性都有一个属性名。

（4）关系（Relationship）：表示一个或多个实体之间的关联关系。实体不是孤立的，实体之间是有联系的，这就是关系。例如：用户是实体，商品是实体，用户选购商品这个过程就会产生“选购商品数量”，“总金额”这些属性，这就是关系。在ER实体关系模型中关系使用菱形框表示，并用线段将其与相关的实体链接起来。

（5）值域 ：属性的取值范围称为值域，每个属性都有一个值域。

（6）关系模式 ：二维表的结构称为关系模式。设关系名为R，其属性为 A1，A2，…，An，则关系模式可以表示为：R(A1,A2,…,An)，一个具体的例子：学生（学号，姓名，性别，...，）。

（7）候选码 ：如果一个属性集的值能唯一标识一个关系的元组而又不含多余的属性，则称该属性集为候选码。在一个关系上可以有多个候选码。

（8）主属性 ：包含在任一候选码中的属性。

（9）非主属性 ：不包含在任一候选码中的属性。

（10）主键 ：有时一个关系有多个候选码，可以选择其中一个作为主键。每个关系有且只有一个主键。

（11）外键 ：如果关系模式 R 中的属性 K 是其他关系模式的主键，那么 K 在关系模式 R 中称为外键。

（12）ER实体关系模型，又叫E-R关系图，实体与实体之间的关系存在一对一的关系、一对多的关系、多对多的关系。

一对一关系：例如：“学生”是实体，“身份证”是实体，一个学生只能有一个身份证，一个身份证也只能对应一个学生。

一对多关系：一对多关系反过来也就成了多对一的关系。例如：“学生”是实体，“账号”是实体，一个学生有多个账号，反过来就是多个账号对应一个学生。

多对多关系：例如：“学生”是实体，“课程”是实体，一个学生可以学习多个课程，一个课程可以被多个学生学习，整体来看，学生学习课程就成了多对多的关系。

实体建模示例

假设在电商购物系统中，对商品、用户设计ER实体关系模型图来表示商品信息、用户购买商品之间的业务联系，完成数据库逻辑模型设计。 设计ER实体关系模型图，步骤如下：

(1)抽象出实体；

(2)找出实体之间的关系；

(3)找出实体的属性；

(4)画出E-R关系图；

4、数据仓库发展历程详解

（略）

5、自上而下建模与自下而上建模理论深度剖析

架构01：Inmon 自上而下的架构

Inmon 模型从流程上看是自上而下的，自上而下指的是数据的流向，“上”即数据的上游，“下”即数据的下游，即从分散异构的数据源 -> 数据仓库 -> 数据集市。以数据源头为导向，然后一步步探索获取尽量符合预期的数据，因为数据源往往是异构的，所以会更加强调数据的清洗工作，将数据抽取为实体-关系模型，并不强调事实表和维度表的概念。

Inmon适合开发进度慢，对设计科学性和规范性高的企业，在业务模式较为固定的行业引用比较好，比如金融和电信行业。

架构02：Kimball 自下而上的架构

Kimball 模型从流程上看是自下而上的，即从数据集市-> 数据仓库 -> 分散异构的数源。Kimball 是以最终任务为导向，将数据按照目标拆分出不同的表需求，数据会抽取为事实-维度模型，数据源经 ETL 转化为事实表和维度表导入数据集市，以星型模型或雪花模型等方式构建维度数据仓库，架构体系中，数据集市与数据仓库是紧密结合的，数据集市是数据仓库中一个逻辑上的主题域。

Kimball模式适合快速迭代，实施成本低，能够快速交付任务。这种模式更适合互联网的高速发展，也适合中下型企业。

6、维度建模方法论详解

维度建模是一种将大量数据结构化的逻辑设计手段，包含维度和指标，它不像ER模型目的是消除冗余数据，维度建模是面向分析，最终目的是提高查询性能，所以会增加数据冗余，并且违反三范式。

维度建模也是重点关注让用户快速完成需求分析且对于复杂查询及时响应，维度建模一般可以分为三种：星型模型、雪花模型和星座模型。

事实表：指存储有事实记录的表，如系统日志、销售记录等；事实表的记录在不断地动态增长，所以它的体积通常远大于其他表。事实表作为数据仓库建模的核心，需要根据业务过程来设计，包含了引用的维度和业务过程有关的度量。作为度量业务过程的事实，一般为整型或浮点型的十进制数值，有可加性，半可加性和不可加性三种类型。其中： （1）可加度量可以按照与事实表关联的任意维度汇总，例如订单总金额； （2）半可加度量可以对某些维度汇总，但不能对所有维度汇总。例如差额是常见的半可加事实，除了时间维度外，他们可以跨所有维度进行操作。(比如每天的余额加起来毫无意义)； （3）一些度量是完全不可加的，例如：比率。对不可加事实，一种好的方法是，分解为可加的组件来实现聚集。

维度表：维度表（Dimension Table）或维表，有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联；相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。

常见的维度表有：日期表（存储与日期对应的周、月、季度等的属性）等。维表是维度建模的基础。 优点；（1）缩小了事实表的大小。（2）便于维度的管理和维护，增加、删除和修改维度的属性，不必对事实表的大量记录进行改动。（3）维度表可以为多个事实表重用，以减少重复工作。

数仓工具箱中的维度建模四步走：

graph TD
1选择业务过程 --> 2声明粒度 --> 3确认维度 --> 4确认事实

这四步是环环相扣，步步相连。下面详细拆解下每个步骤怎么做？

（1）选择业务过程

维度建模是紧贴业务的，所以必须以业务为根基进行建模，那么选择业务过程，顾名思义就是在整个业务流程中选取我们需要建模的业务，根据运营提供的需求及日后的易扩展性等进行选择业务。比如商城，整个商城流程分为商家端，用户端，平台端，运营需求是总订单量，订单人数，及用户的购买情况等，我们选择业务过程就选择用户端的数据，商家及平台端暂不考虑。业务选择非常重要，因为后面所有的步骤都是基于此业务数据展开的

（2）声明粒度

先举个例子：对于用户来说，一个用户有一个身份证号，一个户籍地址，多个手机号，多张银行卡，那么与用户粒度相同的粒度属性有身份证粒度，户籍地址粒度，比用户粒度更细的粒度有手机号粒度，银行卡粒度，存在一对一的关系就是相同粒度。为什么要提相同粒度呢，因为维度建模中要求我们，在同一事实表中，必须具有相同的粒度，同一事实表中不要混用多种不同的粒度，不同的粒度数据建立不同的事实表。并且从给定的业务过程获取数据时，强烈建议从关注原子粒度开始设计，也就是从最细粒度开始，因为原子粒度能够承受无法预期的用户查询。但是上卷汇总粒度对查询性能的提升很重要的，所以对于有明确需求的数据，我们建立针对需求的上卷汇总粒度，对需求不明朗的数据我们建立原子粒度。

（3）确认维度

维度表是作为业务分析的入口和描述性标识，所以也被称为数据仓库的“灵魂”。在一堆的数据中怎么确认哪些是维度属性呢，如果该列是对具体值的描述，是一个文本或常量，某一约束和行标识的参与者，此时该属性往往是维度属性，数仓工具箱中告诉我们牢牢掌握事实表的粒度，就能将所有可能存在的维度区分开，并且要确保维度表中不能出现重复数据，应使维度主键唯一

（4）确认事实

事实表是用来度量的，基本上都以数量值表示，事实表中的每行对应一个度量，每行中的数据是一个特定级别的细节数据，称为粒度。维度建模的核心原则之一是同一事实表中的所有度量必须具有相同的粒度。这样能确保不会出现重复计算度量的问题。有时候往往不能确定该列数据是事实属性还是维度属性。记住最实用的事实就是数值类型和可加类事实。所以可以通过分析该列是否是一种包含多个值并作为计算的参与者的度量，这种情况下该列往往是事实。

其中粒度是非常重要的，粒度用于确定事实表的行表示什么，建议从关注原子级别的粒度数据开始设计，因为原子粒度能够承受无法预估的用户查询，而且原子数据可以以各种可能的方式进行上卷，而一旦选择了高粒度，则无法满足用户下钻细节的需求。

事实是整个维度建模的核心，其中雪花模型或者星型模型都是基于一张事实表通过外健关联维表进行扩展，生成一份能够支撑可预知查询需求的模型宽表，而且最后的查询也是落在事实表中进行。

7、星型模型&雪花模型&星座模型及选型方案设计

星型模型：

是一种多维的数据关系，它由一个事实表（Fact Table）和一组维表（Dimension Table）组成。每个维表都有一个维作为主键，所有这些维的主键组合成事实表的主键。事实表的非主键属性称为事实（Fact），它们一般都是数值或其他可以进行计算的数据；如下图：

星型模型是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，所以数据有一定的冗余。

雪花型模型：

当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的事实表，形成一些局部的 "层次 " 区域，这些被分解的表都连接到主维度表而不是事实表。

通过最大限度地减少数据存储量以及联合较小的维表来改善查询性能。雪花型结构去除了数据冗余。

星座模型：

星座模型也是星型模型的扩展。区别是星座模型中存在多张事实表，不同事实表之间共享维表信息，常用于数据关系更复杂的场景。其经常被称为星系模型。

三种模型对比

属性	星型模型（星座模型）	雪花模型
事实表	1张或者多张	1张或多张
维度表	一级维表	多层级维表
数据总量	多	少
数据冗余度	高	低
可读性	高	低
表个数	少	多
表宽度	宽	窄
查询逻辑	简单	复杂
查询性能	高	低
扩展性	差	好

总结：

(1)查询性能角度来看：在OLTP-DW环节，由于雪花模型要做多个表联接，性能会低于星型模型；但从DW-OLAP环节，由于雪花模型更有利于度量值的聚合，因此性能要高于星型模型。

(2)模型复杂度角度：星型模型更简单方便处理

(3)层次结构角度：雪花型模型更加贴近OLTP系统的结构，比较符合业务逻辑，层次比较清晰。

(4)存储角度：雪花模型具有关系数据模型的所有优点，不会产生冗余数据，而相比之下星型模型会产生数据冗余。

8、维度建模案例实践

参考《数仓工具箱》