数据仓库系列3-事实表

数据仓库系列3-事实表

文章目录

• 一. 事实表介绍
• • 1.1 事实表结构
  • 1.2 事实表的度量
  • 1.3 事实表中的空值
  • 1.4 一致性事实
• 二. 事实表分类
• 2.1 事务事实表
• • 2.2 周期快照事实表
  • 2.3 累积快照事实表
  • 2.4 无事实的事实表
  • 2.5 聚集事实表或 OLAP 多维数据库
  • 2.6 合并事实表
• 三. 如何设计事实表
• • 3.1 确定数据域
  • 3.2 选择业务过程
  • 3.3 确定粒度
  • 3.4 确定维度
  • 3.5 确定事实
  • 3.6 冗余维度
• 四. 高级事实表技术
• • 4.1 事实表代理键
  • 4.2 蜈蚣事实表
  • 4.3 属性或事实的数字值
  • 4.4 日志／持续时间事实
  • 4.5 头／行事实表
  • 4.6 分配的事实
  • 4.7 利用分配建立利润与损失事实表
  • 4.8 多种货币事实
  • 4.9 多种度量事实单位
  • 4.10 年-日事实
  • 4.11 多遍 SQL 以避免事实表间的连接
  • 4.12 针对事实表的时间跟踪
  • 4.13 迟到的事实
• 参考:

一. 事实表介绍 1.1 事实表结构

  发生在现实世界中的 *** 作型事件，所产生的可度量数值 ，存储在事实表中。从最低 的粒度级别来看 ，事实表行对应一个度量事件 ，反之亦然 。因此，事实表 的设计完全依赖 于物理活动，不受可能产生的最终报表的影响 。除数字度量外，事实表 总是包含外键 ，用 于关联与之相关的维度 ，也包含可选的退化维度饱和日期／时间戳。查询请求的主要 目标是基于事实表开展计算和聚集 *** 作。

1.2 事实表的度量

  事实表的度量可以氛围可加 、半可加 、不可加事实三类。
  事实表中的数据度量可划分为三类。最灵活、最有用的事实是完全可加，可加性度量可以按照与事实表关联的任意维度汇总 。半可加度量可 以对某些维度汇总 ，但不能对所有 维度汇总 。差额是常见的半可加事实 ，除了时间维度外 ，它们可以跨所有维度进行加法 *** 作。另外，一些度量是完全不可加的 ，例如 ， 比率 。对非可加事实 ，一种好的方法是 ，尽 可能存储非可加度 量的完全可加的分量 ，并在计算出最终的非可加事实前 ，将这些分 量汇 总到最终的结果集合中 。最终计算通常发生在 BI 层或 OLAP 多维数据库层 。

以贷款业务为例，还款事实表

1. 还款金额为可加
2. 剩余金额(应收-实收)为半可加，统计截止本月底每个客户的剩余金额为总剩余金额，但是你把昨天的剩余金额+今天的剩余金额累加，这个就没有什么业务含义了。
3. 还款达成的比例这个为不可加。

1.3 事实表中的空值

  事实表中可以存在空值度量 。所有聚集函数（SUM 、COUNT 、MIN 、MAX 、AVG）均 可针对空值事实计算 。然而 ，在事实表的外键中不能存 在空值 ，否则会导致违反参照完 整 性的情况发生 。关联的维度表必须用默认行（代理键）而不是 空值外键表示未知的或无法应 用的条件。

以贷款业务为例，申请事实表
例如贷款产品的不同，有的产品要求客户填写月收入，用于风控决策分析，有的产品可不填。在设计事实表的时候，可以将此列设为空，不影响分析申请某产品的客户的平均月收入。

1.4 一致性事实

  如果某些度量出现在不同的事实表中 ，需要注意，如果需要 比较或计算不同事实表中 的事实，应保证针对事实的技术定义是相同的 。如果不同的事实表定义是 一致的 ，则这些 一致性事实应该具有相同的命名 ，如果它们不兼 容，则应该有不同的命名用于告诉业务用户和BI应用。

