OneData方法论-事实表设计

事实表设计

事实表特性

事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程来设计。

事实表中一条记录所表达的业务细节程度被称为粒度，粒度可以通过两种方式来表达：一种是维度属性组合所表示的细节程度，一种是所表示的具体业务含义。

作为度量业务过程的事实，一般是整数或浮点型的十进制数值，有可加性、半可加性、不可加性三种。可加性是指可以按照与事实表关联的任意维度进行汇总，半可加性是指可以按照部分关联维度进行汇总，不可加性例如比例性度量，是不能按照维度进行汇总。

维度属性也可以退化到事实表中，退化维度可以用于进行事实表的过滤查询、聚合等。

事实表设计原则

• 原则1：尽可能包含所有与业务过程相关的事实

  事实表设计的目的是为了度量业务过程，所以分析哪些事实与业务过程有关是设计中非常重要的关注点。

• 原则2：只选择与业务过程相关的事实

  在选择事实时，应该注意只选择与业务过程有关的事实。比如下单这个业务过程的事实表设计中，不应该有支付金额这个表示支付业务过程的事实。

• 原则3：分解不可加性事实为可加

  对于不具备可加性条件的事实，应该分解为可加性的事实。比如订单优惠率，应该拆分为订单原价金额和优惠金额两个可加性事实。

• 原则4：在选择维度和事实之前必须声明粒度

  粒度用于明确表中一行表示业务的细节层次，决定了维度模型的扩展性，在设计事实表的过程中，粒度定义的越细越好，这样会提供最大限度的灵活性。

• 原则5：在同一个事实表中不能有多种不同粒度的事实

  单个事实表中所有事实粒度要与表定义粒度保持一致，在同一个事实中不能有多种粒度。例如支付成功事务事实表，粒度为票一级，而不是按照订单一级。

• 原则6：事实的单位要保持一致

  单个事实表内度量的单位应该保持一致。

• 原则7：对事实的null值要处理

  表中不允许存在null值。

• 原则8：使用退化维度提高事实表的易用性

  可以将部分维度属性退化到事实表中提升事实表的处理效率。

事实表设计方法

《数据仓库建模方法论》一书中将维度设计定义为四步设计方法：选择业务过程，声明粒度，确定维度，确定事实。

第一步：选择业务过程以及确定事实表类型。

以一个订单流转为例：

业务过程通常使用行为动词表示业务执行的活动。比如流程中包含的业务过程有四个：创建订单、买家付款、卖家发货、买家确认收货。

在明确了流程所包含的业务流程后，需要根据具体业务需求选择业务过程。是选择“卖家发货”这个业务过程还是“创建订单”和“买家付款”，这都需要根据业务情况讨论。

选择业务过程后，相应的事实表类型也随之确定。比如选择“卖家发货”这个业务过程，因为只有单个业务过程，所以可以设计单事务事实表；如果选择四个业务过程，并且需要分析四个业务过程的时间间隔，那么就需要建立包含所有业务过程的累计快照事实表。

第二步：声明粒度

应当选择最细级别的原子粒度。

第三步：确认维度

应该选择能够描述清楚业务过程所处的环境的维度信息，例如买家付款事务事实表，粒度为子订单，相关维度有买家、卖家、商品、收货人信息、业务类型、订单时间等维度。

第四步：确定事实

事实可以通过回答“过程的度量是什么”来确定。应该选择与业务过程有关的所有事实，且事实的粒度要与所声明的事实表粒度一致，同时对不可加性事实进行拆分。

第五步：冗余维度

在确定事实后可以将一些维度表数据冗余进事实表来加速事实表聚合、查询功能，降低资源消耗。

事实表类型

事务事实表

任何类型的事件都可以被理解为一种事务。比如交易过程中的创建订单、买家付款，物流过程中的揽货、发货、签收，退款中的申请退款、申请小二介入等，都可以被理解为一种事务。

