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

原创 2017年05月26日 13:29:04
        退化维度技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。
        有时,维度表中除了业务主键外没有其它内容。例如,在本销售订单示例中,订单维度表除了订单号,没有任何其它属性,而订单号是事务表的主键,这种维度就是退化维度。业务系统中的主键通常是不允许修改的。销售订单只能新增,不能修改已经存在的订单号,也不会删除订单记录。因此订单维度表也不会有历史数据版本问题。退化维度常见于事务和累计快照事实表中。
        销售订单事实表中的每行记录都包括作为退化维度的订单号代理键。在操作型系统中,销售订单表是最细节事务表,订单号是订单表的主键,每条订单都可以通过订单号定位,订单中的其它属性,如客户、产品等,都依赖于订单号。也就是说,订单号把与订单属性有关的表联系起来。但是,在维度模型中,事实表中的订单号代理键通常与订单属性的其它表没有关联。可以将订单事实表所有关心的属性分类到不同的维度中,例如,订单日期关联到日期维度,客户关联到客户维度等。在事实表中保留订单号最主要的原因是用于连接数据仓库与操作型系统,它也可以起到事实表主键的作用。某些情况下,可能会有一个或两个属性仍然属于订单而不属于其它维度。当然,此时订单维度就不再是退化维度了。
        退化维度通常被保留作为操作型事务的标识符。实际上可以将订单号作为一个属性加入到事实表中。这样订单维度就没有数据仓库需要的任何数据,此时就可以退化订单维度。需要把退化维度的相关数据迁移到事实表中,然后删除退化的维度。
        注意,操作型事务中的控制号码,例如,订单号码、发票号码、提货单号码等通常产生空的维度并且表示为事务事实表中的退化维度。

一、退化订单维度

        使用维度退化技术时先要识别数据,分析从来不用的数据列。例如,订单维度的order_number列就可能是这样的一列。但如果用户想看事务的细节,还需要订单号。因此,在退化订单维度前,要把订单号迁移到sales_order_fact事实表。图1显示了修改后的模式。
图1

        按顺序执行下面的四步退化order_dim维度表:
  • 给sales_order_fact表添加order_number列
  • 把order_dim表里的订单号迁移到sales_order_fact表
  • 删除sales_order_fact表里的order_sk列
  • 删除order_dim表
        下面的脚本完成所有退化订单维度所需的步骤。
set search_path=tds;

alter table sales_order_fact rename to sales_order_fact_old; 
create table sales_order_fact as
select t2.order_number,
       t1.customer_sk,
       t1.product_sk,
       t1.order_date_sk,
       t1.year_month,
       t1.order_amount,
       t1.order_quantity,
       t1.request_delivery_date_sk  
  from sales_order_fact_old t1 inner join order_dim t2 on t1.order_sk = t2.order_sk;

comment on table sales_order_fact is '销售订单事实表';  
comment on column sales_order_fact.order_number is '订单号';  
comment on column sales_order_fact.customer_sk is '客户维度代理键';  
comment on column sales_order_fact.product_sk is '产品维度代理键';  
comment on column sales_order_fact.order_date_sk is '日期维度代理键';  
comment on column sales_order_fact.year_month is '年月分区键';  
comment on column sales_order_fact.order_amount is '销售金额';  
comment on column sales_order_fact.order_quantity is '数量';  
comment on column sales_order_fact.request_delivery_date_sk is '请求交付日期代理键';  

drop table sales_order_fact_old;  
drop table order_dim;
        HAWQ没有提供UPDATE功能,因此要更新已有数据的订单号,只能重新装载所有数据。在本例中,订单号维度表中代理键和订单号业务主键的值相同,其实可以简单地将事实表的order_sk字段改名为order_number。但这只是一种特殊情况,通常代理键和业务主键的值是不同的,因此这里依然使用标准的方式重新生成数据。

