数仓：事实表设计方法，原则和三种类型选择

2022-11-03人工智能153

数仓：事实表设计方法，原则和三种类型选择
关注公众号，回复关键字【资料】，获取【10万字大数据框架面试知识点】与【大数据开发的命令手册】

; 事实表设计方法

事实表作为数据仓库维度建模的核心，紧紧围绕业务流程进行设计。它包含与业务流程和业务流程度量相关联的维度引用(外键)。

[En]

As the core of dimensional modeling of data warehouse, fact table is designed closely around the business process. It contains the dimension reference (foreign key) associated with the business process and the measurement of the business process.

一般设计会遵循以下四个步骤：

1. 选择业务过程及确定事实表类型

通常情况下，一个业务过程对应一张事实表。

2. 声明粒度

精确定义每张事实表的每行数据表示什么,按照业务尽可能选择最细粒度，以此来应各种细节程度的需求。

3. 确定维度

确定与每张事实表相关的维度有哪些? 维度的丰富程度就决定了模型能够支持的指标丰富程度。

4. 确定事实

每个业务流程的度量值(通常是可以累加的数值类型的值，如数字、金额等)。有关分项数字如下：

[En]

The measure of each business process (usually a value of a numeric type that can be accumulated, such as number, amount, and so on). The breakdown is as follows:

（1）可加事实

可以根据与事实表相关联的所有维度来累积附加事实，例如事务事实表中的事实。

[En]

Additive facts can be accumulated according to all the dimensions associated with the fact table, such as the facts in the transactional fact table.

（2）半可加事实

半相加事实意味着它们只能根据与事实数据表相关的一些维度进行累加，例如周期性快照事实数据表中的事实。比如库存，库存事实可以按照仓库或者商品维度来累计，而不是按照时间维度来累计，因为按日累计库存是不科学的。

[En]

Semi-additive facts mean that they can only be accumulated according to some of the dimensions related to the fact table, such as the facts in the periodic snapshot fact table. For example, inventory, inventory facts can be accumulated according to the warehouse or commodity dimension, but not according to the time dimension, because it is unscientific to accumulate daily inventory.

（3）不可加事实

非加性事实意味着它们根本不是可加性的，例如比率事实。非加性事实通常需要转换为加性事实，例如可以转换为分子和分母的比率。

[En]

Non-additive facts mean that they are not additive at all, such as ratio facts. Non-additive facts usually need to be transformed into additive facts, such as ratios that can be converted into numerators and denominators.

开发指标过程中，指标定义总结以下公式(仅供参考)

指标 = 维度 + 度量 + (条件)

其中度量：基于事实表中可以进行统计的数据属性，如：金额，次数等。

映射到SQL就是

select &#x5EA6;&#x91CF; from table where &#x6761;&#x4EF6; group by &#x7EF4;&#x5EA6;

事实表设计原则

尽可能包含所有与业务过程相关的事实
只选择与业务过程相关的事实
分解不可加性事实为可加的组件
在选择维度和事实之前必须先声明粒度
在同一个事实表中不能有多种不同粒度的事实
事实的单位要保持一致
对事实的 null 值要处理
使用退化维度提高事实表的易用性

什么是维度退化？

可以删除维度降级的维表，从而简化了维度数据仓库的模式。因为简单模式比复杂模式更容易理解，并且具有更好的查询性能。

[En]

Dimension tables with dimension degradation can be removed, thus simplifying the schema of the dimension data warehouse. Because simple schemas are easier to understand than complex ones and have better query performance.

当维度没有数据仓库所需的任何数据时，可以将其降级。您需要将维度降级的相关数据迁移到事实表中，然后删除降级的维度。

[En]

A dimension can be degraded when it does not have any data needed by the data warehouse. You need to migrate the relevant data of dimension degradation to the fact table, and then delete the degraded dimension.

维度属性也可以存储在事实表中，而存储在事实表中的这个维度列称为“维度降级”。与存储在维度表中的其他维度一样，维度降级也可用于筛选和查询事实数据表、实现聚合操作等。

[En]

Dimension attributes can also be stored in the fact table, and this dimension column stored in the fact table is called "dimension degradation". Like other dimensions stored in dimension tables, dimension degradation can also be used to filter and query fact tables, implement aggregation operations, and so on.

比如说订单id，这种量级很大的维度，没必要用一张维度表来进行存储，而我们进行数据查询或者数据过滤的时候又非常需要，所以这种就冗余在事实表里面，这种就叫退化维度，citycode这种我们也会冗余在事实表里面，但是它有对应的维度表，所以它不是退化维度。

三种类型

事务型事实表

是什么

事务事实表用于记录每个业务流程并跟踪特定时间点的度量事件。它存储每个业务流程的原子操作事件，即最细的粒度。

[En]

The transaction fact table is used to record each business process and track the measurement events at a certain point in time. It stores the atomic operation events of each business process, that is, the finest granularity.

场景

交易事实表可以用来分析与每个业务流程相关的统计指标。因为它保留最细粒度的记录，所以它可以提供最大的灵活性，并可以支持各种详细程度的意外统计要求。

[En]

Transactional fact table can be used to analyze the statistical indicators related to each business process. Because it keeps the finest-grained records, it can provide maximum flexibility and can support unexpected statistical requirements of various levels of detail.

不适合干什么

（1）存量型指标不适合

比如说商品库存，账户余额... 对于这种存在进进出出（加或减操作）不适合。
一张存储商品的进货的原子操作事件，一张存储商品出库的原子操作事件。如果要统计当日的库存又多少，这就需要对进货表和出库表进行操作，需要一正一反地区分对库存的影响。所以在写代码或者sql过程还是执行效率都会打折扣。

（2）多事务关联统计不适合

比如在订单中，建立了下单事务事实表和支付事务事实表，现在指标统计某个时间内用户下单到支付的时间间隔的平均值，这就需要这两个表的Join操作，然而，订单在大型公司属于达标，大表 Join 大表你可想而知。

周期型快照事实表

定期快照事实表会定期且可预测地记录事实，可以是每天、每周、每月、每年等等。它主要用于分析一些股票或状态指标。

[En]

Periodic snapshot fact tables record facts at regular and predictable intervals, which can be daily, weekly, monthly, annual, and so on. It is mainly used to analyze some stock or state indicators.

场景

存量型：例如对于商品库存、账户余额这些存量型指标，业务系统中通常就会计算并保存最新结果，所以定期同步一份全量数据到数据仓库，构建周期型快照事实表，就能轻松应对此类统计需求。

状态型：例如对于空气温度、行驶速度这些状态型指标，由于它们的值往往是连续的，我们无法捕获其变动的原子事务操作，所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样，构建周期型快照事实表。

累积型快照事实表

用于描述流程开始和结束之间的关键步骤事件，覆盖流程的整个生命周期，通常使用多个时间字段来记录关键时间点，当流程随生命周期变化时，记录也会随着流程的变化而修改。

[En]

Used to describe key step events between the beginning and end of a process, covering the entire life cycle of the process, usually with multiple time fields to record critical time points, when the process changes with the life cycle, the record is also modified as the process changes.

场景

如交易过程中的下单、付款、发货、确认收货等业务流程。会记录下订单的时间、付款的时间、交货的时间和收获的时间。如果你计算用户在一段时间内下订单和付款之间的平均时间间隔。做到这一点很容易。

[En]

Such as the business process of placing orders, paying, delivering goods and confirming receipt in the transaction process. Will record the time of placing the order, the time of payment, the time of delivery and the time of harvest. If you count the average time interval between a user placing an order and paying over a certain period of time. It's easy to do this.