而事务事实表就是针对这些过程构建的一类事实表，用以跟踪定义业务过程的个体行为，提供丰富的分析能力。

我们使用事实表设计方法的例子建设一个单事务事实表：

第一步：选择业务过程

这步不在赘述。

第二步：确定粒度

在下单时，有两种方式，第一种是选定商品直接购买，这样会产生一个交易订单；另一种是将多种商品加入购物车中，然后一起结算，此时对于每一种商品都会产生一个订单，同时对于同一个店铺会额外产生一个父订单；由于在同一个店铺购买，会有父订单承载订单物流，优惠等信息。

而每一种商品产生的订单就会被称为子订单，子订单记录父订单的订单号，并且有子订单标志。如果在同一个店铺只购买了一种商品，则会将父子订单合并，只保留一条记录。

在创建订单、买家付款、卖家发货、买家确认收货四个业务过程中，创建订单、买家付款、买家确认收货可以使用子订单粒度。

但是在卖家发货中一个子订单可以拆开成多个物流单进行发货，而秉承最细粒度的原则，对卖家发货确定为物流单粒度。

第三步：确定维度

确定维度包含：买家、卖家、商品、商品类目、发货地区、收货地区、父订单维度以及杂项维度。由于订单的属性较多，比如订单的业务类型、是否无线交易、订单的 attributes 属性等，对于这些使用较多却又无法归属到上述买卖家或商品维度中的属性，则新建一个杂项维度进行存放。

第四步：确定事实

作为过程度量的核心，事实表应该包含于其描述有关的所有事实。

第五步：冗余维度

在确定维度时，包含了买卖家维度、商品维度、类目维度 、收发货维度等，为了提高对事实表的过滤查询、统计聚合的效率，会进行维度冗余。

单事务事实表

单事务事实表是针对每个业务过程设计的一个事实表。这样设计可以方便对每个业务过程进行独立的分析研究。还是按照交易流程举例，单事务事实表会将每一个业务过程设计为事务事实表。

之后每天的下单记录进入下单事务事实表，每天的支付记录进入当天的支付事务事实表，由于事实表的稀疏性质，有可能出现当天下单但是在第二天才完成支付的情况。

多事务事实表

多事务事实表将不同的事实放在同一个事实表中，即同一个事实表包含不同的业务过程。多事务事实表在设计时有两种方法进行处理：①不同业务过程的事实使用不同的事实字段进行存放；②不同业务过程的事实使用同一个事实字段进行存放，但增加一个业务过程标签。

第一种方式：

在建设多事务事实表时必须确保融合在一张表里的业务过程粒度统一，当然不同的业务过程关联的维度属性肯定是不完全一致的，但是可以在维度层次保证这几个业务过程放在同一个事务事实表中。

接着便是思考如何将不同业务过程度量放在同一张表中，因为选择第一种方式，所以不同度量由不同字段进行存放，如果不是当前业务过程的度量，则默认为0。

既然一张事实表中包含多个业务过程，在表中就要针对每个业务过程打标签，标记当天是否是这个业务过程，比如针对下单，就打一个是否当天下单的标签，针对交易完成，则打一个是否当天完成的标签。

第二种方式：

这里我们使用收藏商品的业务来讲述，收藏业务比较简单，只有收藏商品和删除商品两个动作，粒度也可以确定为用户+商品。

收藏商品和删除商品是两个不同的业务过程，但是确定了相同的粒度和维度，所以考虑设计多事务事实表时，可以将两个业务过程放到同一个表中，但是采用的是第二种方式，将不同度量使用相同字段存放。

比如收藏事务事实表使用一个“收藏事件类型”字段来区分是收藏商品还是删除商品。虽然其事实主要度量是商品的价格，但是更多用来统计收藏和删除的次数。

当不同业务过程的度量比较相似时，可以采用第二种设计方式。
当不同业务过程的度量差异较大时，可以采用第一种设计方式。

