数据仓库建模方法论
数据仓库建模的意义在于更好的管理数据。
我们企业数据量越来越大以后，我们很难来查找数据。数据模型就是数据组织和存储方法，它强调从业务、数据存储和使用角度合理存储数据，只有将数据有序的组织和存储起来，数据才能高性能、低成本、高效率的被使用。
1. 表模型
1.1 ER model
提出者：Bill Inmon
ER(Entity Relationship, ER)模型来描述企业的业务，并用范式的方法表达出来。
1.1.1 实体关系类型
实体关系类型模型将数据抽象为两个概念——实体和关系，实体表示为一个对象，例如学生、班级，关系是两个实体之间的关系，例如学生和班级之间的从属关系。
1.1.2 数据库规范化（NF)
数据库规范化是使用一系列范式设计数据库（通常是关系性数据库）的过程，目的是减少数据的冗余。一般范式越高，数据冗余度就越低。现在在企业业务型数据库中，常用的就是3NF。
第一范式（1NF）
定义：1NF 要求表中每个字段的值都是原子（不可再分）的，即每个单元格只能存放一个值。
举例：假设有一个“学生”表，记录学生姓名和他们选修的课程。若表设计如下：
学号 | 姓名 | 选修课程 |
001 | 张三 | 数学, 物理 |
002 | 李四 | 英语 |
这种设计违反了1NF，因为“选修课程”字段存放了多个值。
改进方法：将“选修课程”拆分，每条记录只保存一个课程信息：
学号 | 姓名 | 选修课程 |
001 | 张三 | 数学 |
001 | 张三 | 物理 |
002 | 李四 | 英语 |
第二范式（2NF）
定义：在满足1NF的基础上，2NF 要求表中所有非主属性必须完全依赖于主键（如果主键为复合主键，不能存在对主键一部分的依赖）。
举例：
考虑一个订单明细表，主键为 (订单号, 产品号)：
订单号 | 产品号 | 产品名称 | 数量 |
A001 | P001 | 手机 | 2 |
A001 | P002 | 耳机 | 1 |
如果“产品名称”只依赖于产品号，而不是整个复合主键 (订单号, 产品号)，就存在部分依赖。
改进方法：将表拆分成两个表：
- 订单明细表（订单号、产品号、数量），
- 产品信息表（产品号、产品名称）。
订单明细表：
订单号 | 产品号 | 数量 |
A001 | P001 | 2 |
A001 | P002 | 1 |
产品信息表：
产品号 | 产品名称 |
P001 | 手机 |
P002 | 耳机 |
这样，每个非主属性都完全依赖于主键，满足2NF。
第三范式（3NF）
定义：在满足2NF的基础上，3NF 要求消除传递依赖，即非主属性不依赖于其他非主属3性。
举例：假设有一个“员工”表，包含如下字段：员工号（主键）、部门号、部门名称。表设计如下：
员工号 | 部门号 | 部门名称 |
E001 | D01 | 销售部 |
E002 | D02 | 技术部 |
在此设计中，部门名称依赖于部门号，而部门号又依赖于员工号，这就构成了传递依赖。
改进方法：将表拆分成两个表：
- 员工表（员工号、部门号），
- 部门表（部门号、部门名称）。
员工表：
员工号 | 部门号 |
E001 | D01 |
E002 | D02 |
部门表：
部门号 | 部门名称 |
D01 | 销售部 |
D02 | 技术部 |
这样，部门名称直接依赖于部门号，而员工表中的非主属性不再传递依赖于其他非主属性，从而满足3NF。
1.2 维度建模
提出者：Ralph Kimball
维度建模是面向大数据数据仓库场景的设计，主要是为了统计分析需求而生，尤其是多维度的数据分析。将复杂的业务通过事实和维度两个概念来呈现。
1.2.1 事实和维度
事实：其实就是业务发生的过程，可以理解为一个个不可以拆分的事件，比如电商交易中的下单，取消订单，付款，退单等，都是业务过程。
维度：描述这个事实的各种元素(When, What, Where, How, Which)，比如Customer，Product，Location，Date…
1.2.2 事实表
- 事务性事实表
- 周期性快照事实表
- 累积性快照事实表
1. 事务性事实表 (Transactional Fact Table)
定义：事务性事实表记录的是逐个业务事件或交易的详细数据。每一条记录通常代表一次单独的操作或事件，比如一个订单、一次销售、一次购买等。它通常与时间戳、产品、客户等维度相关联。
特征：
- 记录逐条的业务事件。
- 每条记录有一个明确的时间戳，表示该事件发生的时间。
- 适用于需要记录交易级别细节的场景，通常数据量大、更新频繁。
使用场景：
- 适合记录订单、销售、交易等单一事件。
- 比如，记录销售订单中的每个单项商品，或者记录银行账户中的每笔交易。
例子：
订单号 | 产品号 | 销售数量 | 销售金额 | 销售时间 |
A001 | P001 | 2 | 100 | 2025-02-20 10:00:00 |
A001 | P002 | 1 | 50 | 2025-02-20 10:00:00 |
A002 | P003 | 3 | 150 | 2025-02-20 12:00:00 |
2. 周期性快照事实表 (Periodic Snapshot Fact Table)
定义：周期性快照事实表定期记录一个时间段内的数据快照，通常用于记录某一时间点（或时间段）的某些度量。这些度量通常是汇总的，比如月末库存量、季度销售总额等。
特征：
- 数据是周期性地汇总的，通常按天、月、季度等时间段来存储。
- 用来表示某些时间点或时间段的“快照”数据，而不是单个交易。
- 适合用于监控业务状况、财务报表等，不涉及细粒度的交易数据。
使用场景：
- 适用于记录周期性数据变化的场景，比如月末的库存、月度销售额等。
- 例如，记录每月的账户余额、每季度的产品销量。
例子：
月份 | 产品号 | 月初库存量 | 月末库存量 | 销售数量 | 退货数量 |
2025-01 | P001 | 500 | 450 | 100 | 10 |
2025-01 | P002 | 300 | 290 | 50 | 5 |
2025-02 | P001 | 450 | 400 | 90 | 15 |
3. 累积性快照事实表 (Cumulative Snapshot Fact Table)
定义：累积性快照事实表记录的是一段时间内的累计度量，通常用于表示某个过程的积累性数据。与周期性快照不同，累积性快照侧重于记录过程中的逐步变化，如账户余额的日积月累。
特征：
- 数据逐步累积，不是某个具体时间点的快照。
- 每个记录表示在某个时间点前的累计结果（例如某一月份的累计销售额或累计利润）。
- 每一条记录包含一段时间内的数据累计量或变化。
使用场景：
- 适用于记录逐步累积的度量值，如累积销售、累计利润、累计客户访问次数等。
- 例如，记录某个销售代表在一个月内的累计销售量，或某个项目的累计成本。
例子：
日期 | 销售代表ID | 累计销售金额 | 累计销售数量 |
2025-01-10 | R001 | 5000 | 100 |
2025-01-20 | R001 | 12000 | 200 |
2025-01-31 | R001 | 25000 | 300 |
4. 区别总结
特征 | 事务性事实表 | 周期性快照事实表（跨天） | 累积性快照事实表（跨天） |
数据粒度 | 单个交易或事件的详细数据 | 某一时间段（如一天、一个月）的汇总数据 | 一段时间内的累计度量 |
记录的内容 | 单独的交易记录 | 每个时间段结束时的快照 | 逐步积累的度量结果 |
更新频率 | 每次交易发生时更新 | 定期更新（如每天、每月） | 随着时间推进逐步更新 |
适用场景 | 事务级别的事件记录，如订单、销售交易 | 周期性数据汇总，如月末库存、季度销售额 | 累积性数据记录，如累计销售额、累计成本 |
查询性能 | 数据量大，查询通常涉及多个表连接 | 查询简便，适合大规模时间段汇总的场景 | 查询通常涉及历史累计数据，适用于动态分析 |
5. 选择适用场景
- 事务性事实表：当需要记录每一个具体的交易或事件时，适用于高频次的操作，且交易数据非常详细。
- 周期性快照事实表：当需要定期汇总某一时间段的数据（如月末、季度末）时，适用于监控某些时间段内的业务状态。
- 累积性快照事实表：当需要记录过程中的累计结果时，适用于逐步积累数据，如累计销售、累计成本等。
选择合适的事实表类型能够提升数据仓库的查询效率和数据处理性能，确保根据业务需求合理存储和汇总数据。
1.2.3 构建事务性事实表
选择业务过程 → 声明粒度 → 确认维度 → 确认事实
- 选择业务过程
在业务系统中，挑选我们感兴趣的业务过程，业务过程可以概括为一个个不可拆分的行为事件，例如电商交易中的下单，取消订单、付款、退单等，都是业务过程。通常情况下，一个业务过程对应一张事物型事实表。
- 声明粒度
粒度：每张事务性事实表中，每行数据表示什么，应该尽可能选择最细粒度，以此来应各种细节程度等需求。
业务过程确定后，需要为每个业务过程声明粒度。
- 确定维度
确定每张事实表相关的维度有哪些？
确定维度时，应该尽量多的选择与业务过程相关的环境信息，因为维度的丰富程度就决定了，维度模型能够支持的指标丰富程度。
- 确定事实
此处的事实一词，指的是每个业务过程的度量值，通常是可累加的数字类型的值，例如：次数、个数、件数、金额等。
综上，事实表的设计就完成了。
- 第一步选择业务过程可以确定有哪些表？(table)
- 第二步可以确定每张表每行数据是代表什么？(row)
- 第三步可以确定每张事物表的维度外键？(column-dim)
- 第四步可以确定每张事物表的度量值字段？(column-fact)
1.2.4 维度表
反规范化维度表——星星模型（常用）
规范化维度表——雪花模型
1.2.5 构建维度表
确定业务需求 → 确定维度表字段
- 梳理业务需求
- 明确分析目标：首先，要与业务部门或数据分析师沟通，明确需要支持哪些类型的分析，识别关键的业务维度。例如，销售分析可能需要“时间”、“产品”、“客户”等维度。
- 了解查询类型：分析数据查询的常见模式，确定需要哪些维度字段来支持高效查询。
- 选择维度：基于业务需求，选择需要的维度。常见维度包括：
- 时间维度：如年、季度、月份、日等。
- 产品维度：如产品ID、产品名称、类别等。
- 客户维度：如客户ID、客户姓名、地理位置等。
- 地域维度：如国家、省份、城市等。
- 员工维度：如员工ID、部门、职位等。
- 分解维度：根据实际需要将一个维度拆解为多个属性。比如，“时间”维度可以分解为“年”、“月”、“日”、“季度”等属性。
- 确定维度表的字段
为每个维度设计必要的字段（属性）。这些字段通常用于描述维度的各个方面。每个维度表通常有一个主键，称为维度键（Dimension Key），通常是数字或字母编号，用于在事实表中引用。
添加层次结构：有些维度是层次化的，例如“时间”维度可以包含“年”、“季度”、“月”、“日”这些层级，方便多维分析。
避免重复信息：确保维度表中的属性是原子的，不应包含冗余数据。每个属性都是维度的单一描述。设置适当的默认值和标志：某些情况下，需要在维度表中设置默认值或标志，例如“失效日期”字段，表示数据是否仍然有效。
1.2.6 星星模型和雪花模型
星型模型（Star Schema）
- 定义：星型模型是数据仓库中最常见的模型，它由一个中央的事实表和多个维度表组成。维度表直接与事实表连接，不做进一步的规范化，因此在设计上看起来像一个“星星”。
- 特点：
- 维度表去规范化：维度表通常是去规范化的，包含了大量的属性，这样做的目的是优化查询性能。
- 简洁：模型简单，容易理解。
- 查询性能好：由于维度表没有复杂的层次结构，查询时不需要做太多的连接操作，查询速度较快。
- 使用场景：
- 适用于查询较频繁、数据更新较少的场景。由于查询优化，星型模型通常在商业智能（BI）工具中表现优秀。
- 适合较小规模的数据仓库或者数据量较大的静态数据分析，如销售数据、财务数据等。
- 优缺点：
- 优点：查询简单、性能优异，设计和理解容易。
- 缺点：维度表去规范化可能导致数据冗余，数据存储空间需求较大，更新时可能需要维护冗余数据。
雪花模型（Snowflake Schema）
- 定义：雪花模型是星型模型的扩展，它将维度表进一步规范化，将复杂的层次结构拆分为多个子表，形成雪花形状的图案。
- 特点：
- 维度表规范化：维度表被进一步拆分成多个子表，消除冗余。比如“产品”维度可以拆分为“产品类别”和“产品”两个表。
- 查询更复杂：由于维度表存在多个关联子表，查询时需要做更多的连接操作，因此查询效率较低。
- 使用场景：
- 适合数据量非常大、需要进行严格数据管理的情况，或者维度表存在复杂的层次关系时，雪花模型可以帮助降低数据冗余。
- 适用于需要进行细粒度分析且数据变化频繁的场景。
- 优缺点：
- 优点：数据冗余低，存储空间节省。数据维护时更容易管理。
- 缺点：查询时需要多表连接，查询复杂度较高，性能较差。
1.2.7 对比：事实表和维度表
特征 | 事实表 | 维度表 |
目的 | 存储度量数据，用于业务分析的计算和聚合 | 存储描述性数据，用于为事实表提供上下文信息 |
数据内容 | 主要包含数值数据（如销售额、数量、成本等） | 主要包含属性数据（如时间、产品、客户等） |
表的大小 | 通常较大，记录每个事件或交易 | 通常较小，包含描述性字段和少量记录 |
主键 | 通常是复合主键，由多个维度键组成 | 通常是单一的主键（维度键） |
数据粒度 | 通常是事务级别的粒度，每条记录代表一个事件 | 每条记录表示一个维度的描述信息 |
存储类型 | 存储数值型度量数据 | 存储分类、描述性的文本信息 |
数据冗余 | 数据冗余较少，数据量通常较大 | 数据冗余较多，尤其是描述性字段重复存储 |
更新频率 | 更新频繁，随着事务或事件的发生而增加记录 | 更新不频繁，数据通常是静态的 |
数仓同步 | 每天增量同步，通过时间做分区 | 通过全量同步，直接覆盖一份 |
关系 | 和维度表通过外键进行关联 | 通过主键与事实表关联 |
索引 | 需要在事实表的外键和主键上建立索引 | 通常在主键上建立索引，可能还会有其他辅助索引 |
设计目标 | 支持高效的聚合计算，通常用于报表分析和决策 | 提供维度上下文，使查询能够进行分组和筛选 |
查询场景 | 处理和计算大规模的事务数据，如销售、订单等 | 提供详细的维度信息供分析和查询使用 |
1.3 ER建模和维度建模的对比
1.3.1 ER建模（实体-关系建模）
使用场景：
- 适用于在线事务处理系统（OLTP）设计。
- 用于传统的事务型数据库建模，主要用于日常业务数据的存储和管理，如银行系统、库存管理等。
- 系统中需要高效的事务处理、数据更新、插入和删除操作。
优点：
- 数据结构清晰： ER模型清楚地定义了实体与实体之间的关系。
- 灵活性高： 适应各种业务需求，能灵活应对复杂的数据结构。
- 规范化： 数据冗余少，避免数据重复，通过设计规范化（如1NF、2NF、3NF）确保数据一致性。
缺点：
- 查询效率较低： 由于高度规范化，查询通常需要多个表连接，可能导致性能问题。
- 复杂性增加： 在处理大量数据和复杂查询时，ER模型可能变得难以扩展和维护。
- 不适合分析型需求： ER建模不专注于分析，而是以事务处理为主。
1.3.2 维度建模（Dimensional Modeling）
使用场景：
- 适用于在线分析处理系统（OLAP）设计。
- 主要用于数据仓库和商业智能（BI）系统，支持数据分析、报表和决策支持。
- 适合历史数据的分析和聚合查询，如销售数据分析、市场营销活动效果评估等。
优点：
- 高效的查询性能： 维度模型专门设计用于数据分析和查询，查询效率高，特别适合进行多维度聚合分析。
- 简洁直观： 事实表和维度表的设计简单，易于理解，适合非技术用户操作。
- 支持复杂分析： 能处理复杂的多维数据查询，支持OLAP操作如切片、切块、旋转等。
缺点：
- 数据冗余： 维度建模为优化查询性能，会允许一定的数据冗余。例如，维度表中可能存储与业务变化无关的重复数据。
- 数据更新复杂： 由于历史数据通常不会被修改，维度建模更适合存储历史数据，但对实时数据更新的支持相对较弱。
- 规范化较差： 为了优化查询性能，通常避免对维度表进行过度规范化，这可能导致数据冗余和一致性问题。
1.3.3 区别总结
特点 | ER建模 | 维度建模 |
目标 | 事务型数据库设计，优化数据的一致性与完整性 | 数据仓库设计，优化数据分析与查询性能 |
数据结构 | 实体、关系及属性，规范化设计 | 事实表、维度表，去规范化设计 |
适用场景 | OLTP（在线事务处理系统） | OLAP（在线分析处理系统），商业智能，数据仓库 |
查询性能 | 查询效率较低，需要多个表连接 | 查询性能高，特别适合多维度分析和聚合查询 |
数据冗余 | 数据冗余少，规范化设计减少重复数据 | 数据冗余较多，为了查询性能，维度表存储重复信息 |
数据更新 | 高效的实时数据更新（UPDATE, DELETE, INSERT) | 不适合频繁更新，主要关注历史数据的存储与查询(QUERY) |
复杂性 | 较复杂，适应高并发事务处理 | 简单易懂，适合面向分析的用户操作 |
2. 数据仓库设计
2.1 数据仓库分层
基本分层，每个公司的数仓建模理解和规范可能不同，但是大体上都遵循一些原则。
- ODS (Operational Data Store)——无需设计
存放未经过处理的原始数据，结构上与源系统保持一致，是数据仓库数据的准备区。
- DWD(Data Warehouse Detail)
基于维度建模理论构造，存放维度模型中的事实表，保存各个业务过程中最小粒度的操作记录。
- DWS(Data Warehouse Summary)
基于上层的指标需求，以分析的主题对象作为建模驱动，构建公共统计粒度的汇总表。
这一层可能有各种不同粒度的汇总。
- ADS(Application Data Service)——无需设计
存放各项统计指标结果，用户想看啥指标，就放啥指标就行了。
- DIM(Dimension - Public)
基于维度建模理论进行构建，存放维度模型中的维度表，保存一致性维度信息
3. 数据仓库构建流程
整个构建流程： 1） 数据调研 2） 明确数据域 3） 构建业务总线矩阵 + 维度模型设计（设计DIM + DWD） 4） 明确统计指标 + 汇总模型设计（设计DWS） 5） 开发 6） 调度
3.1 构建流程
3.1.1 数据调研
上游：业务调研。我们当前这个业务的业务系统（OLTP）有哪些表？这些表分别是做什么的？记录了哪些事件？表和表之间的ER模型关系是什么？
下游：需求调研。我们要分析这个业务系统的哪些指标？粒度如何？需要我抽取上游数据中哪些表呢？
3.1.2 明确数据域（非必须）
因为有的业务系统的表非常杂乱，我们可能需要对业务数据库的表进行数据域的划分
比如有的表
- 和用户有关系 —— 用户数据
- 和交易行为有关系 —— 交易数据
- …
3.1.3 构建总线矩阵 + 维度模型设计 （设计DIM + DWD）
总线矩阵：
总线矩阵中每一行对应的是一个业务过程，
总线矩阵按照数据域、 业务过程、粒度、维度、度量分开。
而我们需要明确这个业务过程的粒度、维度、度量。
根据业务总线矩阵，我们可以轻松的设计出 DIM层 + DWD层
3.1.4 明确统计指标 + 汇总模型设计 （设计 DWS）
我们到底要统计哪些指标啊？根据报表需求进行分析，整理出指标体系，包括原子指标、派生指标、衍生指标。
同一周期、粒度的指标，可以放在一张表里。
比如当日各渠道独立访客数+当日各渠道会话总数，可以放在一张表里，独立访客数+会话总数分别是两个字段。这样减少了表的重复数据。优化了空间，
3.1.5 开发（工作占比量很少）
3.1.6 调度（自动化）
4. 数据仓库规范
表命名规范
字段命名规范
…