数据仓库设计与数据建模初探

一、为什么需要引入数据仓库

数据仓库本质上是一种数据库，但它有一些特定的特性和用途，使其与传统的关系数据库有所不同。

需要分析的数据量较大（单批 GiB），此时事务性数据库分析性能堪忧，需要通过建立索引而且会分析查询会影响在线事务。而数仓一般采用列式存储，自带索引性能加成。
分析查询不想影响在线事务
需要记录历史数据
需要对来自不同数据库、数据源的数据进行整合、关联分析和交叉探查。比如将存储在 SLS 的用户日志和 MySQL 的用户信息进行关联分析，通过用户的行为日志建立用户画像。
批处理分析，需要定期批量查询分析数据并生成结果，比如各种 BI 报表。
......

数据仓库系统一般采取下图架构来满足上述使用场景的：

数据从不同的数据源引入到统一的存储
在统一存储里进行分析计算
计算得到分析结果给外部消费
串联上述数据引入、处理和分析过程的程序就是 ETL（Extract-Transform-Load，抽取-转换-加载）任务

二、基本概念解释

ETL:

是 Extract（抽取）、Transform（转换）和 Load（加载）的缩写，是一种将数据从多个源头提取、清洗和转换后加载到目标存储系统的数据集成过程，旨在提高数据的质量和一致性，支持高效的数据分析和业务决策。

数据建模：

数据仓库需要合理有序的数据结构作支撑，如何设计数据的存储格式和数据之间的关联关系，这个过程就是所谓的数据建模

OLTP(online transaction processing 联机事务处理)：

是一种数据处理方式，主要用于管理日常事务并保持数据的完整性和一致性。

OLTP系统专注于处理短小的、实时的在线交易，这些交易包括插入、更新、删除和简单的查询操作。OLTP系统强调快速的响应时间和高效的事务处理，以确保数据的一致性和完整性。

OLAP（Online Analytical Processing，联机分析处理）：

是一种数据处理方式，主要用于复杂查询和数据分析，专注于从大量数据中快速提取和分析信息，帮助用户进行多维数据分析和数据挖掘。

三、微观设计：开始数据建模

1、建模思想分类

数据仓库的数据建模理论分为自上而下的 Inmon 派和自下而上的 Kimball 派。

Inmon 的自上而下是指先从上游业务系统的数据着手，进而构建出整个企业共享的数据仓库（Enterprise Data Warehouse），在这个数据仓库下在延伸出不同主题的数据集市（Data Marts，数据集市就是关于某一主题的小数据仓库），比如账单数据作为一个账单数据集市。
Kimball 的自下而上是指从最下游的业务数据分析需求开始，先想清楚要解决什么问题，解决这些问题需要知道哪些业务过程、所要分析的数据的维度和指标度量需求，再去上游捞取需要的数据。根据分析问题的领域不同，就逐渐积累出了一堆数据集市，Kimball 认为这堆逐渐构建出来的数据集市就是逻辑上的数据仓库了。

在互联网行业中，需求变化飞快，追求敏捷更符合实际场景，阿里的 MaxCompute 也推荐使用维度建模，除此以外，微软的 Power BI、Tableau 等业界 BI 数据工具都可以采用维度建模，本文主要介绍的也是 Kimball 的维度建模理论。

维度建模方法和关系建模方法（关系型数据库）的根本区别来自他们组织数据的视角和使用数据的目的不同。

2、建模案例对比

关系建模将世界看作「实体」和「关系」，通过这两个概念和3NF规划化对业务进行建模，最后结构化为表格。这种视角源于对数据一致性、减少数据冗余的追求。如下示例，是一个简单的零售系统的关系建模示意图。

而维度建模则将世界看作「维度」和「事实」，或者称为「维度」和「度量」。

维度是用来描述属性和筛选数据的，比如商品的类别、产地
事实表示某个业务的度量、指标或者数字，比如商品的销售量和库存。

将维度和事实各自整理为表格的每一列，表格的每一行则代表一个度量事实。提供给下游分析消费。这种视角源于对数据分析需求便利性的追求。

同样是对零售订单的进行建模，可以对比一下两种方式之不同。

技术上，关系建模主要构建实体表和关系关联表。维度建模主要构建「事实表」和「维度表」，并且以事实表为中心，关联所有维度表——因此这种建模方法也被称为星型模型（Star schema）。
目标上，两种建模方法的内核目标不同。维度建模以分析的便利性和查询性能为主要目标，分析查询主要是筛选+聚合数据两个操作，因此在事实表就已经将数据分为【维度】和【度量】两部分，维度用于筛选，度量用于求和等聚合计算。并且为了提高查询性能，需要减少不同表关联 Join 操作，因此将常用的维度从维度表里冗余到了事实表中（这个冗余操作在维度建模里叫做维度退化），不常用的维度会独立保留在对应的维度表里。

