HAWQ取代传统数仓实践(十五)——事实表技术之无事实的事实表

原创 2017年06月06日 11:02:35

一、无事实事实表简介

        在多维数据仓库建模中,有一种事实表叫做“无事实的事实表”。普通事实表中,通常会保存若干维度外键和多个数字型度量,度量是事实表的关键所在。然而在无事实的事实表中没有这些度量值,只有多个维度外键。表面上看,无事实事实表是没有意义的,因为作为事实表,毕竟最重要的就是度量。但在数据仓库中,这类事实表有其特殊用途。无事实的事实表通常用来跟踪某种事件或者说明某些活动的范围。
        无事实的事实表可以用来跟踪事件的发生。例如,在给定的某一天中发生的学生参加课程的事件,可能没有可记录的数字化事实,但该事实行带有一个包含日期、学生、教师、地点、课程等定义良好的外键。利用无事实的事实表可以按各种维度计数上课这个事件。
        无事实的事实表还可以用来说明某些活动的范围,常被用于回答“什么未发生”这样的问题。例如:促销范围事实表。通常销售事实表可以回答如促销商品的销售情况,可是无法回答的一个重要问题是:处于促销状态但尚未销售的产品包括哪些?销售事实表所记录的仅仅是实际卖出的产品。事实表行中不包括由于没有销售行为而销售数量为零的行,因为如果将包含零值的产品都加到事实表中,那么事实表将变得非常巨大。这时,通过建立促销范围事实表,将商场需要促销的商品单独建立事实表保存,然后通过这个促销范围事实表和销售事实表即可得出哪些促销商品没有销售出去。
        为确定当前促销的产品中哪些尚未卖出,需要两步过程:首先,查询促销无事实的事实表,确定给定时间内促销的产品。然后从销售事实表中确定哪些产品已经卖出去了。答案就是上述两个列表的差集。这样的促销范围事实表只是用来说明促销活动的范围,其中没有任何事实度量。建立一个单独的促销商品维度表能否可以达到同样的效果呢?促销无事实的事实表包含多个维度的主键,可以是日期、产品、商店、促销等,将这些键作为促销商品的属性是不合适的,因为每个维度都有自己的属性集合。
促销无事实事实表看起来与销售事实表相似。然而,它们的粒度存在显著差别。假设促销是以一周为持续期,在促销范围事实表中,将为每周每个商店中促销的产品加载一行,无论产品是否卖出。该事实表能够确保看到被促销定义的键之间的关系,而与其它事件,如产品销售无关。
        下面以销售订单数据仓库为例,说明如何处理源数据中没有度量的需求。建立一个无事实的事实表,用来统计每天发布的新产品数量。产品源数据不包含产品数量信息,如果系统需要得到历史某一天新增产品的数量,很显然不能简单地从数据仓库中得到。这时就要用到无事实的事实表技术。使用此技术可以通过持续跟踪产品发布事件来计算产品的数量。可以创建一个只有产品(计什么数)和日期(什么时候计数)维度代理键的事实表。之所以叫做无事实的事实表是因为表本身并没有数字型度量值。这里定义的新增产品是指在某一给定日期,源产品表中新插入的产品记录,不包括由于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

        无事实事实表是没有任何度量的事实表,它本质上是一组维度的交集。用这种事实表记录相关维度之间存在多对多关系,但是关系上没有数字或者文本的事实。无事实事实表为数据仓库设计提供了更多的灵活性。
版权声明:本文为博主原创文章,未经博主允许不得转载。

维度模型数据仓库(十七) —— 无事实的事实表

(五)进阶技术         12. 无事实的事实表         本篇讨论一种技术,用来处理源数据中没有度量的需求。例如,产品源数据不包含产品数量信息,如果系统需要得到产品的数量,很显然不能简单...
  • wzy0623
  • wzy0623
  • 2015年11月17日 14:26
  • 2069

HAWQ取代传统数仓实践(十三)——事实表技术之周期快照

一、周期快照简介        周期快照事实表中的每行汇总了发生在某一标准周期,如一天、一周或一月的多个度量。其粒度是周期性的时间段,而不是单个事务。周期快照事实表通常包含许多数据的总计,因为任何与事...
  • wzy0623
  • wzy0623
  • 2017年06月02日 15:32
  • 2572

HAWQ取代传统数仓实践(十六)——事实表技术之迟到的事实

一、迟到的事实简介        数据仓库通常建立于一种理想的假设情况下,这就是数据仓库的度量(事实记录)与度量的环境(维度记录)同时出现在数据仓库中。当同时拥有事实记录和正确的当前维度行时,就能够从...
  • wzy0623
  • wzy0623
  • 2017年06月08日 17:00
  • 2626

HAWQ取代传统数仓实践(十四)——事实表技术之累积快照

一、累积快照简介        累积快照事实表用于定义业务过程开始、结束以及期间的可区分的里程碑事件。通常在此类事实表中针对过程中的关键步骤都包含日期外键,并包含每个步骤的度量,这些度量的产生一般都会...
  • wzy0623
  • wzy0623
  • 2017年06月05日 14:52
  • 2531

HAWQ取代传统数仓实践(十七)——事实表技术之累积度量

累积度量指的是聚合从序列内第一个元素到当前元素的数据,例如统计从每年的一月到当前月份的累积销售额。本篇说明如何在销售订单示例中实现累积月销售数量和金额,并对数据仓库模式、初始装载、定期装载做相应地修改...
  • wzy0623
  • wzy0623
  • 2017年06月09日 18:38
  • 2030

HAWQ取代传统数仓实践(十一)——维度表技术之维度合并

有一种合并维度的情况,就是本来属性相同的维度,因为某种原因被设计成重复的维度属性。例如,在销售订单示例中,随着数据仓库中维度的增加,我们会发现有些通用的数据存在于多个维度中。客户维度的客户地址相关信息...
  • wzy0623
  • wzy0623
  • 2017年05月31日 15:03
  • 2680

HAWQ取代传统数仓实践(十)——维度表技术之杂项维度

一、什么是杂项维度        简单地说,杂项维度就是一种包含的数据具有很少可能值的维度。事务型商业过程通常产生一系列混杂的、低基数的标志位或状态信息。与其为每个标志或属性定义不同的维度,不如建立单...
  • wzy0623
  • wzy0623
  • 2017年05月27日 13:59
  • 2485

HAWQ取代传统数仓实践(九)——维度表技术之退化维度

退化维度技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。        有时,维度表中除了业务主键外没有其它内容。例如,在本销售订单示例中,订单维度表除了订...
  • wzy0623
  • wzy0623
  • 2017年05月26日 13:29
  • 2152

HAWQ取代传统数仓实践(十二)——维度表技术之分段维度

一、分段维度简介        在客户维度中,最具有分析价值的属性就是各种分类,这些属性的变化范围比较大。对某个个体客户来说,可能的分类属性包括:性别、年龄、民族、职业、收入和状态,例如,新客户、活跃...
  • wzy0623
  • wzy0623
  • 2017年06月01日 11:42
  • 3389

BIEE建模技术之-时间事实表

很多时候,我们希望将指标在时间上求平均,比如月平均。算法是:汇总值/天数。 注意:天数不确定,需要根据你所选择的时间来 对于这种需求,最关键的地方就是怎么得到这个天数,而这个天数应该怎么得到呢? ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HAWQ取代传统数仓实践(十五)——事实表技术之无事实的事实表
举报原因:
原因补充:

(最多只允许输入30个字)