二、修改定期数据装载函数

        退化一个维度后需要做的另一件事就是修改定期数据装载函数。需要把订单号加入到销售订单事实表,而不再需要导入订单维度。修改后的函数如下。
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,  
           order_date,  
           entry_date,  
           order_amount,  
           order_quantity,  
           request_delivery_date   
      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,        
     customer_zip_code,        
     customer_city,        
     customer_state,      
     shipping_address,       
     shipping_zip_code,       
     shipping_city,       
     shipping_state,        
     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_customer_zip_code        
                else b_customer_zip_code        
            end customer_zip_code,        
           case flag         
                when 'D' then a_customer_city        
                else b_customer_city        
            end customer_city,        
           case flag         
                when 'D' then a_customer_state        
                else b_customer_state        
            end customer_state,        
           case flag         
                when 'D' then a_shipping_address        
                else b_shipping_address        
            end shipping_address,      
           case flag         
                when 'D' then a_shipping_zip_code        
                else b_shipping_zip_code        
            end shipping_zip_code,        
           case flag         
                when 'D' then a_shipping_city        
                else b_shipping_city        
            end shipping_city,        
           case flag         
                when 'D' then a_shipping_state        
                else b_shipping_state        
            end shipping_state,      
           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.customer_zip_code a_customer_zip_code,        
                   a.customer_city a_customer_city,        
                   a.customer_state a_customer_state,       
                   a.shipping_address a_shipping_address,        
                   a.shipping_zip_code a_shipping_zip_code,        
                   a.shipping_city a_shipping_city,        
                   a.shipping_state a_shipping_state,       
                   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.customer_zip_code b_customer_zip_code,        
                   b.customer_city b_customer_city,        
                   b.customer_state b_customer_state,       
                   b.shipping_address b_shipping_address,        
                   b.shipping_zip_code b_shipping_zip_code,        
                   b.shipping_city b_shipping_city,        
                   b.shipping_state b_shipping_state,       
                   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.customer_zip_code,0) = coalesce(b.customer_zip_code,0)        
                        and coalesce(a.customer_city,'') = coalesce(b.customer_city,'')         
                        and coalesce(a.customer_state,'') = coalesce(b.customer_state,'')      
                        and coalesce(a.shipping_address,'') = coalesce(b.shipping_address,'')         
                        and coalesce(a.shipping_zip_code,0) = coalesce(b.shipping_zip_code,0)        
                        and coalesce(a.shipping_city,'') = coalesce(b.shipping_city,'')         
                        and coalesce(a.shipping_state,'') = coalesce(b.shipping_state,'')      
                        ))) t        
             order by coalesce(a_customer_number, 999999999999), b_customer_number limit 999999999999;        
     
    -- 重载PA客户维度      
    truncate table pa_customer_dim;        
    insert into pa_customer_dim        
    select customer_sk,       
           customer_number,          
           customer_name,          
           customer_street_address,          
           customer_zip_code,          
           customer_city,          
           customer_state,        
           isdelete,       
           version,        
           effective_date,        
           shipping_address,     
           shipping_zip_code,    
           shipping_city,    
           shipping_state       
      from customer_dim        
     where customer_state = 'pa';       
    
    -- 装载产品维度        
    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 sales_order_fact          
    select a.order_number,          
           customer_sk,          
           product_sk,          
           e.date_sk,        
           e.year * 100 + e.month,             
           order_amount,      
           order_quantity,  
           f.date_sk             
      from rds.sales_order a,         
           v_customer_dim_his c,          
           v_product_dim_his d,          
           date_dim e,   
           date_dim f,           
           rds.cdc_time g          
     where a.customer_number = c.customer_number          
       and a.order_date >= c.effective_date        
       and a.order_date < c.expiry_date           
       and a.product_code = d.product_code          
       and a.order_date >= d.effective_date        
       and a.order_date < d.expiry_date           
       and date(a.order_date) = e.date    
       and date(a.request_delivery_date) = f.date    
       and a.entry_date >= g.last_load and a.entry_date < g.current_load;                      
        
    -- 分析tds模式的表        
    analyze customer_dim;        
    analyze product_dim;        
    analyze sales_order_fact;        
        
    -- 更新时间戳表的last_load字段          
    truncate table rds.cdc_time;        
    insert into rds.cdc_time select v_cur_date, v_cur_date;        
        
end;          
$$          
language plpgsql;
        函数做了以下两点修改:
  • 去掉装载和分析order_dim维度表的语句。
  • 事实表中的order_number字段字节从rds.sales_order表获得。

三、测试


1. 准备测试数据

        执行下面的SQL脚本在源库中增加两条销售订单记录。
use source;   

set @start_date := unix_timestamp('2017-05-25');    
set @end_date := unix_timestamp('2017-05-25 12:00:00');    
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date)); 
set @request_delivery_date := from_unixtime(unix_timestamp(date_add(current_date, interval 5 day)) + rand() * 86400);      
set @amount := floor(1000 + rand() * 9000);   
set @quantity := floor(10 + rand() * 90);      

insert into sales_order values (null,1,1,@order_date,@request_delivery_date,@order_date,@amount,@quantity);          
 
set @start_date := unix_timestamp('2017-05-25 12:00:01');    
set @end_date := unix_timestamp('2017-05-26');    
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date)); 
set @request_delivery_date := from_unixtime(unix_timestamp(date_add(current_date, interval 5 day)) + rand() * 86400);   
set @amount := floor(1000 + rand() * 9000);   
set @quantity := floor(10 + rand() * 90);      

