数仓【规范建模篇-万字详解】_数仓建模规范-优快云博客

三大范式：（1NF）：要求数据库表的每一列都是不可分割的原子数据项，即列中不可再分，确保每列保持原子性。
（2NF）：在满足第一范式的基础上，非主键列必须完全依赖于主键，即每个表必须有一个主键，其他数据项与主键一一对应，消除非主属性对主键的部分依赖。
（3NF）：在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即消除传递依赖，非主键列不能依赖于其他非主键列。

1.3:规范建模术语概念:

主题域：联系较为紧密的数据主题的集合。可以根据业务的关注点，将这些数据主题划分到不同的主题域。

主题：简单点说每一个主题对应一个宏观分析领域。代表着分析事实数据的不同角度。

实体：实体是一个数据对象，指应用中可以区别的客观存在的事物。例如：商品、用户、学生、课程等

业务过程：是企业活动中的事件，如下单、支付、退款都是业务过程，业务过程是一个不可拆分的行为事件。

主题域、主题、实体间关系：主题设计是对主题域进一步分解，细化的过程。主题域下面可以有多个主题，主题还可以划分成更多的子主题，而实体则是不可划分的最小单位

数据域：数据域是指面向业务分析，将业务过程或者维度进行抽象的数据集合。

数据域和主题域的区别：主题域：是针对数据集市提出的概念

数据域：是阿里数据中台 CDM 层建设中提出的，是数据驱动业务，是对数据的分类，更好的数据赋能业务。

总结：

数据域是对数据的分类，主题域和业务域是对业务的分类。

主题域和数据域:最终都是对数据的分类，只是一个是数据视角，一个是业务视角。

根本的目的是：统一规则，方便管理，容易理解，有利于开发效率，有利于快速服务业务场景就可以了。

二：典型的数仓建模方法论

2.1:ER模型：

高层模型：描述主要主题以及主题之间的关系，用来描述企业业务总体概述
中层模型：在高层基础上，细化主题数据项
物理模型：中层基础上，考虑物理存储。基于性能和特点进行物理属性（表分区，合并等）设计。

2.2:维度模型：

选择业务过程：可以是单个业务事件（交易支付/退款等），也可是相关业务事件组成的业务流程（支付，发货，结算。。。）

选择粒度：粒度可以是单个维度也可以是维度的组合（比如选择订单粒度，那么相应的店铺，客户等维度都可以选择）

相关维度：根据业务需求，需要分析的维度。

选择事实：确定分析时，需要衡量的指标

（维度建模分层详情，请见后续）

2.3:DATA VAULT模型：

HUB：企业核心业务实体

LINK:代表hub之间的关系

SateLLite：是HUB详细描述内容

2.4:Anchor模型：

Anchors：类似DATA VAULT 的 hub

Attributes：类似于DATA VAULT 的 SateLLite

Ties：Anchors之间的关系

Knots：代表可能会公用属性的提炼

阿里数据模型实战演变：

一阶段：oracle只有 ods和DSS两层

二阶段：mpp架构：ODL-BDL-IDL-ADL 但是在引入 ER 模型的过程遇到了困难。因此在业务变化快速，经验不够的情况，不太适合构建ER模型

三阶段： hadoop分布式+维度建模为核心的建模理论

这期主要详解常用的维度建模理论。

三：维度建模-分层

模型分层：

分层简介：
数据仓库建模通常包括以下层次：

ODS 层：

该层是面向原始数据的层，用于存储从源系统同步过来的数据，保留最原始的数据形态和数据历史。

数据来源：ODS层通常会集成多个数据源/系统的数据，例如不同系统的业务数据，日志数据、外部数据等。
数据处理：在ODS层中，数据通常以原始格式存储，并且保持尽可能接近源系统的结构和粒度，这样后续的数据溯源会很方便。部分情况也会对数据进行一些基本的清洗、验证和修复，以确保数据的质量和完整性。（具体看公司业务需求）

DWD 层：

该层主要负责对ODS层数据进行清洗（去除不符合要求的数据）、规范化（统一数据格式）、规范（确保数据质量）。

DWS层：

该层提供了面向主题的汇总数据，进行了聚合操作，方便后续的分析。

DIM：

通常存储维度数据，如用户属性、商品属性等，为分析提供描述维度。

ADS层：

提供面向业务的数据服务，可以为各类分析应用提供数据支持。

每层数据的作用和目的都是为了更好地支持数据分析和决策制定。以上层次的划分是大多数数据仓库建模的通用方法，但具体实施时可能会根据项目需求和公司规范进行调整。