单事务事实表与多事务事实表对比

	单事务事实表	多事务事实表
业务过程	一个	多个
粒度	不相关	相同粒度
维度	不相关	一致
事实	只取当前业务过程的事实	保留多个业务过程中的事实，非当前业务过程使用置零处理
冗余维度	多个业务过程，需要冗余多次	不同业务过程只需要冗余一次
理解程度	易于理解，不会混淆	难以理解，需要通过标签来限定
计算存储成本	较多，每个业务过程都需要计算存储一次	较少，降低了存储计算量，但是提高了计算复杂度

父子事实表处理

在上文中存在父子订单事实，当一个父订单存在多个子订单的时候，就需要将下单综合或者支付总额分摊到每个子订单上。

子订单下单全额 ＝ 
	下单商品数量 × 商品价格

子订单分摊的有效下单全额 ＝ 
	下单商品数量 X 商品价格 ＋ 
	父订单邮费 X 下单分摊比例 - 
	子订单折扣 - 父订单折扣 - 下单分摊比例

下单分摊比例 ＝ 
	(下单商品数量 × 原价 - 子订单折扣) / 
	SUM(下单商品数量 × 原价 - 子订单折扣)

子订单分摊的支付金额 ＝ 
	父订单支付金额 × 支付分摊比例

支付分摊比例 ＝ 
	(下单商品数量 × 原价 - 子订单折扣＋调价) / 
	SUM(下单商品数量 × 原价 - 子订单折扣 ＋ 调价)

周期快照事实表

事务事实表可以很好的跟踪一个事件，并对其进行度量提供丰富的分析能力。但是当需要一些状态度量时，比如账户余额、买卖家星级、库存、累计交易额等，则需要聚集与之相关的事务才能够进行识别计算。

对于这些状态度量，维度建模给出了第二种常见的事实表–周期快照事实表（快照事实表）。

快照事实表在确定时间对实体的度量进行抽样，这样可以更容易的研究实体的度量，而不需要聚集长期的事务历史。

特性

快照事实表的设计与事务事实表有很大区别，主要体现在快照事实表的粒度通常以维度形式声明，快照事实表是稠密的，快照事实表至少包含一个用来展示半可加属性的事实。

第一，用快照采样状态

快照事实表以预定的间隔采样状态度量。这种间隔联合一个或多个维度，用来定义快照事实表的粒度。

以交易卖家自然年汇总事实表为例，卖家需要看到一些交易状态数据，比如自然年至今或历史至今的下单金额，支付金额，支付买家数等状态度量，这些状态度量可以通过事务事实表来聚集，但是随着时间跨度越大，聚集效率会越来越低，因此需要设计快照事实表进行状态的度量。

粒度

快照事实表的粒度通常被多维度声明，可以简单的理解为快照需要采样的周期以及什么需要被采样。以上述表为例，粒度可以被理解为每天针对卖家的历史截至当日的下单支付金额进行快照。

当然，时间周期是按照业务需求来定的。

密度和稀疏性

快照事实表与事务事实表比较，他是稠密的，无论当天是否有业务过程发生，都会记录一行，比如针对卖家的历史至今的下单数和支付金额，不管卖家当天是否达成交易，都会记录一行。稠密性是快照事实表的重要特征。

半可加性

快照事实表中的状态度量是半可加的，半可加事实只能在部分维度中获得有意义的汇总结果。

设计步骤

• 确定快照事实表的快照粒度
• 确定快照事实表采样的状态度量

单维度的每天快照事实表

第一步，确认粒度

采样周期为每天，针对卖家、买家、商品、类目、地区等维度的快照事实表，不同采样粒度确定了不同的快照事实表。

第二步，确定状态度量

确定好粒度以后，就要针对这个粒度确定需要采样的状态度量。

混合维度的每天快照事实表

混合维度相对于单维度，只是在每天的采样周期上针对多个维度进行采样。

