数仓建模理论与规范

一、 模型架构设计目标

数据仓库的定义

数据仓库是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于支持管理决策（Decision Making Support）。

从上面的定义可用看到数据仓库主要有四个特点：

• 面向主题：面向分析主题，如商家全域分析、交易环节分析等。

• 集成的：将业务系统进行集成组装，并整合到数据仓库中。

• 相对稳定：不同于OLTP 需要进行很多事务性操作（如：插入、删除、修改，等），OLAP 是一次性载入后进行多次查询访问。

• 反映历史变化：不同于OLTP 仅反映当下数据状态，OLAP 可以反映数据历史的变化情况。

范式建模VS 维度建模

建模的两大核心思想是：范式建模和维度建模。

范式建模法（3NF）——Inmon

数据仓库建模中最常用的方法，在技术上可以解决关系型数据库的数据存储，减少大量的数据冗余；在业务上可以使模型更加简洁易懂，数据的出口唯一。

• 优点：从关系型数据库的角度出发，结合了业务系统的数据模型，能很方便的实现数据仓库建模，同时逻辑清晰，避免了数据冗余。

• 缺点：从底层数据向数据集市的数据进行汇总时需要进行一定变通，经常需要多表关联才能满足相应的需求。

维度建模——Kimball

按照事实表、维表来构建数据仓库、数据集市。

• 优点：事实表事先针对各个维度做了大量的预处理，比如进行预先的聚集、排序、分类等，后续基于其上的应用在访问速度很快；维度非常直观，紧紧围绕着业务模型，能快捷完成维度建模。

• 缺点：当业务发生变化，需要重新进行维度的定义，往往需要重新进行维度数据的预处理，并导致大量的数据冗余，无法保证数据来源的一致性与完整性。

本次分享主要介绍维度建模。

模型架构设计目标

模型架构设计的总体目标是清晰的层次架构、合理稳定的数据分域和高效易用的数据模型。具体体现在以下四个方面：

易使用

• 一致性

• 规范性

• 完整性

高质量

• 稳定产出

• 口径一致

• 准确性

低成本

• 计算成本

• 存储成本

• 研发成本

好运维

• 可扩展

• 可回刷

• 易维护

二、 模型架构设计核心思想

核心原则

模型架构设计的核心原则是高内聚、低耦合，即在域内内聚，域之间耦合，以及业务和模型的耦合，在此之上实现稳定性、扩展性、建设效率、产出效率和使用效率。

核心过程

模型架构设计的核心过程有四个步骤：数据分层、业务分类、数据分域、模型设计（包括：确定维度、确定事实、确定模型）。

三、 数据分层架构设计

数据分层架构主要包含三个层次。

贴源层：ODS（Operational Data Store）操作型数据存储层

面向业务的原始溯源性，贴原从业务系统引入并组织数据。

中间层：CDM（Common Data Model）公共数据模型层

面向业务通用性，易用性、复用性，组织公共通用明细数据与汇总数据。包括三种类型数据：

• DWD（Data Warehouse Detail）：明细类数据事实表

• DWS（Data Warehouse Summary）：汇总类数据事实表

• DIM：维度表

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

面向业务应用视角组织数据，一般是面向产品、业务场景进行公共数据组合与个性化计算。

下图右边以淘宝为例，列举淘宝三个核心Project（tbads、tbcdm、tbods）

四、 数据分域架构设计

数据分域分为三个步骤：收集、提炼、归纳。

收集：业务数据需求、存量数据梳理

• 核心目的：对现有数据和业务诉求需要的数据进行merge，保障数据仓库的完

整性，形成数据全集。

• 核心对象：分析师、业务运营人员、数仓开发者。

• 核心输出：粒度、维度、数据指标、使用场景等信息。

提炼：业务过程、业务梳理

业务过程：指企业的业务活动行为，如点击、浏览、下单等，业务过程是一个不可拆分的行为事件。

• 核心目的：对收集的数据全集，进行业务关键词（包括业务过程、业务元素）提炼，根据经验罗列分类。

• 核心对象：数据模型架构师。

• 核心输出：业务过程、业务元素列表。

归纳：数据域