比如要对比分析18岁和25岁女性客户在上月的购物情况。

如果在关系建模下进行类似的查询，查询逻辑不仅冗长，而且需要好多个关联操作，这种查询性能难以接受，这在上世纪后期大型传统零售企业的日百万级订单量都很难顶得住，何况在当前电商公司更巨大的日订单量。

SELECTb.订单日期 - c.出生年月 AS 客户年龄,SUM(a.商品数量 * d.商品单价)
FROM 订单-商品关系表 a
LEFT JOIN 订单实体表 b ON a.订单ID = b.订单ID
LEFT JOIN 客户实体表 cON b.客户ID = c.客户ID
LEFT JOIN 商品实体表 dON a.商品ID = d.商品IDWHERE b.订单日期=上个月AND b.订单日期 - c.出生年月 IN (18, 25)AND c.性别=‘女性’
GROUP BY客户年龄

如果使用上述的维度模型，因为客户的性别、出生年月已经转化为年龄并提前添加到订单事实表，而且关于支付金额的度量数据也和其他维度提前附着在事实表里，对数据分析师来说，只需要知道事实表，一个简单的 SQL 查询就可以得到分析结果。

SELECT客户年龄,SUM(支付金额)
FROM 订单事实表
WHERE 订单日期=上个月AND 客户年龄 IN (18, 25)AND 客户性别=‘女性’
GROUP BY客户年龄

由这个例子可窥一斑，维度的建模方法在数据分析方面相对于关系建模的优势。

3、建模基本流程

维度建模基本流程可以总结如下几步：

确定业务流程
确定粒度
确定事实
确定维度
冗余维度（可选）

通过上述过程可以构建出一系列：事实表+维度表。

确定业务过程

确定业务过程是维度建模第一步，其实就要

搞清楚要分析什么问题？
分析这个问题需要了解业务的重要节点？
进一步的，这些节点可用什么数据来度量？
这些数据可以从角度和维度来解读？

确定粒度

粒度则是要分析的数据的最小单位，就是事实表每一行要表示的含义。

典型的粒度声明如下：

客户销售事务上的每个产品扫描到一行中
机场登机口处理的每个登机牌采用一行表示
仓库中每种材料库存水平的每日快照采用一行表示
每个银行账户每月的情况采用一行表示

确定维度

维度要解决的问题是：业务人员如何描述业务过程度量事件的数据？

确定维度的几个原则：

尽量多包含一些维度，以尽可能满足下游的消费需求
维度来源尽量来自已经建立的维度表，统一维度数据的来源，保持数据一致性

比如要度量「每个月某商品在广东省 25-30岁程序员群体里的销售量」，就可以拆解出「订单时间」、「商品」、「客户收货地」、「客户年龄」、「职业」四个维度和「销售量」这 1 个度量事实，并设计如下事实表和维度表。

确定事实

事实，可以理解为度量或者指标。

事实可以通过回答「这个业务过程可以用什么数据度量」来确定，如要度量「每个月，商品 A 在广东省 25-30岁程序员群体里的销售量、销售金额、退货量、退货金额」

冗余维度（可选）

实践中，为了更方便地从事实表里的分析度量数据，减少在不同维度表之间进行关联操作，可以进一步将常用的维度从维度表里冗余一部分到事实表里，这个步骤也称为「维度退化」，意味着维度从维度表中退化成为了事实表中的属性。这样设计出来的表也就是我们常说的宽表。

还是以上述分析问题为案例，可以进一步将事实表设计为如下，将客户信息从「客户维度表」中退化到订单事实表，这样对于数据分析师来说不用关联客户维度表也可以得到他想要分析的客户信息了。

4、事实表设计

事实表基础

事实表应该一般包含如下3部分：

日期/时间戳。因为数仓本来的目的就是用于分析业务的各种历史变化和趋势的，所以事实表中一般都要包含日期或者时间戳。
维度或维度表外键
事实度量（如销售量、销售额、退货量）

事务/事件事实表

对事务、事件类的业务过程，他们的粒度是一行一个事务记录。

比如下订单的业务过程适合设计为一行一个订单的事务的粒度。这类事实表也被称为事务事实表。

周期快照事实表

