数仓建模及ETL基本过程笔记（2020.11.26更新）

一、数仓建设步骤及宏观逻辑

1.1 范式

 关系型数据库关系模式的集和和合理化程度标准。主要包括1NF、2NF、3NF、BCNF、4NF、5NF。

• 1NF：属性不可再分，是关系型数据库最基本的要求。简单理解就是所有属性都是一行的，没有层叠的。
• 2NF：在1NF基础上消除了非主属性对码的部分函数依赖（概念：函数依赖、码、非主属性、部分函数依赖）。为了满足更高级别的范式，只有拆表，这个过程叫做模式分解；
• 3NF：在2NF的基础上消除非主属性对码的传递函数依赖（概念：传递函数依赖）。满足3NF的数据库设计基本上解决了数据冗余过大、插入异常、修改和删除异常等问题；
• BC范式：在3NF基础上消除主属性对码的部分函数依赖和传递函数依赖。
• 4NF：满足3NF，且表中非主属性不包括多值；
• 5NF：从最终结构重新建立原始结构，表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。

 范式越高，数据库冗余越低，但是数据库的效率也越来越低。

1.2 常见的数仓建模方法

 当前数仓建模主要有两种流派，三种方法，分别是：

• a）inmon的关系建模，也叫做范式建模，参考《数据仓库第四版》，主要采用3NF建模，核心思想是从关系数据库出发，建立从“业务数据模型”（包括主题域模型和逻辑模型）到“数据仓库模型”的映射。优点是:建立在关系型数据库基础上，构建数仓比较方便，缺点是缺少灵活性，性能也受限。
• b）kimball的维度建模，参考《数据仓库工具箱》，由于维度建模更符合互联网公司的快速开发和敏捷迭代，所以使用维度建模的较多。优点是贴合业务，模型直观且易于理解、具有一定的灵活性，缺点是需要大量的数据预处理保障数据一致性等
• c）实体建模。不是数仓建模的方法之一，只是一种思想，将业务划分为实体、时间和说明，用处是：所处行业没有行业模型的时候，可以使用实体建模梳理业务模型，抽象出领域概念模型。

1.3 维度建模的步骤

参考《数据仓库工具箱》，维度建模包括四个步骤，分别是：

• a）选择业务过程；
   业务过程指“组织完成的操作行过程”，如获取订单、学生课程注册等。业务过程事件建立或获取性能度量，并转换为事实表中的事实；
• b）声明粒度；
   粒度用于确定某一事实表中的行表示什么。原子粒度是最低级别的粒度，强烈建议从原子级别的粒度开始设计，因为原子粒度能够承受无法预期的用户查询，且不需要猜测业务公共问题。
• c）确认维度；
   维度表被称为数据仓库的灵魂，主要是用来描述某一过程事件所涉及的“谁，什么、何处、何时、为什么、如何”等背景，包含了用于过滤和分类事实的描述性属性。
• d）确认事实。
   事实涉及来自业务过程事件的度量，基本上是以数值值表示。一个事实表行与按照事实表粒度描述的度量事件之间存在一对一的关系，因此事实表对应一个物理可观察的事件。在事实表中，所有事实只允许与声明的粒度保持一致。
   最后数仓设计人员输出表名和列名、示例领域值以及业务规则。

二、事实表技术概念

2.1 事实表结构

 事实表的设计完全依赖于物理活动，不受可能产生的最终报名的影响。除了数字度量意外，事实表总是包含外键，用于关联与之相关的维度，也包含可选的退化维度键和日期/时间戳。
 查询请求的主要目标是基于事实表的极端和聚集操作。

2.2 可加、半可加、不可加事实

 事实表中的数字度量分为三类，可加、半可加和不可加，主要考虑基于某系维度表，数字加起来是不是有业务意义。最有用的是全完可加，即可以与事实表关联的任意维度关联，比如销售额；半可加是可以对某些维度汇总，但不是所有维度汇总起来都有意义，比如差额；不可加即所有维度汇总都没有意义，比如比率。

2.3 常见的事实表

 常见的事实表主要包括：事务事实表、周期快照事实表、累计快照事实表、无事实的事实表、聚集事实表和合并事实表等，前三个为基本类型。

 事务事实表的一行对应空间或者时间某点的度量事件；

 周期快照事实表每一行汇总了某个标准周期（一天/一周/一月）的多个周期事件，粒度是周期性的，不是个体的事务。每个周期结束时建立快照，顺序存储在事实表中。周期性快照事实表通常是检索固定的、可预测视图纵向性能趋势的唯一方法。

 累积快照事实表的行汇总了过程开始和结束之间可预测的度量事件。

