数仓之事实表设计

事实表基础

特性

• 事实表是数据仓库维度建模的核心，与业务紧密相连，通过获取描述业务过程的度量来表达业务过程，包含了引用的维度和与过程有关的度量。

• 相对维表来说，通常事实表比维表细长得多，数据行的增加速度也比维表快。

• 维度属性也可以存储到事实表中，作为“退化维度”。退化维度可以作为事实表的过滤条件，实现聚合操作。主要是为了方便操作和使用，比如物联网数据中轨道电路的状态字段，调整和分路，这两个状态通常用来聚合和过滤。

• 事实表有三种类型：事务事实表、周期快照事实表、累积快照事实表。

设计原则

• 尽可能包含所有与业务过程相关的事实，以便满足分析需求。【粒度要细】

• 只选择与业务过程相关的事实，不包含其它业务过程事实以面耦合过高，如订单下单业务过程的事实表，不应该包含支付金额这个属于支付过程的事实。如果25HZ轨道电路的事实不应该包含ZPW2000的事实信息，一天信息只反应一个回路信息，也就是说一个业务过程。

• 分解不可加性事实为可加的组件，如将订单的优惠率分解为订单原价与订单优惠金额。

• 选择维度与事实之前须明确粒度，粒度指的是表达业务的细节程度，每个维度和事实必须与所定义的粒度保持一致。粒度就是数据表中一行数据所代表的含义，一行数据反应了什么业务过程及细节程度，确认这个很关键。比如25HZ轨道电路，按照整个回路来说，有输入端电缆侧电流，变压器二次侧电流、送端长内引接线电流、长外引接线电流、送端短内引接线电流、短外引接线电流、受端电缆侧电流、变压器二次侧电流、受端长内引接线电流、长外引接线电流、受端短外、短内引接电流及轨道电压、轨道相位角等

• 同一个事实表中不能包含多种不同粒度的事实，如轨道电路事实表中不能同时既有25HZ的又有ZPW200的即使有，也请用标签区分，因为这两个是不同的对象，不同的粒度，其电路参数，属性均不一样，从现实世界角度讲粒度其实也是对对象的描述，两种不同对象的的描述就不要放在一个表中了。下图是一个bad case。

• 事实单位需要保持一致，对空值使用零值填充，适当使用退化维度提高事实表的易用性。

设计方法

• 选择业务过程以及确定事实表类型：通过分析业务过程，选择与需求有关的业务过程。如电商领域中选择“创建订单”或者同时选择“创建订单”与“买家付款”两个业务过程，其事实表类型会有所不同。

• 声明粒度：尽量选择最细级别的原子粒度以保事实表的灵活性，如父子订单的场景中，应该使用子订单粒度。

• 确定维度：确定粒度后也就确认了业务主键，可以进一步确认相关的维度组合以及字段，如粒度为子订单，相关维度有买家、卖家、商品等，如与设备相关的有设备履历信息表，设备阈值参数表，与巡检维修相关的有设备巡检维修表、与联锁相关的有进路联锁关系表、与预警相关的有预警信息表、时间维度表等。

• 确定事实：确定业务过程的度量，如子订单事实表中，有子订单分摊金额、邮费、优惠等。

• 冗余维度：逆规范化便于查询等操作，如子订单事实表中冗余商品类别字段。

事务事实表

• 任何类型的事件都可以理解为一种事务，如交易过程中的创建订单、买家付款等事件。事务事实表针对这些过程构建，跟踪定义业务过程的个体行为作为数仓的原子明细数据。

设计过程

• 选择业务过程：电商交易中包含的过程主要有创建下单、付款、发货、收货，这四个业务过程是下游分析统计的重点。

• 确定粒度：确定事实表每一行数据表达的细节层次，电商交易中常见的父子订单就是两种粒度。用户下单后，对于同一个商店的商品会生成一个父订单，父订单记录了物流信息、商品优惠等数据；每个商品生成一个子订单，如果只有一个商品，则合并为一个订单。下单、付款、收货三个过程粒度一般为子订单粒度，而发货由于一个子订单可以拆成多个物流单发货，故粒度为物流单。

• 确定维度：确定买家、卖家、商品、商品类目等相关维度。如设备维度表：设备产品型号，所属类型，设备所在回路信息等

• 确定事实：事实表应该包含与其描述过程有关的所有事实。

• 冗余维度

单事务事实表

• 单事务事实表指的是对每一个业务过程设计一个事实表，优点是可以方便地对每个业务过程进行独立的分析。

多事务事实表