有些情况下，我们想衡量一个固定周期之间，某些业务的趋势变化，因此粒度会变为一行一个时间点的业务度量。比如我们想要观察每天总销售、销售金额。这种场景比较适合建立周期快照事实表。如下是一个周期快照事实表。

累计快照事实表

对于一些带有工作流、流水线性质的业务过程，比如订单下单、订单发货、订单收货三个业务过程比较相关，并且维度都是一样的（都和订单信息相关），如果使用上述事务事实表来度量，他们要么分在三个事实表里、要么放在一个表里的三行，每行代表一个订单动作。

当然更好的方式是将他们串联在一起，这样可以分析更好的分析发货延迟、物流延迟等业务度量。这种串联在一起的形式就是累积快照事实表。这样对于累积快照事实表，每一行的粒度就是一个业务流水线实例。

5、维度表设计

维度表基础

设计维度表也有几个步骤：

确定主键
确定维度属性
反规范化
确定处理维度变化的策略

其中需要重点说明的是要适当进行反规划化。

规范化主要好处是数据一致性，可以实现一处修改、处处生效。但是会使得维表层次变得复杂。

如下左图就是满足 3NF 的商品维度表设计，使用这种方式构建的维表被称为「雪花模型」。因为一个维表就关联了多个维表，如果把它和事实表关联，就会使得上文的星型模型事实表像雪花一样进一步分叉分形，比星型模型更复杂化了。

为了更好的分析和使用，需要尽量将多个维度表退化到一个维度表里，避免把维度表设计成为 3NF 规划的结构。

如下右图则是通过左图反规划以后得到的一个维表，从数据分析角度来看，左图杂乱的规范化维表显然很难理解和处理（尤其当公司里缺少数据说明文档，很多业务逻辑和设计依靠口口相传的时候）。

因此右边这么一个干净整洁、便于使用的维度表是更合理的选择，而且对于数据仓库本身的使用场景来说，数据一经导入不会修改或者很少修改，因此一致性不是一个强诉求，进一步讲 3NF 规范化也不是强诉求。

处理维度变化

上文提到数据仓库的使用场景下，数据一经导入很少变更。但是挡不住引入的数据本身就在变化，比如某个客户的收货地址发生变化了。你会觉得这好像问题不大，变就变啊，给他发货的时候使用最新地址不就行了？这对只关心当前现状的事务性场景来说是没问题的，但是分析历史数据的时候就有问题了。

考虑你要分析的问题是：广东省程序员的一年消费主要集中在哪些商品品类？

如果你使用客户在年底时的默认收货地址来确定这个客户属于哪个省份，那么恭喜你踩坑了。

如果这个客户在这一年6月份从广东搬到了浙江，默认收货地址也改为了浙江省杭州市，你会将这个客户一年的消费记录都统计到了浙江省里去。客户的消费行为可能会显得有一些异常，和一般的浙江省客户消费行为不太相似，比如这个广东人居然在冬天会买秋裤护膝。

更合理的方式，应该是将这个客户的半年前的消费记录统计到广东省，后半年的消费记录统计到浙江省。要做到这一点就需要让数据具备按历史时间回溯的能力，因此需要合理地处理维度变化。

下面介绍处理维度变化的几个主要手段：

直接重写

就是直接更新维度表的值。这样的方式就会遭遇上述的统计不一致的问题。

增加新行

这种方法通过：1. 新增一行新纪录，并标记为有效；2. 标记旧记录为无效，两个主要步骤来完成。

这种技巧在事务性数据库里也比较常用，比如加一个字段 is_deleted = Y/N 作为记录软删除的手段，便于出现问题的时候可以及时回滚补救。

但是分析历史的时候没有不知道哪个时间应该用哪条记录，只知道当前生效的记录。

增加新列

这种方法通过新增一列来实施维度变更，这样能保留旧列的信息，同时把新维度更新到维度表中。

这种方式的好处是不影响原来只想消费旧列的下游消费方，但是对于想要消费新列的消费方，需要实施变更，修改查询 SELECT 条件才能查到新列的维度数据。

而且也存在无法按时间回溯到正确记录的问题

周期快照

这种方法比较简单粗暴，按照一定周期（比如每天）给维度表保存一次全量数据并记录保存的时间，也称为打快照。这种方法好处是可以按照快照时间来回溯当时的维度数据，非常便于 ETL 和分析处理。解决了上述的回溯历史记录的问题，比如根据快照时间来判断这个客户在20230601 时居住在广东，20230701 时居住在浙江。

缺点是数据存储存在大量冗余，但是当下存储成本远低于CPU和内存，如果存储冗余还可以接受，通过存储成本换取开发和计算资源的成本是划算的。