以贷款业务为例，账户周期快照表
有的事实表逾期金额的口径是逾期已到期金额，有的事实表统计的口径逾期客户剩余的所有金额，业务对比两个口径的数据的时候，就会发生异常。
在数据仓库实时的过程中，同一个度量值由于统计口径不同，到时数仓开发人员与业务人员反复沟通确认数据，带来极大的不便。所以在数据仓库建模的过程中，对于度量值要做到统一，如出现类似的度量值，可以新增一个度量值，给事实表增加一列。

二. 事实表分类

维度建模数仓领域中的事实表大致分以下四种：

1. 事务事实表
2. 周期快照事实表
3. 累计快照事实表
4. 无事实的事实表
5. 聚集事实表
6. 合并事实表

稀疏表：当天只有发生了 *** 作才会有记录
稠密表：当天没有 *** 作也会有记录，便于下游使用

周期快照事实表的日期维度通常是记录时间段的终止日，记录的事实是这个时间段内一些聚集事实值。事实表的数据一旦插入即不能更改，其更新方式为增量更新。

以贷款业务为例，假设我有一个申请事实表，某一个日期没有客户申请，那么申请事实表没有该天的数据，而周期快照事实表，累计事实表有截止当天的快照数据。所以事务事实表可以是稠密的，也可以是稀疏的，周期快照事实表，累计快照事实表是稠密的，快照事实表的数据量是大于等于事务事实表。

2.1 事务事实表

  事务事实表的一行对应空间或时间上某点的度 量事件 。原子事务粒度事实表是维度化 及可表达的事实表 ，这类健壮的维度确保对事务数据的最大化分片和分块 。事务事实表可 以是稠密的，也可以是稀疏的 ，因为仅当存在度量时才会建 立行。这些事实表总是包含 一 个与维度表关联的外键 ，也可能包含精确的时间戳和退化维度键 。度量数字事实必须与 事 务粒度保持一致 。

以贷款业务为例
申请事实表、还款事实表等都是事务事实表，而且事务事实表是事实表的基础，快照表(周期快照和累计快照)都是基于事务实时表加工而来。

2.2 周期快照事实表

  周期快照事实表中的每行汇总了发生在某 一标准周期 ，如某一天 、某周 、某月的多个 度量事件 。粒度是周期性的 ，而不是个体的事务 。周期快照事实表通常包含许多事实 ，因 为任何与事实表粒度 一致的度量事件都是被允许存在的 。这些事实表其外键的密度是均匀 的，因为即使周期内没有活动发 生 ，也会在事实表中为每个 事实插入包含 0 或空值的行 。

以贷款业务为例
因为业务系统的数据是实时变化的，客户还款数据的变化，会影响在库和逾期的数据。而数据分析经常会统计截止某个时间点的静态结算数据，例如账户的贷款余额，所以会经常使用到周期快照事实表。

2.3 累积快照事实表

  累积快照事实表的行汇总了发生在过程开始和结束之间可预测步骤内的度 量事件 。管 道或工作流过程（例如 ，履行订单或索赔过程）具有定义的开始 点，标准中间过程 ，定义的 结束点 ，它们在此类事实表中都可以被建模 。通常在事实表中针对过程中的关键步骤都包 含日期外键 。累积快照事实表中的 一行，对应某一具体的订单 ，当订单产生时会插入 一行。 当管道过程发生时 ，累积事实表行被访问并修改 。这种对累积快照事实表行的 一致性修改 在三种类型事实表中具有特性 ，除了日期外键与每个关键过程步骤关联外 ，累积快照事实 表包含其他维度和可选退化维度的外键 。通常包含数字化的与粒度保持 一致的 ，符合里程碑完成计数的滞后 性度量。

周期快照事实表记录的是重复的可预测到的时间间隔的事实，例如帐户月结余事实表，用来记录每个月末的帐户结余信息。一般周期快照的数据会按报表需要的周期进行记录，比较适合周期长一些的情况。

而累计快照适用于较短周期，有着明确的开始和结束状态的过程，如一个订单执行的过程，并记录过程中每个步骤的执行时间，使分析人员对执行的过程有整体的把握。周期快照事实表记录上每个步骤的执行时间是逐步建立的，随着执行的过程逐步更新的事实表中。