insert into sales_order values (null,1,1,@order_date,@request_delivery_date,@order_date,@amount,@quantity);          
  
commit ;
        以上语句在源库上生成2017年5月25日的两条销售订单。为了保证自增订单号与订单时间顺序相同,注意一下@order_date变量的赋值。

2. 执行定期装载函数并查看结果

~/regular_etl.sh
        脚本执行成功后,查询sales_order_fact表,验证新增的两条订单是否正确装载。
select a.order_number,   
       customer_name,   
       product_name,   
       e.date,
       f.date,	   
       order_amount amount,
       order_quantity quantity	   
  from sales_order_fact a,
       customer_dim b,  
       product_dim c,   
       date_dim e,
       date_dim f	   
 where a.customer_sk = b.customer_sk 
   and a.product_sk = c.product_sk    
   and a.order_date_sk = e.date_sk 
   and a.request_delivery_date_sk = f.date_sk   
 order by order_number desc
 limit 5;
        查询结果如图2所示,可以看到新增两条记录的订单号被正确装载。
图2
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wzy0623/article/details/72765653

维度模型数据仓库(十三) —— 退化维度

(五)进阶技术         8. 退化维度         本篇讨论一种称为退化维度的技术。该技术减少维度的数量,简化维度数据仓库的模式。简单的模式比复杂的更容易理解,也有更好的查询性能。当一个维...
  • wzy0623
  • wzy0623
  • 2015-11-12 12:47:12
  • 2682

Mondrian系列(五)-深入维度、层次和级别(4)-维度退化和内联表

1.维度退化 维度退化是指对于简单的维度来说,不创建自己的维表,例如,下面的事实表: product_id time_id payment_me...
  • wonder4
  • wonder4
  • 2009-11-30 15:17:00
  • 2999

什么是退化维度?

什么是退化维度(Degenerate Dimension,DD),就是那些看起来像是事实表的一个维度关键字,但实际上并没有对应的维度表,其中,事实表的粒度就是文档本身或文档中的一个分列项。    具体...
  • Johnson_hejun
  • Johnson_hejun
  • 2008-11-18 15:51:00
  • 1131

什么是退化维(Degenerate Dimensions)

什么是退化维(Degenerate Dimensions) 退化维的定义是Ralph Kimball提出来的。一般来说事实表中的外键都对应一个维表,维的信息主要存放在维表中;但是退化维仅仅是事实表中的...
  • yangwl
  • yangwl
  • 2004-07-03 00:05:00
  • 2761

数据库的一些基本概念

一、基本入门概念 DBMS:数据库管理系统,就是数据库软件 数据库:保持有组织的数据的容器(通常是一个文件和一组文件),不管数据是什么或如何组织的,是通过DBMS创建和操纵的容器。 SQL:结构化查询...
  • nawuyao
  • nawuyao
  • 2015-12-21 11:11:33
  • 4666

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(五)——数据仓库维度建模实例

维度建模实例 库存管理业务建模案例 1. 选定某一业务过程,如:库存管理业务 2.根据各用户的需求(关注的主题),定义该业务处理的粒度。 主题一:矿厂中每种产品库存水平的日快照 主题...
  • houxiaoqin
  • houxiaoqin
  • 2015-08-12 01:11:44
  • 2046

数据仓库基础理论笔记

第一节 互联网电商大数据环境 如果你真正进入这个行业了! 入职后你所在部门一般叫:数据平台、数据中心、数据部 可能的团队:数据仓库组;BI(商业智能)组、某事业部数据组;架构组;数据专家组;....
  • china_demon
  • china_demon
  • 2016-07-12 19:32:03
  • 4295

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

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

BI中事实表和维度表的定义

一个典型的例子是,把逻辑业务比作一个立方体,产品维、时间维、地点维分别作为不同的坐标轴,而坐标轴的交点就是一个具体的事实。也就是说事实表是多个维度表的一个交点。而维度表是分析事实的一个窗口。  ...
  • zoubf
  • zoubf
  • 2016-01-10 21:45:32
  • 5748

数据仓库-事实表和维度表的设计

数据仓库的物理模型较常见的操作型数据库的物理模型有很大不同。最明显的区别是:操作型数据库主要是用来支撑即时操作,对数据库的性能和质量要求都比较高,为了防止“garbage in,garbage out...
  • y3177530
  • y3177530
  • 2016-04-11 14:48:04
  • 3971
收藏助手
不良信息举报
您举报文章:HAWQ取代传统数仓实践(九)——维度表技术之退化维度
举报原因:
原因补充:

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