单维度和多维度的快照事实表都有一个特点：都可以从事务事实表进行汇总产出，这是周期快照事实表常见的一种产出模式。初次之外还有一种产出模式，即直接使用业务系统的数据作为周期快照事实表的数据源进行加工，比如卖家星级、DSR事实表等。

全量快照事实表

第一步，确定粒度

好中差评每天都在变化，下游统计分析也每天都在进行，因此确定采样周期是每天，这里的采样维度比较特殊，是针对评价本身，即每天按照评价进行采样，每一条好中差评价就是该事实表的最细粒度。

第二步，确定状态度量

对于好中差评价的度量关注更多的是评价本身，即没有类似于金额、数量这样的度量，因此设计为无事实事实表，更多关注评价的状态。

同时全量快照事实表会进行维度冗余。

注意事项

1. 事务与快照成对设计

   事务事实表和快照事实表往往都是成对设计，相互补充的。特别是可以在事务事实表的基础上加工快照事实表。减少下游分析的成本。

2. 附加事实

   快照事实表在确定状态度量时，一般都是保存采样周期结束时的状态度量。

3. 周期到日期度量

   业务使用方关心的统计周期度量包含历史至今、自然年至今，季度至今，财年至今的一些状态度量，因此在确定周期快照事实表的度量时，也要考虑类似的度量值，满足更多的统计分析需求。可以针对不同的统计周期设计不同的快照事实表。

累计快照事实表

有很多跨事件的时间关联性需求是事务事实表无法满足的，比如统计买家下单到支付的时长，卖家发货到买家收货的时间，如果使用事务事实表进行统计，逻辑复杂且性能很差。对于类似于研究事件之间时间间隔的需求，采用累积快照事实表可以解决。

设计过程

第一步，选择业务过程。

在拆分业务过程时，主要有四个事件：即创建订单、买家付款、卖家发货、买家确认收货，对于这四个业务过程，业务统计只关注创建订单、买家付款、买家确认收货三个业务过程，而在统计事件时间间隔中，卖家发货也是关键环节。所以对于累计快照事实表，我们选择四个业务过程。

第二步，确定粒度。

上文中已经明确粒度为子订单。

第三步，确定维度。

与事务事实表关联的所有维度表的并集。

第四步，确定事实。

累计快照事实表，需要将各业务过程对应的事实均放入事实表中。累计快照事实表解决的最重要的问题是统计不同业务过程之间的时间间隔，建议将每个过程的时间间隔作为事实放在事实表中。

第五步，冗余维度。

特点

1. 数据不断更新

   事务事实表记录事务发生时的状态，对于实体的某一实例不再更新；而累积快照事实表则对实体的某一实例定期更新。比如在11月11日下单，11月12日支付，11月13日确认收货，只会有一条记录，但是这条记录会不断更新。
   

2. 多业务过程日期

   累积快照事实表适用于具有较明确起止时间的短生命周期实体，比如订单、物流等，对于商品、用户等长生命周期的实体，还是要用周期快照事实表。

   累积快照事实表的典型特征是多业务过程日期，用于计算业务过程之间的时间间隔。同时用来保存全量数据，保存整个业务过程的全部交易数据。

特殊处理

1. 非线性过程

   一般的交易流程是有四个阶段：下单→支付→发货→确认收货。

   但是不是所有交易都会走此流程，比如：下单→关闭订单、下单→支付→关闭订单。

   而在特殊情况下，流程可能会回转。比如在退款过程中，正常流程是：买家申请退款→卖家同意退款→退款达成、买家 申请退款→卖家不同意退款→退款关闭。

   但是会因为买卖方没有达成协议，此时流程就变成了：买家申请退款→卖家不同意退款→买家申请退款→卖家不同意退款……

   对于非线性过程，处理方式如下：

   • 业务过程统一

     比如流程结束标志的统一，最开始设计交易累计快照事实表时，以交易完成作为结束标志；进一步了解业务后，发现交易关闭也是交易结束的一个分支。

   • 针对业务关键里程碑构建全面的流程

     比如交易，对于没有支付或没有发货的交易订单，全流程依然可以覆盖，相关业务过程的时间字段和事实置空。

   • 循环流程处理

     为了解决一个业务过程存在多个里程碑日期的问题。使用业务过程第一次发生日期还是最后一次发生日期，决定权在业务手中。