以贷款业务为例
周期快照适用于贷后的账户，保存周期性的应还、实还、在库余额等信息。
累积快照适用于申请事实表，因为截止某个周期，会存在很多在途(还未审批完成的订单)的订单，截止某个周期的数据要走到最终完结(例如审批通过或拒绝)才便于进行分析。

2.4 无事实的事实表

  尽管多数度量事件获取的结果是数字化的 ，但也存在某些事件仅仅记录 一系列某一时 刻发生的多维实体。例如 ，在给定的某 一天中发生的学生参加课程的事件，可能没有可记 录的数字化事实 ，但该事实行带有 一个包含日历天 、学生、教师、地点 、课程等定义良好 的外键 。同样，客户交际也是 一种事件，但没有相关的度量 。利用无事实的事实表也可以 分析发生了什么 。这类查询总是包含两 个部分 ：包含所有可能事件的无事实覆盖表 ，包含实际发生的事件的活动表 。当活动从覆盖表中减除时，其结果是尚未发生的事件。

以贷款业务为例
客户注册了APP，但是未提交贷款申请，此时注册这个业务过程 有客户的维度、时间维度、地域维度等，均可以用来分析客户注册这个业务场景，但是此时没有可以度量的值，可以理解为一个无事实的事实表。

2.5 聚集事实表或 OLAP 多维数据库

  聚集事实表是对原子粒度事实表数据进行简单的数字化上卷 *** 作，目的是为了提高查 询性能。这些聚集事实表 以及原子事实表可以同时被 BI 层使用 ，这样 BI 工具在查询时可 以平滑地选择适当的聚集层次 。这一被称为聚集导航的过程是开放的 ，以便报表制作者、 查询工具 、BI 应用都能够获得同样的性 能优势。适当设计的聚集集合应该类似数据库索引 ， 能够提高查询性能 ，但不需要直接面对 BI 应用或商业用户 。聚集事实表包含外键以缩小 一 致性维度 ，聚集事实的构建是通过对来自多 个原子事实表的度量的汇总而获得的。最后 ， 使用汇总而度量 聚集 OLAP 多维数据库一般与关系类型的聚集方法类似 ，但是 OLAP 多维 数据库可 以被商业用户直接访问 。

以贷款业务为例
申请事实表是以申请单为粒度，现在数据分析部门需要分析每个产品的批核效率，此时我们可以根据申请单的产品进行汇总，将度量数据进行sum汇总，这样会方便我们进行查询。

2.6 合并事实表

  通常将来自多个过程的，以相同粒度表示的事实合并为一个单一的合并事实表 ，这样做能够带来方便 。例如 ，现货销售可以与销售预测合并为 一张事实表 ，与针对多个不同的 非实表采用下钻应用 比较，这样做可使对现货及预测任务的分 析工作变得简单快捷 。合并事实表会增加 ETL 处理过程的负担 ，但降低了 BI 应用的分析代价 。合并事实表特 别适合 那些经常需要共同分析的多过程度量 。

三. 如何设计事实表 3.1 确定数据域

可以将数据域理解为一个数据主题，比如门店、订单、客户等等，每一个主题下包含属于该主题的业务过程，比如订单主题下包含订单的创建、无效等等业务过程。

3.2 选择业务过程

业务过程通常用行为动词表示
业务过程通常由某个 *** 作系统支撑，如账单系统或者购买系统。
一系列业务过程产生一系列事实表
在设计事实表的时候，首先得知道这张事实表要记录什么事实，也就是说，对应的业务过程是什么，是关于下单这个业务过程，还是支付或者购买之类的业务过程。

3.3 确定粒度

业务过程选定以后，就要针对每个业务过程确定一个粒度，即确定事务事实表每一行所表达的细节层次。（以“组织结构”为例，比如一个层级结构式：总公司，分公司，部门，科室。这就是不同的粒度。）

确定粒度也可以理解为你想设计的事实表中，每一行应该代表什么。

