HAWQ取代传统数仓实践（十五）——事实表技术之无事实的事实表
HAWQ取代传统数仓实践（十五）——事实表技术之无事实的事实表
一、无事实事实表简介
在多维数据仓库建模中，有一种事实表叫做“无事实的事实表”。普通事实表中，通常会保存若干维度外键和多个数字型度量，度量是事实表的关键所在。然而在无事实的事实表中没有这些度量值，只有多个维度外键。表面上看，无事实事实表是没有意义的，因为作为事实表，毕竟最重要的就是度量。但在数据仓库中，这类事实表有其特殊用途。无事实的事实表通常用来跟踪某种事件或者说明某些活动的范围。
无事实的事实表可以用来跟踪事件的发生。例如，在给定的某一天中发生的学生参加课程的事件，可能没有可记录的数字化事实，但该事实行带有一个包含日期、学生、教师、地点、课程等定义良好的外键。利用无事实的事实表可以按各种维度计数上课这个事件。
无事实的事实表还可以用来说明某些活动的范围，常被用于回答“什么未发生”这样的问题。例如：促销范围事实表。通常销售事实表可以回答如促销商品的销售情况，可是无法回答的一个重要问题是：处于促销状态但尚未销售的产品包括哪些？销售事实表所记录的仅仅是实际卖出的产品。事实表行中不包括由于没有销售行为而销售数量为零的行，因为如果将包含零值的产品都加到事实表中，那么事实表将变得非常巨大。这时，通过建立促销范围事实表，将商场需要促销的商品单独建立事实表保存，然后通过这个促销范围事实表和销售事实表即可得出哪些促销商品没有销售出去。
为确定当前促销的产品中哪些尚未卖出，需要两步过程：首先，查询促销无事实的事实表，确定给定时间内促销的产品。然后从销售事实表中确定哪些产品已经卖出去了。答案就是上述两个列表的差集。这样的促销范围事实表只是用来说明促销活动的范围，其中没有任何事实度量。建立一个单独的促销商品维度表能否可以达到同样的效果呢？促销无事实的事实表包含多个维度的主键，可以是日期、产品、商店、促销等，将这些键作为促销商品的属性是不合适的，因为每个维度都有自己的属性集合。
促销无事实事实表看起来与销售事实表相似。然而，它们的粒度存在显著差别。假设促销是以一周为持续期，在促销范围事实表中，将为每周每个商店中促销的产品加载一行，无论产品是否卖出。该事实表能够确保看到被促销定义的键之间的关系，而与其它事件，如产品销售无关。
下面以销售订单数据仓库为例，说明如何处理源数据中没有度量的需求。建立一个无事实的事实表，用来统计每天发布的新产品数量。产品源数据不包含产品数量信息，如果系统需要得到历史某一天新增产品的数量，很显然不能简单地从数据仓库中得到。这时就要用到无事实的事实表技术。使用此技术可以通过持续跟踪产品发布事件来计算产品的数量。可以创建一个只有产品（计什么数）和日期（什么时候计数）维度代理键的事实表。之所以叫做无事实的事实表是因为表本身并没有数字型度量值。这里定义的新增产品是指在某一给定日期，源产品表中新插入的产品记录，不包括由于SCD2新增的产品版本记录。注意，单从这个简单需求来看，也可以通过查询产品维度表获取结果。这里只为演示无事实事实表的实现过程。
二、建立新产品发布的无事实事实表
在tds模式中新建一个产品发布的无事实事实表product_count_fact，该表中只包含两个字段，分别是引用日期维度表和产品维度表的外键，同时这两个字段也构成了无事实事实表的逻辑主键。图1显示了跟踪产品发布数量的表。
图1
执行下面的脚本在数据仓库模式中创建产品发布日期视图及其无事实事实表。
set search_path=tds;
create view product_launch_date_dim
(product_launch_date_sk,
product_launch_date,
month_name,
month,
quarter,
year)
as
select distinct
date_sk,
date,
month_name,
month,
quarter,
year
from product_dim a, date_dim b
where a.effective_date = b.date
and a.version = 1;
create table product_count_fact (
product_sk int,
product_launch_date_sk int);说明：
- 与之前创建的很多日期角色扮演维度不同，产品发布日期视图只获取产品生效日期，而不是日期维度里的所有记录。因此在定义视图的查询语句中关联了产品维度和日期维度两个表。product_launch_date_dim维度是日期维度表的子集。
- 从字段定义上看，产品维度表中的生效日期明显就是新产品的发布日期。
- version = 1 过滤掉由于SCD2新增的产品版本记录。
三、初始装载无事实事实表
下面的脚本从产品维度表向无事实事实表装载已有的产品发布信息。脚本里的insert语句添加所有产品的第一个版本，即产品的首次发布日期。
insert into product_count_fact
select a.product_sk product_sk, b.date_sk date_sk
from product_dim a,date_dim b
where a.effective_date = b.date and a.version = 1; 使用下面的语句查询product_count_fact表以确认正确执行了初始装载，查询结果如图2所示。
select product_sk,product_launch_date_sk
from tds.product_count_fact
order by product_sk;
图2
四、修改定期数据装载函数
修改了数据仓库模式后，还需要针对性的修改定期装载函数，在处理产品维度表后增加了装载product_count_fact表的语句。下面显示了修改后的定期装载函数。
create or replace function fn_regular_load ()
returns void as
$$
declare
-- 设置scd的生效时间
v_cur_date date := current_date;
v_pre_date date := current_date - 1;
v_last_load date;
begin
-- 分析外部表
analyze ext.customer;
analyze ext.product;
analyze ext.sales_order;
-- 将外部表数据装载到原始数据表
truncate table rds.customer;
truncate table rds.product;
insert into rds.customer select * from ext.customer;
insert into rds.product select * from ext.product;
insert into rds.sales_order
select order_number,
customer_number,
product_code,
status_date,
entry_date,
order_amount,
quantity,
request_delivery_date,
verification_ind,
credit_check_flag,
new_customer_ind,
web_order_flag,
order_status
from ext.sales_order;
-- 分析rds模式的表
analyze rds.customer;
analyze rds.product;
analyze rds.sales_order;
-- 设置cdc的上限时间
select last_load into v_last_load from rds.cdc_time;
truncate table rds.cdc_time;
insert into rds.cdc_time select v_last_load, v_cur_date;
-- 装载客户维度
insert into tds.customer_dim
(customer_number,
customer_name,
customer_street_address,
shipping_address,
isdelete,
version,
effective_date)
select case flag
when 'D' then a_customer_number
else b_customer_number
end customer_number,
case flag
when 'D' then a_customer_name
else b_customer_name
end customer_name,
case flag
when 'D' then a_customer_street_address
else b_customer_street_address
end customer_street_address,
case flag
when 'D' then a_shipping_address
else b_shipping_address
end shipping_address,
case flag
when 'D' then true
else false
end isdelete,
case flag
when 'D' then a_version
when 'I' then 1
else a_version + 1
end v,
v_pre_date
from (select a.customer_number a_customer_number,
a.customer_name a_customer_name,
a.customer_street_address a_customer_street_address,
a.shipping_address a_shipping_address,
a.version a_version,
b.customer_number b_customer_number,
b.customer_name b_customer_name,
b.customer_street_address b_customer_street_address,
b.shipping_address b_shipping_address,
case when a.customer_number is null then 'I'
when b.customer_number is null then 'D'
else 'U'
end flag
from v_customer_dim_latest a
full join rds.customer b on a.customer_number = b.customer_number
where a.customer_number is null -- 新增
or b.customer_number is null -- 删除
or (a.customer_number = b.customer_number
and not
(coalesce(a.customer_name,'') = coalesce(b.customer_name,'')
and coalesce(a.customer_street_address,'') = coalesce(b.customer_street_address,'')
and coalesce(a.shipping_address,'') = coalesce(b.shipping_address,'')
))) t
order by coalesce(a_customer_number, 999999999999), b_customer_number limit 999999999999;
-- 装载产品维度
insert into tds.product_dim
(product_code,
product_name,
product_category,
isdelete,
version,
effective_date)
select case flag
when 'D' then a_product_code
else b_product_code
end product_code,
case flag
when 'D' then a_product_name
else b_product_name
end product_name,
case flag
when 'D' then a_product_category
else b_product_category
end product_category,
case flag
when 'D' then true
else false
end isdelete,
case flag
when 'D' then a_version
when 'I' then 1
else a_version + 1
end v,
v_pre_date
from (select a.product_code a_product_code,
a.product_name a_product_name,
a.product_category a_product_category,
a.version a_version,
b.product_code b_product_code,
b.product_name b_product_name,
b.product_category b_product_category,
case when a.product_code is null then 'I'
when b.product_code is null then 'D'
else 'U'
end flag
from v_product_dim_latest a
full join rds.product b on a.product_code = b.product_code
where a.product_code is null -- 新增
or b.product_code is null -- 删除
or (a.product_code = b.product_code
and not
(a.product_name = b.product_name
and a.product_category = b.product_category))) t
order by coalesce(a_product_code, 999999999999), b_product_code limit 999999999999;
-- 装载新增产品数量无事实事实表
insert into tds.product_count_fact
select a.product_sk, b.date_sk
from tds.product_dim a, tds.date_dim b
where a.version = 1
and a.effective_date = v_pre_date
and a.effective_date = b.date;
-- 装载销售订单事实表
insert into sales_order_fact
select a.order_number,
customer_sk,
product_sk,
e.date_sk,
e.year * 100 + e.month,
order_amount,
quantity,
f.date_sk,
g.sales_order_attribute_sk,
h.customer_zip_code_sk,
i.shipping_zip_code_sk,
a.order_status
from rds.sales_order a,
v_customer_dim_his c,
v_product_dim_his d,
date_dim e,
date_dim f,
sales_order_attribute_dim g,
v_customer_zip_code_dim h,
v_shipping_zip_code_dim i,
rds.customer j,
rds.cdc_time k
where a.customer_number = c.customer_number
and a.status_date >= c.effective_date
and a.status_date < c.expiry_date
and a.product_code = d.product_code
and a.status_date >= d.effective_date
and a.status_date < d.expiry_date
and date(a.status_date) = e.date
and date(a.request_delivery_date) = f.date
and a.verification_ind = g.verification_ind
and a.credit_check_flag = g.credit_check_flag
and a.new_customer_ind = g.new_customer_ind
and a.web_order_flag = g.web_order_flag
and a.customer_number = j.customer_number
and j.customer_zip_code = h.customer_zip_code
and j.shipping_zip_code = i.shipping_zip_code
and a.entry_date >= k.last_load and a.entry_date < k.current_load;
-- 重载PA客户维度
truncate table pa_customer_dim;
insert into pa_customer_dim
select distinct a.*
from customer_dim a,
sales_order_fact b,
v_customer_zip_code_dim c
where c.customer_state = 'pa'
and b.customer_zip_code_sk = c.customer_zip_code_sk
and a.customer_sk = b.customer_sk;
-- 分析tds模式的表
analyze customer_dim;
analyze product_dim;
analyze sales_order_fact;
analyze pa_customer_dim;
-- 更新时间戳表的last_load字段
truncate table rds.cdc_time;
insert into rds.cdc_time select v_cur_date, v_cur_date;
end;
$$
language plpgsql;五、测试
修改源数据库的product表数据，把产品编码为1的产品名称改为‘Regular Hard Disk Drive’，并新增一个产品‘High End Hard Disk Drive’（产品编码为5）。执行下面的脚本完成此修改。
use source;
update product set product_name = 'Regular Hard Disk Drive' where product_code=1;
insert into product values (5, 'High End Hard Disk Drive', 'Storage');
commit;修改后的产品数据如图3所示。
图3
执行定期装载。
~/regular_etl.sh通过查询product_count_fact表确认定期装载执行正确。
select c.product_sk psk,
c.product_code pc,
b.product_launch_date_sk plsk,
b.product_launch_date pld
from product_count_fact a,
product_launch_date_dim b,
product_dim c
where a.product_launch_date_sk = b.product_launch_date_sk
and a.product_sk = c.product_sk
order by pc, pld;查询结果如图4所示。可以看到只是增加了一条新产品记录，原有数据没有变化。
图4
无事实事实表是没有任何度量的事实表，它本质上是一组维度的交集。用这种事实表记录相关维度之间存在多对多关系，但是关系上没有数字或者文本的事实。无事实事实表为数据仓库设计提供了更多的灵活性。
腾讯云开发者