（维度表，事实表，dws汇总表请看后续详解）

总体遵循的层次调用原则如下：

ODS层数据不能直接被应用层任务引用。如果中间层没有沉淀的ODS层数据，则通过CDM层的视图访问。CDM层视图必须使用调度程序进行封装，保持视图的可维护性与可管理性。
CDM层任务的深度不宜过大（建议不超过10层）。
一个计算刷新任务只允许一个输出表，特殊情况除外。
如果多个任务刷新输出一个表（不同任务插入不同的分区），最好需要建立一个虚拟任务，依赖多个任务的刷新和输出。通常，下游应该依赖此虚拟任务，避免实际应用的表产生数据不全的情况。
CDM汇总层优先调用CDM明细层，可累加指标计算。如果有已经产出的部分粒度汇总数据，CDM汇总层尽量优先调用已经产出的粗粒度汇总层，避免大量汇总层数据直接从海量的明细数据层中计算得出。
CDM明细层累计快照事实表优先调用CDM事务型事实表，保持数据的一致性产出。
有针对性地建设CDM公共汇总层，避免应用层过度引用和依赖CDM层明细数据。

（后续着重讲解事实表和维度表的设计）

四：维度表详解：

4.1：维度表设计

维度建模中度量为事实，环境描述为维度。

维度表中列为维度属性，查询约束，条件，分组，报表标签的来源。所以维度是查询约束，分类汇总以及排序。

维度建模的设计方法：

第一步：选择维度/新建维度。必须保证维度的唯一性，比如商品，只允许有且只有一个维度定义。（即维度的统一）

第二步：确定主维表

第三步：确定相关维表

第四步：确定维度属性（维度属性可以从主维表选择或者生成新的属性，第二是从相关维度选择属性或者生成新的维度）

确定维度属性的提示：

尽可能丰富维度属性
尽可能多给有意义的文字描述
区分数值型属性和事实（主要看用途）
尽量沉淀出通用的维度属性

关于规范化和反规范化：将维度属性层次合并到单个维度称为反规范化，雪花模型为规范化，但是需要大量的关联操作，查询性能也很差，然而反规范化更方便易用性也很好。在实际操作中，几乎总是使用维表的空间来换取简明的查询性能。

维度一致性的几种表现形式：

共享维表：一种维度只允许存在一个

一致性上卷：利用一种维度的子集维度，进行不同业务过程的探查也不会有问题

交叉属性：两维度有相同类目属性，则可以在相同的类目属性上进行不同业务过程交叉探

查

总线矩阵

维度建模的数据仓库中，有一个概念叫Bus Architecture，中文一般翻译为“总线架构”。总线架构是Kimball的多维体系结构（MD）中的三个关键性概念之一，另两个是一致性维度（Conformed Dimension）和一致性事实（Conformed Fact）。

一致性维度和事实，企业数据仓库应该建立一个一致性维度和事实，而不是为每个部门建立维度和事实。

一致性维度：具有一致的维度关键字，一致的属性列名称，一致的属性定义和一致的属性值。一致性维度要么是统一的，要么是维度表的一个子集。

一致性事实：指每个度量在整个数据仓库中都是唯一的统计口径，为了避免歧义，一个度量只有唯一的业务术语。

一致性维度就好比企业范围内的一组总线，不同数据集市的事实的就好比插在这组总线上的元件。这也是称之为总线架构的原因。

总线矩阵（Bus Matrix）: 实际设计过程中，我们通常把总线架构列表成矩阵的形式，其中列为一致性维度，行为不同的业务处理过程，即事实，在交叉点上打上标记表示该业务处理过程与该维度相关。这个矩阵也称为总线矩阵（Bus Matrix）。

4.2：维度高级设计

数据仓库是面向主题，集成，非易失的且随时间变化的数据集合

数据集成的规范统一：

1.命名规范的统一：表名，字段名等的命名规范

2.字段类型的统一：维度表内字段的类型统一，便于后续使用

3.公共代码以及代码值的统一：如枚举类型的字段，每个code所代表的业务含义要进行统一规范

4.业务含义相同的表的统一：将业务含义关系大源系统影响小的表进行整合，将业务关系小，源系统影响差异大的表进行分而治之：

其中表的统一通常有以下几种方式：

主从表的设计：主表主要信息，从属信息放在各自从表

直接合并：共有和个性全部放在同一个表，如果重合度低，则会出现大量空值

不合并：表结构差异大，无法合并

具体选择以上哪种方案重点考虑三个原则：