数据域：面向业务，根据业务过程进行分类，组合抽象而成的数据集合。数据域不能轻易变动，在划分数据域时，既能覆盖当前所有的业务场景数据，又能在新业务进入时被融入，或对整体架构无影响下的扩展新数据域。

• 核心目的：对业务过程、业务元素的列表进行抽象，尽量避免边界模糊不清，归纳出数据域名称。

• 核心对象：数据模型架构师。

• 核心输出：数据域大图，包括核心业务过程与元素的包含关系。

下图用实例来介绍数据分域过程中如何进行收集、提炼和归纳：

五、 数据模型设计流程

数据模型设计主要分为三个阶段：需求调研，规范定义，模型设计。

名词解释

维度

• 维度是对应业务的数据分析角度，维度是度量的环境，用来反映业务的属性，某类属性的集合构成一个维度。

• 维度归属于一个数据域，如地理维度（其中包括国家、地区、省以及城市等级别的内容）、时间维度（其中包括年、季、月、周、日等级别的内容），会员维度。

度量

• 对某个业务事件的衡量，通常为数字，如件数，次数。

• 区别于原子指标，度量命名一般不带上具体的业务动作。

粒度

• 精确定义事实表的每一行所表示的业务含义，传递的是与事实表度量有关的细节层次，比如子订单粒度。

时间周期

用来明确数据统计的时间范围或者时间点，如最近30 天、自然周、截至当日等。

修饰词

指除了统计维度以外指标的业务场景限定抽象，比如有效（下单金额），PC 端（下单金额）。

原子指标

• 基于某一业务事件行为下的度量，具有明确业务含义，是业务定义中不可再拆分的指标。

• 原子指标=业务过程+度量，如下单（事件）金额（度量）。

派生指标

• 可以理解为对原子指标业务统计范围的圈定。

• 派生指标=一个原子指标+多个修饰词（可选）+时间周期。

• 比如：最近30 天PC 端下单金额（最近30 天为时间周期，PC 端为修饰词，下单金额为原子指标）。

需求调研

业务调研

业务调研的流程分三个步骤：

• 输入调研模板。

• 针对产品和运营进行调研。

• 归纳产出：业务过程&数据域。

下图举例说明业务调研的流程：

需求分析

需求分析的三个步骤：

• 输入调研模板，研究报表&数据体系。

• 与分析师&运营讨论收集信息。

• 归纳产出：指标体系。

总线矩阵

根据业务调研、需求分析，完成总线矩阵，以明确数据域与哪些业务过程相关，业务过程与哪些维度相关。

规范定义

一致性维度

维度及维度属性

在总线矩阵下，维度必须归属某一个数据域，维度属性的来源一种是源系统，一种是挖掘计算，如最近一次支付时间。

特殊维度

• 杂项维度：将事实表中的状态、分类等字段定义为维度，比如交易订单、物流订单中的状态等均可称杂项维度。

• 行为维度：基于历史事实构建，如会员最近一次支付时间就是一个行为维度，可作为其父维度会员维度的维度属性，不建议单独创建维度。

维度整合和拆分

• 维度整合

􎛏 同一业务板块下同维度不同属性信息可整合，如会员维度基本属性、星级等信息。

􎛏 不同业务板块同维度信息可整合，如天猫淘宝基本会员信息。

• 维度拆分

􎛏 同一维度不同分类的属性，差异较大或业务关联度不大的信息，如不同业务板块的商品维度可拆分成不同维度表。

􎛏 每个商品会有主属性（形状、颜色、价格，等）和扩展属性，比如有些产品的一个扩展属性是带电的，那么在物流业务上就会有相应的限制，因此需要根据其业务属性拆分到不同维度表。

􎛏 从产出时效、易用性考虑垂直拆分出主商品维表和商品维度扩展表。比如“跨境十日达”商品，会根据其业务属性划分维度属性，并放入维度扩展表。

命名规则

• 命名规则尽可能使用英文简写。

• 若英文简写过长，可考虑拼音首字母简写，如：商品itm，商家slr，买家byr。

最佳实践

以淘系常用维度来看命名规则。

一致性度量

基本原则

• 度量必须归属某一个业务过程。

• 度量类型：一般是数值，所以在定义数据类型时候一般为数字类型，同时需要和维度属性做区别。