事务 周期快照 累计快照

 周期 离散事务时间点 以有规律的、可预测的间隔产生快照 用于时间跨度不确定的不断变化的流水线/工作流

粒度 每个事务或事务线一行 每个快照周期加上其他维度一行 每次管道时间一行

日期维度 事务周期 快照日期 管道的关键里程碑所涉及的多个日期

事实 事务性能 时间间隔内的累计性能 管道性能事件

事实表稀疏性 稀疏或稠密，与活动有关 稠密 稀疏或稠密，与管道事件有关

事实表更新 不需要更新，除非有错误需要修改 不需要更新，除非有错误需要修改 管道活动发生更新时

无事实的事实表：某些事件仅记录了一系列某一时刻发生的多维实体，没有数字化的度量。

聚集事实表：对原子粒度事实表数据进行简单的数字化上卷操作，目的是提高查询性能；

合并事实表：通常对来自多个过程，以相同粒度表示的师表合并为单一的事实表，如将现货销售和销售预测合并为一张事实表。

三、维度表技术概念

 每个维度键都包含单一的主键列，维度表通常比较宽，是扁平型的非规范表，包括大量低粒度的文本属性。

3.1 常见的维度键

主要包括自然键、持久键、超自然键和维度代理键。

自然键：具有业务意义，来源于自然源系统的键，如学生学号。

维度代理键：无语义的整型主键，以1开始（日期维度不需要遵守代理键规则）。代理键为数据仓库提供了一种机制，用于区分同一个操作型账号的两个不同的实例，如果仅仅依赖操作型代码，可能在获取或者整理数据时遭遇键重叠的问题。

持久键：雇员离职后又入职，雇员号保持持久性，雇员号被成为持久键，也被称为持久性超自然键。

3.2 常见的维度类型

退化维度：除了主键外没有其他内容。在事实表中是属于外键，但没有与之关联的维度表，退化维度的维度表可以删除，把内容放在事实表。比如操作型事务控制号码，如订单号、发票号码等。退化维度放在事实表，可以减少关联次数，并且退化维可以用于group by操作，进行分组统计。

非规范扁平维度：维度设计者应该抵制对表范式的要求，利用非范式设计满足维度建模的目标：简化和速度。

 多层次维度、日历日期维度、扮演角色的维度、杂项维度、雪花维度（避免使用雪花维度）、支架维度（对其他维度的引用，尽量少用）

四、ETL基本过程

  ETL最佳实践的宗旨：不要采用无结构的方法；否则将会产生大量类似意大利面条一样的未分类的表、模块、过程、脚本、触发器、报警和工作安排。
 参考《数仓工具箱》介绍，成体系的ETL系统包括34个子系统，分别负责不同的职责，其中包括：

• 1）从源系统获取数据的3个子系统；
• 2）处理增值和清洗的5个子系统，包括监控质量误差的维度结构；
• 3）将数据发布到维度结构所涉及的13个子系统；
• 4）帮助管理ETL环境的13个子系统；

4.1 ETL前期的需求整合

 ETL需求综合的含义是收集并理解“已知的将会影响ETL系统的需求、现实和约束等”，主要的需求类型一共包括10个方面，ETL设计初需核实此清单是否都考虑到，具体的10个需求/约束领域如下：

• 1）业务需求。
  理解并验证商业需求、维护关键性能指标的列表、可能进行下钻和跨钻的目标；
• 2）合规性；
  列出所有数据及报表主题要遵循的法律限制、列出这些数据的输入和数据转换步骤、维护“监管链”、和法律部门及首席合规官咨询讨论
• 3）数据质量；
  数据质量非常重要；提前注意元数据中“不如意”的数据元素，列举在数据分析期间发现的在ETL过程中需要持续监控和标记的数据元素。
• 4）安全性；
  寻求高层明确指示；有经验的安全管理员的参与；和合规性需求有交叉；
• 5）数据集成；
  数据集成是个大课题；全面的、集中式的主数据管理系统会有帮助；需要数仓具有一致性维度和事实；应利用业务过程的总线矩阵建立意志行为维度的有限列表；
