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

        无事实事实表是没有任何度量的事实表,它本质上是一组维度的交集。用这种事实表记录相关维度之间存在多对多关系,但是关系上没有数字或者文本的事实。无事实事实表为数据仓库设计提供了更多的灵活性。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏腾讯云技术沙龙

邹伟:如何开发一款小游戏

大家下午好,今天我分享的主题是如何开发一款火爆的小游戏。其实小程序和小游戏还是有一些共通的地方,比如在登录部分小程序和小游戏是类似的,而Wafer2也是支持小游...

1.9K26
来自专栏DevOps时代的专栏

DevOps 三剑客:Dev,Ops and Jenkins

每一位开发、测试和运维工程师,都有一个私人管家,他的名字叫Jenkins. Jenkins World就像这位管家的生日Party,大家聚在一起狂欢,分享Jen...

2148
来自专栏Golang语言社区

技术干货分享:如何选择 HTML5 游戏引擎

原生手游市场已是红海,腾讯、网易等寡头独霸天下,H5游戏市场或将成为下一个风口。据笔者所知,很多H5游戏开发团队由于选择引擎不慎导致项目甚至团队夭折。如何选择适...

2889
来自专栏服务端技术杂谈

我的微服务之路

故事开端 故事开始于一年半前,当时还在维护着公司的一套老项目,项目虽老,但是每天的pv,up都是过千万的。理论上算得上是一个大项目,对于技术能力有一定的挑战。 ...

2295
来自专栏软件开发 -- 分享 互助 成长

浅谈保证软件工程质量的一些心得体会

Itwolf原创博客,转载请标明出处,谢谢

2439
来自专栏Jerry的SAP技术分享

SAP成都研究院郑晓霞:Shift Left Testing和软件质量保证的一些思考

今天的文章来自Jerry的同事,曾经的搭档郑晓霞(Zheng Kate)。郑晓霞是在Jerry心中是一位很有实力的程序媛,2011年从西安某软件公司跳槽到SAP...

1182
来自专栏DevOps时代的专栏

龙门阵之 DevOps 门外汉须知

作者:龙井 本文根据《龙门阵之DevOps门外汉》直播内容精简整理而成。 直播 PPT 分享链接: https://pan.baidu.com/s/1i5ss6...

3858
来自专栏软件开发 -- 分享 互助 成长

浅谈保证软件工程质量的一些心得体会

前言: 质量这个词究竟有多重要,没有切身体会真的很难说的出来,从毕业到进入华为工作马上就要满1.5年了,现在这个词理解更加深刻了些。这么说吧,质量在华为的研发领...

1688
来自专栏软件测试经验与教训

如何设定性能测试的目标?

3636
来自专栏企鹅号快讯

完全自学web前端开发找到高薪工作的过来人,告诉你怎么自学

现在是个知识爆炸的时代,网上学习资源很多,作为一个完全自学web前端开发做程序员的过来人,经常被问到:我是0基础,怎么自学?才能学成一个优秀的web前端开发找到...

2066

扫码关注云+社区