• 度量分类：按照事实的是否可加性分为：可加性度量、半可加性度量、不可加度量。

命名规则

• 尽可能使用英文简写。

• 当英文简写很长可以考虑汉语拼音首字母的简写，但一般要保持整个数仓一致性，所以尽可能选择一种命名缩写规则，如：数量cnt、金额amt。

最佳实践

• 前面已经定义了业务过程，比如交易域的支付过程，接下来要规范定义交易业务过程涉及的度量，例如金额amt、单量cnt 等。

指标定义

基本原则

• 派生指标可以选择多个修饰词，唯一归属于一个原子指标，属于某一个数据域。

• 派生指标一般由原子指标和时间周期、若干修饰词构成。

• 派生指标的命名要继承原子指标的命名规则和名称，可以抽象为：修饰词+原子指标+时间周期。

命名规则

• 尽可能使用英文简写。

• 若过长，考虑拼音首字母简写，如：

􎛏 原子指标：支付单量pay_ord_cnt

􎛏 派生指标：近1 天淘特支付单量tt_pay_ord_cnt_1d

最佳实践

• 下图左边是逻辑结构图，右边是指标举例，最近1 天淘特支付单量的逻辑结构图：

•下图是以交易域的指标为例看命名规则：

􎛏 电商版块：ed

􎛏 交易域：trd

􎛏 支付：pay

􎛏 单量：ord_cnt

􎛏 订单：ord

􎛏 业务来源类型：From_group

􎛏 最近一天：1d

􎛏 淘宝特价版：tt

􎛏 支付单量：pay_ord_cnt

􎛏 订单：ord

􎛏 订单ID、订单状态：order_id，order_status

􎛏 最近1 天淘特支付单量：tt_pay_ord_cnt_1d

模型设计

设计原则

• 高内聚，低耦合

• 规范性，一致性

• 稳定性，可扩展

• 公共逻辑下沉

• 成本性，能平衡

• 支持多次回刷

维度表设计

设计流程

基本原则

• 缓慢变化维

Kimball 的三种处理方法：重写、插入新记录、插入维度列（仅限两次变更）；

阿里采用极限存储的方法。

• 维度的一致性

相同维度属性在不同物理表种字段名称、数据类型、内容必须一致。

• 维度的组合和拆分

􎛏 同一维度不同属性关联性强可以整合，如会员基础属性星级等。

􎛏 同一维度不同属性差异大的可拆分，如淘宝商品与航旅商品。

􎛏 同一维度不同属性关联度不强的可拆分，如会员表拆为买卖家维度表，如淘宝商品与CBU 商品。

􎛏 从产出时效、易用性、稳定性角度考虑是否需要拆分，如商品维度表和扩展表。

命名规则

dim_{业务BU/pub}{数据域}{维度定义}[_{自定义命名标签}]

最佳实践

以淘系事实表、交易订单表和其中冗余的商品、买卖家相关维度表为例来介绍。

下图是淘系商品维度表：dim_tb_itm

􎛏 维度：商品

􎛏 维度属性：商品标题、商品金额、商品颜色、主图等

􎛏 冗余属性：类目维度属性等

下图是淘系商品属性扩展维度表：dim_tb_itm_extend

扩展维度表从主维度表中拆分出来主要是有三个方面考虑：稳定性、产出时效、变化频次角度。

主维度表VS 扩展维度表：

主维度表

• 稳定

• 产出时间早

• 热度高

扩展维度表

• 变化较快

• 产出时间晚

• 热度低

事实表设计

a) 事务型事实表

• 针对业务过程构建的一类事实表，用以跟踪定义业务过程的个体行为，是数仓

最原子的明细数据，提供丰富的分析能力。

• 按照所描述的业务过程的数量分为单事务事实表和多事务事实表。

设计流程

基本原则

􎛏 完整性：尽可能包含所有与业务过程相关的事实。

􎛏 高内聚低耦合：只选择和业务过程相关的事实。

􎛏 粒度明确：在同一个事实表中，粒度必须唯一。

􎛏 成本性能的平衡：使用退化维度提高事实表的易用性。

b) 单事务事实表

淘宝下单事件事务事实表：dwd_tb_trd_ord_di

基本特征

􎛏 业务过程：订单创建