如果选择的业务过程是下单，并且我想观察每个订单的详细情况，那么粒度可以选择为订单粒度，在下单业务过程中，最细的粒度应该就是订单粒度。

对于事务表，一般都是从最细粒度构建。同时，上卷汇总粒度对性能调整来说非常重要。不同的事实表粒度，需要建立不同的事实表，比如订单粒度和店铺粒度的数据，不要在一张事实表中，在同一事实表中不要混用多种不同的粒度。

3.4 确定维度

维度一般用来解决如何描述事实表中每一行数据的问题。

维度信息一般是时间，地区，客户等等，具体还要看对应的使用场景，当度量和维度进行关联的时候，一条事实表的纪录只能和对应的维度进行关联，应该是1：1的关系。

3.5 确定事实

事实，也叫做度量或者指标，作为事实表的核心，事实表应该包含与其描述过程有关的所有事实。

不同的业务过程拥有不同的事实，一个业务过程中，可能也有不同的度量，比如在下单业务过程中， 需要包含下单金额、下单数量、下单分摊金额等等;

属于不同粒度的事实应该放在不同的事实表中。

3.6 冗余维度

在确定维度时，我们确定了一些维度，但是Kimball维度建模理论建议在事实表中只保存这些维表的外键，一般我们会把一些维度退化、冗余到事实表中，方便在事实表中进行统计，也为下游使用这张事实表的时候不用再去关联别的维度表，是一种以存储空间换效率的做法。

四. 高级事实表技术

本节讨论 的这些技术涉及不太 常见的事实表模式 。

4.1 事实表代理键

  代理键可用作所有维度表的主键 。此外 ，可使用单列代理事实键 ，尽管不太需要 。事实表代理键可用于:
①作为事实表的唯一主键列；
②在ETL中，用作事实表行的直接标识符 ，不必查询多个维度 ：
③允许将事实表更新 *** 作分解为风险更小的插入和删除 *** 作。

4.2 蜈蚣事实表

  一些设计者为多对一层次的每层建立不同的规范化维度 ，例如 ，日期维度、月份维度 、 季度维度和年维度等 ，并将所有外键包含在 一个事实表中 。这将产生娱蛤事实表 ，包含与 维度相关的多个维度 。应该避免使用娱蛤事实表 。所有这些固定深度的 、多对一层次化关 联的维度都应该回到它们最细节的粒度上 ，例如 ，上例中提到的日期 。当设计者将多个外 键嵌入到单一低粒度维度表中 ，而不是建立杂项维度时 ，也会产生娱蛤事实表 。

4.3 属性或事实的数字值

  设计者有时会遇到一些数字值 ，难 以确定将这些数字值分类到维度表或是事实表的情况。典型的实例是产品的标准价格。如果该数字值主要用于计算目的，则可能属于事实表 。 如果该数字值主要用于确定分组或过滤 ，则应将其定义为维度属性 ，离散数字值用值范围属性进行补充（例如 ，$0 50） 。某些情况下 ，将数字值既建模为维度又建模为属性是非常有益的，例如，定量准时交货度量 以及定性文本描述符 。

4.4 日志／持续时间事实

  累积快照事实表获取多个过程里程碑 ，每个都包含日期外键并可能包含日期／时间戳。 商业用户通常希望分析这些里程碑之间的滞后及延迟时间 。有时这些延迟仅仅是日期上的差异，但某些情况下，延迟可能基于更复杂的业务规则 。如果流水线包含大量的步骤，则可能存在上百个延迟 。与其要求用户查询通过日期／时间戳或者日期维度外键计算每个可能存在的延迟，不如根据过程的开始时间点为每个度量步骤存储一个时间延迟 。这样做可以方便地通过利用存储在事实表中的两个延迟，简单地用减法计算任 何两个步骤间可能存在 的延迟。

4.5 头／行事实表

   *** 作型交易系统通常包括事务头指针行 ，头指针行与 多个事务行关联 。采用头／行模式（也称为父／子模式），所有头指针级别维度外键与退化 维度应该被包含在行级别事实表 。