2. 多源过程

   针对多业务过程建模时，业务过程可能来自于不同的系统或者来源于不同的表，其对于累计快照事实表的模型设计没有影响，但是会影响ETL的开发复杂度。

   对于多源业务建模，主要考虑事实表的粒度问题。对于淘宝交易累计快照事实表，其粒度是交易子订单，而对于退款，每个子订单存在多次退款，那么将退款加入模型时，就需要考虑整体粒度不变。

3. 业务过程取舍

   将退款相关业务流程设计进入交易累积快照事实表时 ，是否需要所有的业务过程？答案是否定的。当拥有大量的业务过程时，模型的实现复杂度会增加，特别是对于多源业务过程，模型的精合度过高，此时需要根据商业用户需求，选取关键的里程碑。

物理实现

逻辑模型和物理模型密不可分，针对累计快照事实表模型设计，有不同的实现方法。

第一种是全量表的形式。此全量表一般为日期分区表，每天的分区存储昨天的全量数据以及当天的增量数据合并的结果，保证每条记录的状态最新。此方法适用于全量数据较少的情况，如果全量数据过大，会导致表数据量不断膨胀。

第二种方式是全量表的变化形式。此种方法主要针对事实表数据量很大的情况。较短生命周期的业务实体一般从产生到消亡都有一定时间间隔，所以可以推测时间间隔，或者根据需求确定一个较大的时间间隔。比如订单只存放200天内的数据，超过200天的数据会归档到归档表中。此方式存在的问题是200天的全量表较大，消耗很大。

第三种方式是以业务实体的结束时间分区。每天的分区存放当天结束的数据，设计一个时间非常大的分区，比如3000-12-31，存放截至当前未结束的数据。由于每天将当天结束的数据归档至当天分区中，时间非常大的分区数据量不会很大，ETL性能较好；且无存储的浪费，而且该表全量数据唯一。

但是第三种方式可能存在及其特殊的情况，即业务系统无法标识业务实体的结束时间。比如业务系统调用接口过多，依赖系统复杂，最终无法判断业务实体是否消亡。前台业务系统没有物流订单的结束时间，针对此问题，可以有两种处理方式：

• 使用相关业务系统的业务实体的结束标志作为业务系统的技术标志，比如交易订单完结了，物流订单就完结了。
• 和前端业务系统确定口径或归档策略。累计快照事实表针对业务实体一般有较短的生命周期。

三种事实表对比

	事务事实表	周期快照事实表	累计快照事实表
日期/时间	离散事务时间点	以有规律的、可预测的间隔产生快照	用于时间跨度不确定的不断变化的工作流
日期维度	事务日期	快照日期	相关业务过程涉及的多个日期
粒度	每行代表实体的一个事务	每行代表某个时间周期的一个实体	每行代表一各实体的生命周期
事实	事务事实	累积事实	相关业务过程事实和时间间隔事实
事实表加载	插入	插入	插入和更新
事实表更新	不更新	不更新	业务过程变更时更新

无事实事实表

不包含事实或度量的事实表称为“无事实的事实表”。虽然没有明确的事实，但可以用来支持业务过程的度量。

常见的无事实的事实表包含如下两种：

第一种是事件类，记录事件的发生。最常见的有日志类事实表。比如浏览日志。

第二种是条件、范围或资格类的，记录维度与维度多对多之间的关系，比如客户和销售人员的分配情况，产品的促销范围等。

聚集型事实表