􎛏 事实表类型：单事务事实表

􎛏 粒度：子订单ID

􎛏 度量：订单创建金额等

􎛏 冗余属性：冗余商品、会员属性

􎛏 数据存储：仅插入不更新、每个实体在整张表只有一条记录

适用场景

􎛏 单业务过程，如下单、支付等

􎛏 单业务过程分析无需限定业务过程

􎛏 举例：双11 下单单量

Select count （ order_id ） from tbcdm.dwd_tb_trd_ord_di where

ds=‘20211111’

冗余原则

􎛏 冗余维度属性下游常用

􎛏 冗余维度属性不影响产出时效

c) 多事务事实表

淘宝多事务事实表：dwd_tb_trd_ord_ent_di

基本特征

􎛏 业务过程：订单创建→支付→完结

􎛏 事实表类型：多事务事实表

􎛏 粒度：子订单ID

􎛏 度量：订单创建金额、支付金额等

􎛏 冗余属性：冗余商品、会员属性

􎛏 数据存储：仅插入不更新，每个分区存储每个实体的最新业务过程状态，通常以打标方式标识其业务过程（如表中的“是否当天下单”、“是否当天支付”、“是否当天确认收货”），每个实体因其业务过程更新情况，在整张表可能有多条记录

适用场景

􎛏 适合一次分析多个业务过程的场景

􎛏 此场景下计算存储相比单事务节约、取数更便捷

􎛏 举例：查询20220110 当天下单当天支付的订单

select * from tbcdm.dwd_tb_trd_ord_ent_di where ds = ‘20220110’ and

is_create = ‘Y’ and is_pay= ‘Y’

d) 累积快照事实表

订单全流程表：dwd_tb_trd_ord_flow_di

基本特征

􎛏 业务过程：订单创建->支付->完结

􎛏 事实表类型：累积快照事实表

􎛏 粒度：子订单ID

􎛏 度量：订单创建金额、支付金额等

􎛏 冗余属性：冗余商品、会员属性

􎛏 数据存储：

每日更新，存储每个实体的最新状态，每个实体在整张表中仅有一条记录每天完成的订单存储在当天日期的分区，而未完成的订单统一存储在300001231 分区

适用场景

􎛏 多个业务过程，联合分析，如下单到支付、支付到订单完结时长的分析

􎛏 保存全量数据、常见于订单处理

􎛏 举例：近7 天支付订单从下单到支付平均时长

select sum （ pay_time-create_time ） /count （ order_id ） from

tbcdm.dwd_tb_trd_ord_flow_di where pay_time>$bizdate-7and（ds>${bizdate-7} or ds= ‘30001231’）

e) 周期型快照事实表

设计流程

基本原则

• 数据公用性

􎛏 汇总层的产出是否有多个下游使用

􎛏 维度的聚集是否有多个下游用于分析等

􎛏 高内聚低耦合：dws 层不跨域计算存储

􎛏 可扩展性：区分统计周期，如果要拆分，需要按照原子周期去拆分到多个dws

命名规则

􎛏 dws_{业务BU 缩写/pub}{数据域缩写}{数据粒度缩写}[{自定义表命名标签缩写}]{统计时间周期范围缩写}

􎛏 比如：dws_tb_itm_slr_td（商品域商家粒度汇总表）

􎛏 tb：业务域

􎛏 itm：数据域

􎛏 slr：统计粒度

􎛏 td：时间周期（截至当天为止）

最佳实践

以淘系事实表商家粒度商品汇总表和其中冗余维度店铺维度为例来介绍：

商家粒度商品汇总表：dws_tb_itm_slr_td

基本特征

􎛏 粒度：商家ID

􎛏 指标：历史截至当日商品数等

􎛏 业务周期：每日

􎛏 物理表类型：周期快照事实表

􎛏 冗余维度：店铺ID

􎛏 数据存储：每日更新，每个分区存储每个

􎛏 实体的最新状态

适用场景

􎛏 时间周期跨度比较大，实体的当前状态

􎛏 不需要每次读时从事务事实再计算

􎛏 举例：该商家历史截至当日共上线的商品数

Select itm_cnt_std from tbcdm.dws_tb_itm_slr_td where ds=‘${bizdate}’