4.6 分配的事实

  头指针／行事务数据与对应的 事实具有不同粒度这样的情况经常发生 ，例如，头表示货运费用 。应该尽量分配头指针事实 ，使其基于业务所提供的规则划分为行级别 ，分配的事 实可以按照所有维度进行分片井上钻 *** 作 。多数情况下，可避免建立头指针级别的 事实表 ， 除非这样的聚集能够获得查询性能的改善 。

4.7 利用分配建立利润与损失事实表

  事实表揭示利润等价方程是企业 DW/BI 应用能够发布的最强大的结果 。利润方程是 ：收入- 开销 = 利润。理想地实现利润方程的事实表应为原子收入事务粒度并包含许多开销项 。

  因为这些表处于原子粒度 ，才能实现数字化的上卷 ，包括客户利润 ，产品利润，促销利润 ， 渠道利润等 。然而 ，建立这些事实表存在一定难度 ，因为开销项必须从其原始来源划分到 事实表粒度 。这一分配步骤通常由 ETL 子系统完成，这一过程是一个与业务相关的步骤 ， 需要高层经理 的支持。出于以上原因 ，利润与损失事实表通常在 DW/BI 程序的早期实现阶 段不会被处理。

4.8 多种货币事实

  以多种货币单位记录财务事务的事实表行应该包含一对列 。其中一列包含以真实币种表示的事实 ，另外列包含同样的 ，但以整个事实表统一的单一标准币种表示的事实 。标准币种值在 ETL 过程中按照规定的货币转换 规则建立。该事实表也必须有一个货币维度用 于区分事务的真正货币 。

4.9 多种度量事实单位

  某些业务过程需要事实 同时以多种度量单位表示 。例如 ，按照业务用户的观点 ，供应 链可能需要对相同事实 以平台 、船运 、零售 以及单个扫描单元构建报表 。如果事实表包含 大量事实 ，而每个事实都必须以所有度 量单位表示 ，此时较好的方法是将 事实以公认的标 准度量单位存储 ，同时存储标准度量与其他度量的转换系数 。这种事实表可按照不同用户 的观点部署 ，使用适当选择的转换系数 。转换系数必须存储在事实表行中以确保计算简单 正确，并尽量降低查询复杂性 。

4.10 年-日事实

  商业用户在事实表 中通常需要年-日（year-to-date, YTD）值 。很难反对单个请求 ，但是 YTD 请求很容易变 换为 “ 财务周期结束时的 YTD ” 或者 “ 财务周期日”。一种更可靠 、可 扩展的处理这些请求 的方法是在 BI 应用或 OLAP 多维数据库中计算 YTD 矩阵 ，而不是在 事实表 巾查出 YTD 事实。

4.11 多遍 SQL 以避免事实表间的连接

  BI 应用绝不应该跨事实表 的外键处理两个事实表 的连接 *** 作 。在关系数据库中 ，控制 此类连接 *** 作的回答集的基数是不可能 的，将会产生 不正确的结果 。例如 ，如果两个 事实 表包含客户产品 出货和返回，则这两个表不 能按照客户和产品外键 直接连接 。要采用跨钻 方式使用两个事实表 ，井对结果按 照公共行头指针属性值 ，进行排序 融合 *** 作以产生正确 结果。

4.12 针对事实表的时间跟踪

  存在三种基本事实表粒度 ：事务 级别、周期快照和累积快照。个别情况下 ，在事实表中增加行有效时期 、行截止日期和当前行标识是非常有用的 ，与采用类型 2 缓慢变化维度， 在事实行有效时获取时间的方式类似。尽管不太常用 ，但该模型能够解决诸如缓慢变化库存平衡的场景，其中频繁周期快照可以在每个快照上 加载同一行。

4.13 迟到的事实

  迟到事实是指如果用于新事实行的多数当前维度内容无法匹配输入行的情况。这通常发生在当事实行延迟产生时 。在此情况下，当迟到度量事件出现时 ，必须搜索相关维度以发现有效的维度键 。

参考:

1. 《数据仓库工具箱 维度建模权威指南》第三版