数据仓库维度建模之事实表设计

一、DWD层明细事实表设计

事实表有粒度大小之分，基于数据仓库层次架构，明细事实表一般存在于dwd层，该层事实表设计不进行聚合、汇总动作，仅做数据规范化、数据降维动作，将多个实事表的内容汇总到一张表中,同时数据保持业务粒度，确保数据信息无丢失。

数据降维：
为了提高模型易用性，将常规维度表中的常用属性数据冗余到相应的事实表中，从而在使用的时候避免维表关联的方式，既为数据降维。

事实表的设计主要是根据业务流程，每一个业务动作事件都可以作为一个事实，一个业务流程中会有多个动作，其中的事实表设计主要有以下两种方案：

1、单事件事实表
对于每一个业务动作事件，设计一个事实表，仅记录该事件的事实以及状态，以用户下单打车为例，订单流程包括用户下单、司机接单、乘客支付等过程，这种场景下dwd层明细事实表的设计如下：

由上图可知，该方案更方便跟踪业务流程细节数据，针对特殊的业务分析场景比较方便灵活，数据处理上也更灵活，但是数仓中需要管理太多的事实表，同时跟踪业务流转不够直观。

2、流程事实表
对于一个业务流程主体，设计一个事实表，跟踪整个流程的事实以及状态流转。同样以用户下单打车为例，dwd层明细事实表的设计如下：

该方案能够更直观的跟踪业务流转和当前状态，可满足大多数的数据分析场景，由于和业务侧的数据模型设计思路一致，也是目前最常用的事实表设计，但是细节数据跟踪不到位，特殊场景的分析不够灵活。

3、两种方案的选择
在开发工作中这两种方案可以同时并存，一般单事件事实表应用略多，应用场景较为灵活，也可以结合两种方案进行设计，在流程表的基础上进行适当拆分

4、事实表的存储
(1)增量存储
每周期仅处理增量部分的数据，针对状态无变化的数据比较合适

(2)全量快照
状态有变化，但每天保存当前的快照数据，对于数据量在可控范围内的情况可以采用。如果存储空间和成本可接受，完整存储，确保能够追溯到历史每天数据状态，如果存储空间有限，考虑移动历史快照数据到冷盘，需要使用的时候可恢复，如果历史状态数据无太大价值，可以考虑部分删除，比如仅保留每月最后一天的快照数据

(3)拉链表
针对数据量大，但缓慢变化，需要跟踪历史状态，和缓慢渐变维类似，拉链表适用于每天变化的数据量较小的场景，否则如果每天的变化数据量大于新增量，可优先选择快照

5、制作拉链表

(1)基于增量数据

insert overwrite table order_info_l
select
	ta.order_id
	,ta.total_amount
	,ta.order_status
	,ta.start_date
	,(case when tb.order_id is not null and ta.end_date>'2020-02-16' then '2020-02-15' else ta.end_date end) as end_date
from order_info_l ta
left join
order_info tb
on ta.order_id=tb.order_id
union all
select 
	order_id
	,total_amount
	,order_status
	,'2020-02-16' as start_date
	,'9999-99-99' as end_date
from order_info;

(2)基于全量快照数据
通过md5算法，获取上日发生变化的数据

select
	ta.order_id
	,ta.total_amount
	,ta.order_status
	,ta.operate_date
	,ta.md5_val
from
(select
	order_id
	,total_amount
	,order_status
	,operate_date
	,md5(concat(order_id,operate_date)) as md5_val
from order_info where dt='2020-02-16')ta
left join
(select
	order_id
	,total_amount
	,order_status
	,operate_date
	,md5(concat(order_id,operate_date)) as md5_val
from order_info where dt='2020-02-15')tb
on ta.order_id=tb.order_id
where ta.md5_val!=tb.md5_val or tb.order_id is null;

二、DWS层聚合事实表

相对于明细事实表，聚合事实表通常是在明细事实表的基础上，按照一定的粒度粗细进行的汇总、聚合操作，粒度上比明细层数据较粗，同时伴随着细节信息的丢失，在数仓层次结构中，通常位于dws层，一般作为通用汇总数据存在，也可以是更高粒度的指标数据，但同一个事实表中，尽可能保证事实粒度一致。

1、事实种类
聚合事实表中的事实可分为以下两种：
(1)可累加事实
在一定的粒度范围内，可累加的事实度量，比如：订单金额、订单数。

(2)不可累加事实：比率，比如：通过率、转化率、去除的下单用户数。
通常情况下，比率这种不可累积的事实，建议拆分存储，比如通过率拆分为通过数、申请数，由细粒度数据去重计算而来的事实，正常存储，但是更粗粒度累积是不可直接使用。

2、事实表种类
数据仓库中，按照日期范围的不同，通常包含以下类别的聚集事实表：
(1)公共维度层-通用汇总
应对大部分可预期的、常规的数据需求，通常针对模式相对稳定的分析、BI指标计算、特征提取等场景，封装部分业务处理、计算逻辑、尽量避免用户直接使用底层明细数据，该层用到的数据范围比较广泛，可满足80-90%的应用场景。

(2)日粒度：
主要应对模式稳定的分析、BI日报、特征提取场景，同时日粒度也为后续周、月累积计算提供数据基础，数据范围一般为上一日的数据。

(3)周期性累积：
主要应对明确的周期性分析、BI周期性报表、数据范围一般在某周期内，数据尽量从日粒度或增量数据来取，避免从底层明细数据重复计算。

(4)历史累积：
历史以来某一特定数据的累积，通常在用户画像、经营分析、特征提取方面场景较多，设计数据范围比较广泛，通常是计算耗时较长的一部分，比如某部门累积营业额、某用户累积利润贡献、用户首次下单时间。

下图为出行订单明细事实表设计，在订单事实表中，对订单事实表、做单事实表、支付事实表进行了聚合汇合。