扩展性：系统或者逻辑有变能以预知的最低成本进行扩展（高内聚，低耦合是核心思想）

效能：性能和成本之间取得平衡

易用性：下游使用/重复使用是否便捷

维表的整合拆分：

维表整合：

垂直整合：多重维度列的形式丰富其维度

水平整合：多种维度以行的进行整合，需要考虑去重以及自然键的设置

维表拆分：

水平拆分：

主从维表形式拆分：主维表一般存放公共属性，从维表一般存放个性属性

单独个体维表形式拆分：按照业务拆分成单独个体表，不存在主从依赖关系

整体思想和维表整合类似，从维表可以放公用属性，也可以放自己特有的属性

垂直拆分：（列信息）

主从维表：扩展性，产出时间，易用性三方面考虑，设计主从维表。主维表放时间早，热度高，从维表放变化快，产出晚，热度低的数据。（冷热分离，数据时效性优先）

历史归档：数据增长较快，但是部分数据会存在过期现象，比如商品，可以考虑数据归档，一张历史商品表，一张目前使用的维表，按照前台的过期政策进行数据归档。（常见的有同前台归档，获取日志的删除标志，自定义策略归档算法简单化原则是比前台晚归档，少归档）

4.3：其他维度

缓慢变化维：

三种处理缓慢变化维的方式：

一：重写维度值:不保留历史数据，始终是最新数据（相当于更新覆盖）

二：插入新的维度值:保留历史数据，插入新的行

三：添加维度列：可以将变化的记录归一为变化前/后的维度

具体选择哪一种，看当时的具体业务需求。数据统计是否要归一前/后的状态还是要分开到时间线前后的状态，还是只取最新的。:

快照维表：

由于代理键在数仓系统会增加ETL的复杂性，所以一般对于缓慢变化维，是每天一个全量快照，好处是简单有效，使用方便。缺点是存储浪费。

极限存储：

既能做到快照维表的优点，也能降低存储成本，就是阿里的极限存储

底层依赖于历史拉链存储（全量数据）,上层做一个视图操作，查询数据更加精确，拉链表用月分区。

递归层次维度：

简介：降低递归层次使用复杂度的最简单/最有效的方式就是层次结构扁平化，建立维度固定数量的级别类目来实现，一定程度上解决上下钻问题。

举例：比如：表结构- 类目名称层级一级二级三级。。。一个类目一条记录

扁平化仅适用于固定数量的级别，非平衡层级结构可以通过预留级别的方式解决，但是扩展性会很差。

缺点：上下钻之前必须知道所属类目层级，而且有的比如一二级类目直接是叶子类目，和事实表关联，二级三级关联不到，那么就取上级类目进行统计

层次桥接表：针对层级结构扁平化的问题，可采用桥接表的方式解决，也可解决非均衡结构的问题。

桥接表：表结构- 父类目id，子类目id，类目层级间隔。这样可以不需要知道所属层级，不需要回填。（如果是非固定层级维度，可以通过桥接表和行形式存储即纵表，这样扩展性高也可以知道他的父子节点）（桥接表需要额外的开发工作量）

行为维度：

即为事实衍生的维度，按照加工方式可以划分为几下几种：

另一维度的过去行为

对于行为维度有两种处理方式：

一：将冗余至现有的维表中

二：加工成单独的行为维表

具体哪种方式，主要参考两大原则：第一：避免维度过快增长，第二：避免耦合度过高。

多值维度：

事实的一条记录在维表中有多条记录与之对应，针对多值维度常见的处理方式有三种：

一：降低事实表的粒度，如果父订单有一对多，那就降低到子订单粒度去分析订单主题，相应的事实字段分摊到子订单上

二：采用多字段：比如房产合同会有多个买卖方的情况，但是合同已经是最细粒度了，这种可以在维度表增加字段（考虑到扩展性可以预留字段，但是扩展性还是不好）。

三：桥接表，较为通用的方式。桥接表设置分组key，将多值id放到桥接表这一个组里，事实表内为分组key。但是桥接表开发和维护成本较高，简单方法能实现的就不用桥接表。

多值属性：多值维度的另一种表现形式：

一：k-v形式存放数据

二：多值按照多列形式存放

三：多值按照多行记录存放（主键发生变化）

杂项维度：（详情见后续事实表描述中的杂项维度）

五：事实表详解

5.1：事实表设计

事实表特性：

组成：围绕业务过程来设计，包含度量业务过程有关的维度。

粒度：事实表一条记录表达的业务细节称为粒度。

度量：（一般为整型或者浮点型）

可加性：任意维度进行汇总