聚集主要是通过汇总明细粒度数据来获得改进查询性能的效果。通过访问聚集数据，减少数据库在响应查询时的工作量，快速响应用户的查询，同时有利于减少不同用户访问明细数据粒度不同而造成的不一致。

阿里巴巴将使用频繁的公用数据，通过聚集进行沉淀，比如卖家最近1天交易汇总，最近N天交易汇总，自然年交易汇总等，这类聚集汇总数据，被称为”公共汇总层“。

基本原则

一致性。聚集表必须提供与查询明细粒度数据一致的查询结果。确保聚集星型模型中维度和度量与原始模型中维度和度量保持一致。

避免单一表设计，不要在同一个表中存储不同层次的聚集数据；否则会导致双重计算或更糟糕的情况。在聚集表中有些行存放天汇总的交易额，有些行存放月汇总的交易额，这回造成使用者双重计算。未来避免这种情况，会将天、月汇总的交易额分行存放，从列名或注释上可以分别出来。

聚集粒度可不同。聚集不需要保持和明细粒度一样的粒度，聚集只关心所需查询的维度。

设计原则

第一步，确定聚集维度。在原始明细表中会有多个描述事实的维度，这时需要根据什么维度聚集，就只需要关心什么维度，使用这个维度聚集数据。

第二步，确定一致性上钻。这时需要关心是按月汇总还是按天汇总，是按照商品汇总还是按照类目汇总。我们需要了解需求是什么，再按照他们想要的需求进行聚集。

第三天，确定聚集事实。在原始明细表中可能会有多个事实的度量，这时要明确按照哪个度量进行汇总。

公共汇总层

1. 基本原则

   除了聚集的基本原则外，建设公共汇总层还有以下原则：

   • 数据公用性。汇总的聚集会有第三者使用吗？基于某个维度的聚集数据是不是被第三方使用，如果是，就有必要沉淀在聚集表中。
   • 不跨数据域。数据域是在较高层次上对数据进行分类聚集的抽象，比如交易划拨在交易域中，商品放在商品域下。
   • 区分统计周期。在表的命名上要说明数据的统计周期，比如_td表示截至到当天，_nd表示最近N天。

2. 交易汇总层设计

   聚集是指针对原始明细粒度的数据进行汇总。假定已有的交易订单如下：
   

   那么他的潜在聚集如下：
   

   可以看出聚集的组合可能性为各个维度属性个数的乘积，下面按照聚表的基本步骤介绍聚集表的设计流程。

   （1）按照商品粒度汇总

   • 确定聚集维度-商品
   • 确定一致性上钻-按商品最近1天汇总
   • 确定聚集事实-下单量、交易额
     

   可以看到聚集的事实都是原始模型中的事实，聚集的维度也是原始模型维度中的商品维度，去掉了其他不关心维度。

   （2）按卖家粒度汇总

   • 确定聚集维度-卖家
   • 确定一致性上钻-按卖家最近7天和最近30天汇总
   • 确定聚集事实-交易额
     

   把不同层级的数据放在一起，比如最近7天和最近30天的事实，就需要在列名或注释上说明清楚。

   （3）按照卖家、买家、商品粒度汇总

   • 确定聚集维度-卖家、买家、商品
   • 确定一致性上钻-按卖家、买家、商品最近1天汇总
   • 确定聚集事实-交易额
     

   当聚集的粒度越细，就会越接近原始明细模型的粒度。

补充说明

1. 聚集是不跨越事实的

   聚集是针对原始星型模型进行汇总，未来获取和查询与原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨越事实的。如果要跨越事实，这被称为横向钻取，必须保证这几个事实是可以基于一致性维度进行分析的，且在原始明细层建设融合多事务事实表，这类表一般用于导出而不是分析。

2. 聚集带来的问题

   聚集会带来查询性能的提升，同时会增加ETL维护的难度。如果聚集对应的维度发生变化，这种情况会带来业务复杂性的提升，会导致原有聚集数据被废弃。