• 将不同的事实放到同一个事实表中，一般有两种处理方法：不同业务过程使用不同的事实字段存放，表会比较宽；不同的业务过程使用同一个事实字段存放，但是增加一个业务过程标记字段以示区分。如标签：扳动过程、进路过程、过车过程、调整、分路等

• 多事务事实表中业务过程的粒度需要尽可能保持一致，维度也尽量保持一致。

二者对比

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

	单事务事实表	多事务事实表
业务过程	一个	多个
粒度	相互间不相关	相同粒度
维度	相互间不相关	一致
事实	只取当前业务过程的事实	保留多个业务过程中的事实，非当前业务过程中的事实需要置NULL处理
冗余维度	多个业务过程，则需要冗余多次	不同的业务过程只需要冗余一次
理解程度	易于理解，不会混淆	难以理解需要标签来限定
计算存储成本	较多，每个业务过程都需要计算存储一次	较少，不同的业务过程融合到一起，降低了存储计算量，但是非当前业务过程的度量存在大量NULL值

在PHM项目 我们采用多事务事实表来建立，如转辙机设备的业务过程分，过车、扳动、静态，轨道电路业务过程分调整和分路，通过加标签的形式来处理。

周期性快照事实表

• 事务事实表可以很好地跟踪业务过程并对其进行度量，但是其对于一些状态度量是低效率的，如卖家累计销售额，这需要对事务事实表进行聚合才可以得出对应的结果。

特性

• 快照事实表以预定的时间间隔对状态度量进行采样

• 快照事实表粒度一般由采样周期以及被采样的状态度量两个维度决定，如上表的粒度为每天对卖家截至当日的下单支付金额进行快照。

• 事务事实表是稀疏的，业务过程发生时才进行记录。快照事实表是稠密的，无论业务过程是否发生变化都会计算、记录数据。

• 快照事实表的度量都是半可加性的，即直接累加没有意义但是可以计算平均值等。

• 快照事实表根据采样维度的数量分为单维度以及混合维度。

• 快照事实表可以通过事务事实表进行汇总产出，也可以直接使用操作型系统的数据作为数据源进行加工。

注意事项

• 事务事实表与快照事实表往往是成对设计的。

• 快照事实表在需要获取上一次采样周期数据的场景下，可以把上一次采样的结果附加到当前记录。

• 计算快照事实表时，可以附加年份、季度等多样的字段便于分析。

累积快照事实表

特点

• 事务事实表对于统计某个业务过程的时长这种需求是低效的，如统计买家支付到卖家发货的时间。

• 建模过程与事务事实表的过程大致相同，适用于维度建模。

• 事务事实表记录的是事务发生时刻的状态，对于实体的某一实例不再更新，而累积快照事实表对实体的某一个实例定期更新。

物理实现

1. 使用日期分区表，每天分区存储昨天全量数据与当天增量数据合并后的结果，大量的数据会导致全量表的膨胀。

2. 对一段时间之前的数据进行归档存储，数据量较大，浪费存储空间。

3. 以业务实体结束时间进行分区，每天的分区存放当天结束的数据，使用一个独立的分区存放未结束的数据。这种方案比较节约资源，对ETL友好。

实际上就是拉链表的一种应用场景，用来跟踪某个事件发生的过程 。如列车进站，一次进路事件，会包括道岔、信号机、继电器的动作，此时需要跟踪是哪些具体子设备发生了动作，产生了哪些数据，而这一系列的动作变化用时间记录下来，反应到表里面就是累积快照表，如下表所示：

日期	列车id	进站开始时间	信号控制继电器动作时间	道岔表示继电器动作时间	道岔控制继电器动作时间	进站结束时间	事件类型
2021/7/1	1000	2021/7/1 10:02	2021/7/1 10:02	2021/7/1 10:05	2021/7/1 10:11	2021/7/1 10:22	进站

累积快照事实表的理解参考链接：

https://www.jianshu.com/p/453afb5382ea

三种事实表比较

无事实的事实表

• 无事实事实表不包含事实或者度量，但是可以用来支持业务过程的度量。常见的类型有两种

  • 事件类，记录事件的发生。如日志类事实表

  • 条件、范围和资格类，保存了一些元数据，如产品的促销范围

聚集型事实表

• 通过汇总明细数据改善查询性能，减少查询时的计算量，以空间换取时间。

聚集的原则

• 聚集表提供的数据需要与查询明细数据的结果保持一致

• 不能在同一个表存储不同层次的数据，否则会导致重复计算等问题

• 聚集表粒度与明细表粒度不一定需要保持一致

公共汇总层

• 通过聚集构建公共汇总层数据，提高数据查询效率与数据复用性。基本原则是保证数据的公用性，数据不跨数据域，表名称需要明确区分统计周期。