半可加性：只可按照特定维度汇总

不可加性：比如比率型的事实，对于不可加性可以分解为可加性来实现聚集

事实表类型：

事务事实表：描述业务过程，保存的最原子的数据。

周期快照事实表：有规律/可预见的时间间隔记录事实。

累计快照事实表：表述过程开始和结束之间关键步骤事件，通常有多个日期字段来记录关键时间点，当过程随着生命周期不断变化，记录也会随着过程变化而修改。

事实表设计原则：

一：尽可能包含所有和业务过程相关事实

二：只选择当前业务过程相关的事实

三：分解不可加事实为可加组件

四：选择维度和事实之前，先声明粒度（一般都是选择最细粒度）

五：一张事实表不会有多种不同粒度的事实

六：事实的（相关事实：支付金额，优惠金额等）单位保持一致

七：NULL值的处理

八：退维提高事实表的易用性和简单性（存储换效率）

事实表设计方法：

一：确定业务过程以及确定事实表类型

二：声明粒度

三：确定相关维度

四：确定事实（度量）

五：冗余维度

5.2：事实表分类

5.2.1：事务型事实表

设计过程：和上述过程一致

事务事实表的分类：

单事务事实表：即每个事实表只有一个业务过程，

多事务事实表：即为一个事实表包含不同的业务过程，在设计时有两种方法进行处理：（在设计过程中也得考虑多业务过程，会不会造成合并困难，业务相关性差等问题）

一：不同业务过程的事实使用不同的事实字段进行存放，如果不是当前业务过程的度量，取零处理。其中还会有多个业务过程时间字段。如果想标记目前属于哪个业务过程，可以采取是或否当前业务打标记的形式。（是否当前下单，是否当天支付，是否当天结算。。。）

二：不同业务过程的事实用同一个字段存放，但增加一个业务过程的标签，核心思想是二者业务相近，采用标签字段区分不同业务过程，事实是同一个字段。

多事务事实表的处理方式选择：

度量和业务相似，采用第二种处理方式。但是一个周期内会存在多条数据。

度量/业务差异较大，选择第一种处理方式。但是度量会有大量空值。

两种事实表的对比：

业务过程方面：单事务只放一个，多事务看这些事务过程是否相似

粒度和维度方面：单事务不用考虑，多事务必须粒度相同，其次维度最好高度相似，否则冗余数据过多。

事实方面：事实少且事实相似度高，可多事务，否则相反。

下游使用方面：多事务学习和使用成本相对较高

计算存储成本方面：多业务过程来自同一系统且事实较少，维度较多可以考虑多事务，计算成本和存储成本都低。其他看具体情况那种更适合

事实的设计准则：完整性，一致性，可加性（不可加性，可进行分子分母的拆分）

5.2.2：周期快照事实表

事务事实表可以跟踪时间，并对其度量，提供分析能力。但是需要一些状态度量（如：余额，星级，库存等）需要聚集相关的事务才可计算，或者聚集无法识别如温度等的信息。快照事实表在确定间隔对事实抽样，不需要聚集长期的事务历史。

特性区分：

粒度：事务事实表粒度可以多种方式区分，快照事实表通常以维度组合形式

数据稠密/稀疏：事务事实表稀疏，快照事实表稠密

事实可加：事务事实表事实完全可加，快照事实表至少包含一个半可加性事实。（数值都是累计后的的，再次按照时间维度累计没有意义）

特性：

1.用快照采样（记录）状态

2.快照粒度：时间周期加维度声明粒度

3.密度与稀疏性：事务事实表保存当天发生的业务过程，但是周期快照表，不管今天有无发生，都会记录。在数据层面就是：前者增量，后者每天全量快照

4.半可加性：周期快照都是半可加性，因为是周期时间内的状态汇总记录，所以针对一些时间维度再进行汇总，没有任何意义，可以通过其他合适的维度进行汇总。

数据来源：

事务事实表：一般较为简单的周期快照事实表，可以通过事务事实表产出，这也是较为常见的一种产出方式。

操作性系统源数据：直接使用操作性系统数据作为周期快照的数据源

注意事项：

1.事务和快照事实表一般是成对设计，互相补充，而且大多快照表可以通过事务表加工出来。这样既丰富了模型，又方便了下游使用。

2.附加周期事实：周期快照表一般保存周期结束时候的状态，但是部分需求也需要看上期的状态，所以可以适当保存上周期的状态度量，具体看业务需求。

3.周期的多样性：数据不同业务不同所需的周期不同，不止历史至今，以及每天等，阿里dataphin还有自然年，月，最近七天。。。。。