• 6）数据延迟；
  数据延迟需求对ETL架构有较大影响，前期应该标注每个需求的数据延迟，明确业务团队是否明白数据延迟和数据质量需要的权衡；
• 7）归档与世系；
  数据暂存：每个ETL流水线主要活动发生后暂存数据（写到磁盘）；
  数据归档：保守建议是所有暂存数据都应该归档，除非有专门的定义明确认为特定的数据集合未来将不再需要；
  某些合规需求要求：每个暂存/归档数据集合都应该包含描述来源和建立数据的处理步骤的元数据，对该世系的跟踪也是明确需要的；
   简单来说就是要：记录数据源和归档的中间数据步骤及满足政策、合规性和安全、隐私等方面的约束。
• 8）BI发布接口；
  ETL小组应该确保数据的内容和结构能够使BI应用简单且快速；
  和数据建模小组配合，李处所有建立并支持BI性能的已知索引和聚集、直接被BI工具利用的事实表和维度表。
• 9）可用的技能；
  清查所在部门的操作系统、ETL工具、脚本语言、编程语言、SQL、DBMS以及OLAP技能，列出未来可能需要的系统及技能。
• 10）传统的许可证书。
  即第9点列出的资源，哪些是有正版授权的，公司高层可能偏向使用花过钱买过的正面授权的资源。

4.2 将数据插入数仓的3个子系统

 最初的ETL首先要做的就是理解数据源、获取数据，然后转换存储到独立于操作性数据库的数仓环境中。

• 系统1：基于需求和源数据的数据探查，结合维度建模，确定哪些源系统被包括进来；
• 系统2：源系统数据的首次全部加载和变化数据获取（Change Data Capture：CDC），重点是CDC的方法。此外的概念还包括：审计列、定时获取、全差异比较、数据库日志抓取和消息队列监控。
• 系统3：不同类型的源系统数据获取工具。包括文件方式（古老主机系统）、流方式。

4.3 数据清洗的5个子系统

 提高数据质量的文化和过程。数据录入源系统，需要纠错设计，并宣贯相应的数据质量文化。

• 系统4：数据清洗系统。尽早诊断分类数据质量问题，并提供清洗数据、获取数据质量事件，附加到最终数据上的可信度度量等；具体操作的时候包括质量屏幕和对质量事件的相应。
• 系统5：错误事件模式。错误时间模式是一种集中式的维度模式，目的是记录ETL流水线中所有的质量屏幕出现的错误事件（就是设计一个错误事件的表，记录相应的事件，粒度是ETL系统质量屏幕抛出的错误）。
• 系统6：审计维度装配器。简单理解就是如每天装配事实表的时候，记录装配（加载），有没有产生错误标记，也是一个表，常见的的包含完成标记点、错误标记类型、分数、版本号等等；
• 系统7：重复数据删除系统。包括重复数据删除、匹配和数据保留等问题，具体方法如列相似性，也有大量数据集成和标准化工具可以用。
• 系统8：一致性系统。负责建立和维护一致性维度和一致性事实，需要输入尽进来的内容行具有相同的结构、重复数据删除、无效数据过滤和标准化等过程，具体的流程图如下：

4.4 发布数据的13个子系统

 ETL系统的主要任务是发布阶段切换维度和事实表，所以发布子系统是ETL结构中最为重要的子系统。

• 系统9（重要）：缓慢变化维度管理器。ETL结构中最为重要的元素之一就是实现缓慢变化维度（Slowly Changing Dimension：SCD）逻辑的能力，ETL必须确定当已存在数仓中的属性值发生变化的处理方法，如果确定修改是合理的，就必须应用适当的SCD技术。主要包括重写、增加新行、增加新属性、增加微型维度以及方法间的交叉使用等；
• 系统10：代理键产生器：强烈建议所有的维度表都是用代理键，目标是产生无语义的键，通常是一个整数，成为维度行的主键；
• 系统11：层次管理器。
• 系统12：特定维度管理器：日期/时间维度、杂项维度、微型维度、缩减子集维度、小型静态维度、用户维度的维度
• 系统13：事实表建立器：包括事务事实表加载器、周期快照事实表加载器、累计快照事实表加载器；
• 系统14：代理键流水线；
• 系统15：多值维度桥接表连接器；
• 系统16：迟到数据处理器；
• 系统17：维度管理器系统；
• 系统18：事实提供者系统；
• 系统19：聚集建立器；
• 系统20：OLAP多维数据库建立器
• 系统21：数据传播管理器