增加新行+有效时间

这个方法结合了「增加新行」非常类似，但是增加了维度记录的「开始时间」和「结束时间」。这种方法结

这种做的好处是可以像周期快照一样按照时间回溯历史记录，同时由摆脱了周期快照的大量数据冗余。

但缺点是，下游消费的时候需要注意指定开始和结束时间来限定要查询的维度记录，否则会查询错误，因而对于下游消费者来说有一定的理解障碍和门槛。

比如上图，如果查询开始时间 > 20230101 , 结束时间 < 20230630 的时候只会返回一个记录，但是查询开始时间 > 20230101 - 结束时间 < 20230730 时，会返回2条记录，这在进行数据关联 Join 的时候会出现意想不到的结果。

为了便于处理，只返回一条记录，更好的查询方法是每次只限定查询一个日期，比如开始时间 >= 20230101 , 结束时间 <= 20230101。

四、宏观设计：数据分层

数据引入层 ODS（Operation Data Store）：

存放未经过处理的原始数据至数据仓库系统，结构上与源系统保持一致，是数据仓库的数据准备区。

数据公共层 CDM（Common Data Model，又称通用数据模型层）：

包括 DIM 维度表、DWD 和 DWS，它们都由ODS 层数据加工而成。主要完成数据加工与整合，建立一致性的维度，构建可复用的面向分析和统计的明细事实表，以及汇总公共粒度的指标。上文微观设计章节介绍的维度建模方法主要应用于本层。

维度层（DIM：Dimension）：

基于维度建模理念思想，建立整个企业的一致性维度。降低数据计算口径和算法不统一风险。

明细粒度事实层（DWD：Data Warehouse Details）：

以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。

汇总粒度事实层（DWS：Data Warehouse Summary）：

以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型。

数据应用层ADS（Application Data Service）：

存放数据产品个性化的统计指标数据。根据CDM与ODS层加工生成。

1、层次调用规范

ADS 应用层优先调用数据仓库公共层 CDM 数据。如果已经存在 CDM 层数据，不允许 ADS 应用层跨过 CDM 中间层从 ODS 层重复加工数据。CDM 中间层应该积极了解应用层数据的建设需求，将公用的数据沉淀到公共层，为其他数据层次提供数据服务。同时，ADS 应用层也需积极配合 CDM 中间层进行持续的数据公共建设的改造。避免出现过度的 ODS 层引用、不合理的数据复制和子集合冗余。
ODS 层数据不能直接被应用层任务引用。如果中间层没有沉淀的 ODS 层数据，则通过 CDM 层的视图访问。CDM 层视图必须使用调度程序进行封装，保持视图的可维护性与可管理性。
CDM 层任务的深度不宜过大（建议不超过10层）。
CDM 汇总层优先调用 CDM 明细层，可累加指标计算。CDM汇总层尽量优先调用已经产出的粗粒度汇总层，避免大量汇总层数据直接从海量的明细数据层中计算得出。
CDM明细层累计快照事实表优先调用CDM事务型事实表，保持数据的一致性产出。
有针对性地建设CDM公共汇总层，避免应用层过度引用和依赖CDM层明细数据。

2、分层表命名规范

表名关键词含义：

单分区增量全量标识：全量=f，增量=i
刷新周期标识：分钟=min，小时=h，天=d，月=m，年=y
统计时间周期范围缩写：分钟=min，小时=h，天=d，月=m，年=y。比如 7d代表7天汇聚结果表。

不同层级的表的命名规范：

ODS：ods_{源系统表名}{其他}{刷新周期标识}{单分区增量全量标识}
- 日增量数据：ods_{源系统表名}_df
- 日全量数据：ods_{源系统表名}_di
DIM：dim_{业务}{维度定义}[_{自定义命名标签}]_{刷新周期标识}{单分区增量全量标识}
DWD：dwd_{业务}{数据域缩写}{业务过程缩写}[{自定义表命名标签缩写}]{刷新周期标识}{单分区增量全量标识}
DWS：dws_{业务}{数据域缩写}{数据粒度缩写}[{自定义表命名标签缩写}]{统计时间周期范围缩写}{刷新周期标识}{单分区增量全量标识}
ADS：ads_{业务}{数据域缩写}{数据粒度缩写}[{自定义表命名标签缩写}]{统计时间周期范围缩写}{刷新周期标识}{单分区增量全量标识}

假设有个分析的需求是：「统计每个月某商品在广东省 25-30岁程序员群体里的销售量」，下面介绍一下如何应用这些规范。

首先设计一个订单事实表：