5.2.3：累计快照事实表

对于研究类似事件之间时间间隔的需求，采用累计快照事实表可以很好解决。

设计过程：过程和事实表的过程一致，但是累计快照本就描述事件之间的间隔需求，所以一定是多事务的。对于累计快照表，用于考察实体的唯一实例，事件发生时对此实例进行更新。

累计快照事实表重点解决不同业务过程之间的时间间隔，建议将每个过程的时间间隔作为事实也放在事实表中。

适用场景：

累计快照表适用于有较明确的起止时间的生命周期的实体，比如订单，物流订单等之流。像商品，用户等的有较长生命周期的实体，一般采用周期快照事实表较合适。而且累计快照表还有个作用就是在明细层保存了一份全量数据。

特殊处理场景：

非线性过程：

业务过程统一：统一流程开始消亡的标志

构建全面流程：对于不是按照标准流程走的流程，全流程任然可以覆盖，没有发生的业务过程，相关事件字段和事实置为null。

多源过程：由于累计表有多业务，每个业务可能来自不同系统，这个主要考虑粒度的统一问题，确保模型粒度不变。

物理实现：

一：全量表的形式：每天的分区存储昨天的全量和当天的增量，相当于每天一个全量快照。但是只适合用于全量数据较小的情况，数据量大会存储大量不再变化的数据，性能降低。

二：全量表的变化形式：针对表全量数据较大的情况，较短的生命周期业务实体从产生到消亡都有时间间隔，根据需求确定最大时间间隔比如100天，那么每天的分区存储近100的数据，超过这个时间的存储在归档中。

三：业务实体的结束时间分区：每天的分区只存放，当前结束的数据，设计一个时间非常大的分区。每天将结束的数据放在每个分区，未结束的数据存放至那个分区，这样两种分区的数据都不会很大，而且没有存储浪费。

但是以上第三种可能存在特殊情况：业务系统无法表示业务实体的结束时间或者无法标记实体结束。有以下两种处理方式：

方式一：用相关的业务实体结束标志作为此业务的结束标志，比如用订单代替物流的结束。（一般订单结束了，那么物流坑定也结束了）

方式二：和前端系统确定口径或者归档策略。和前端确定口径，确定最大消亡时间，前端也会定期对历史数据进行归档，也可使用前端的归档时间作为业务实体的消亡时间。

5.3：三种事实表的比较

三种事实表的比较：

	事务事实表	周期快照事实表	累计快照事实表
时期/时间	事务时间点	规律的时间间隔	时间跨度不确定的工作流
日期维度	事务日期	快照日期	多个流程日期
粒度	每行代表实体事务	每行代表时间周期的一个实体	每行代表一个实体生命周期
事实	事务事实	累计事实	相关业务时间间隔事实
加载	插入	插入	插入与更新
更新	不更新	不	业务过程变更时更新

无事实的事实表：第一类是记录时间发生类，第二是类条件，范围，资格等的事实表

5.4：维度建模-常见模型

在数据仓库和维度建模中，存在三种常见的模型：星形模型、雪花模型和星座模型。它们是用于组织和表示数据仓库中的维度和事实数据的不同结构。

星形模型：

星形模型（Star Schema）：星形模型是最简单和最常见的维度建模结构。在星形模型中，一个中心的事实表（包含事实数据）与多个维度表（包含维度数据）通过主键-外键关系连接。这种模型的特点是简单、扁平化且易于理解。事实表通常包含与业务过程或业务事件相关的指标，而维度表则包含描述业务上下文的属性。星形模型适用于大多数分析和查询场景，提供了快速的查询性能和直观的数据模型。

雪花模型：

雪花模型（Snowflake Schema）：雪花模型是星形模型的扩展，用于处理更复杂的维度关系。在雪花模型中，维度表进一步分解成多个规范化的子维度表，形成了层次结构。这些子维度表通过外键关联，并与事实表形成多级的关系。雪花模型的优点是可以更好地处理维度间的关系和层次结构，提供更详细和灵活的数据分析。但它也增加了数据模型的复杂性和查询的复杂性，可能影响查询性能。

星座模型：

星座模型（Constellation Schema）：星座模型是星形模型和雪花模型的混合形式，用于处理更为复杂的数据关系。在星座模型中，可以同时存在星形模型和雪花模型结构，根据不同的维度关系使用不同的模型结构。这种模型结构提供了更大的灵活性和可扩展性，可以满足不同维度关系的需求。星座模型可以根据具体的业务场景和数据分析要求进行设计和优化。