4.5 ETL管理相关的13个子系统

面对数仓可能包含的巨大维度模型，ETL需要做到可用、可靠、可管理。ETL管理子系统就是为了满足这些目标的关键部件。

• 系统22：任务调度器；
• 系统23：备份系统；
• 系统24：恢复和重启系统；
• 系统25：版本控制系统；
• 系统26：版本迁移系统；
• 系统27：工作流监视器；
• 系统28：排序系统；
• 系统29：世系及依赖分析器
• 系统30：问题提升系统；
• 系统31：并行/流水线系统
• 系统32：安全系统；
• 系统33：合规性管理器；
• 系统34：元数据存储管理器。

4.6 ETL系统设计和开发的十个步骤

• 1）设计高层规划（基于源系统和分析目标）
• 2）选择ETL工具；
• 3）开发默认策略选择；
• 4）按照目标表钻取数据，确定开发ETL规范文档；
• 5）使用历史数据填充维度表；
• 6）完成事实表历史加载；
• 7）维度表增量处理过程；
• 8）事实表增量处理过程；
• 9）聚集表和OLAP加载；
• 10）ETL系统操作和自动化。

五、企业数据仓库总线架构

 一般而言，自上而下建立一个统一的企业级数仓是困难的，但分别单独建立却无法满足一致性的目标，所以需要一种架构化、增量式的方法构建企业数据仓库。维度模型对应的架构即为“企业数据仓库总线架构”（核心目标是解决一致性，替代“烟筒式维度模型”）。

5.1 企业数仓总线架构简介

 总线架构的核心是将独立业务过程的公共维度进行提取，简单的举例如下图：

 总线矩阵列表示整个企业的公共维度，有助于创建核心维度列表。不同的企业，其总线矩阵行与列的数量不同，多大多数企业来说，矩阵非常类似正方形，包含25~50行以及大约相同数量的列。所以，总线矩阵是数仓结构设计、数据治理、协调、项目评估以及组织交互的重要交付件之一。
 企业总线常见的错误类型:1）基于部门而不是业务添加的行；2）报表为中心，而不是业务过程为中心，添加了过于狭窄的行；3）过于宽泛的行；4）层次中的不同级别放在了不同的列；

5.2 一致性维度简介

 一致性维度也被成为公共维度、主维度、引用维度和共享维度。一致性维度首先在ETL系统建立，然后逻辑或物理上复制到数仓环境中，并在构建的时候，确保数仓开发小组能够使用这些维度，由CIO监督执行。
 一致性维度能够保证来自不同业务过程的性能度量（体现多个业务过程的事实表）合并到单一报表中，这种全外连接的方式通常称为“跨钻”。
 一致性维度对数据治理和管理也有重要的意义，解决和认识这个问题，需要管理层去改变和推动，IT缺乏推进该工作的权威和力量。
 设置一致性维度并与维度模型关联的中心任务中，数据结构相关工作占95%，剩下的5%就是建立一致性事实定义，即企业级的关键性能指标，如收入、利润、标准加个和开销等，必须要保持一致性事实（定义/度量等在不同业务过程中定义需要相同）。

六、其他问题

• 1、什么时候需要多个事实表？
  • 如果性能度量包含不同的自然粒度或者维度，则可能需要为不同过程建立不同的事实表；
  • 如果事务维度随着时间变化，就应该设计一系列相关的事实表而不是在单一事实表中获取库存事务。
• 2、累计快照事实表一般需要哪些特殊的键？为什么?
  • 多个事务里程碑的时间键，便于计算不同审批流/里程碑的时间间隔；
  • 批次更新的状态维度的外键，要么是0要么是1，标明当前流水线走到什么阶段。
• 3、累计快照事实表的更新会遇到什么问题？
  • 同时更新事实和维度外键，可能会带来更新延迟，所以对于不经常发生的复杂场景以及独立点分析，不适合用累计快照事实表；
• 4、简要阐述辅助事实表类型
  • 同时采用累计快照和周期快照，比如回滚累积快照的同时，在周期快照中存储36个月的历史记录，即可提供更为完整的业务视图。但同时使用周期、累积和事务事实表，在管理上会有很大负担。