我们以最简单的“增加列”开始，继而讨论维度子集、角色扮演维度、层次维度、退化维度、杂项维度、维度合并、分段维度等基本的维度表技术。这些技术都是在实际应用中经常使用的。在说明这些技术的相关概念和使用场景后，我们以销售订单数据仓库为例，给出实现代码和测试过程，必要时会对前面已经完成的配置和脚本做出适当修改。

7.1 增加列

业务的扩展或变化是不可避免的，尤其像互联网行业，需求变更已经成为常态，唯一不变的就是变化本身，其中最常碰到的扩展是给一个已经存在的表曾加列。以销售订单为例，假设因为业务需要，在操作型源系统的客户表中增加了送货地址的四个字段，并在销售订单表中增加了销售数量字段。由于数据源表增加了字段，数据仓库中的表也要随之修改。本节说明如何在客户维度表和销售订单事实表上添加列，并在新列上应用SCD2，以及对消费配置和rule定义所做的修改。图7-1显示了增加列后的数据仓库模式。

图7-1 增加列后的数据仓库模式

1. 停止Canal Server、Canal Adapter

在实时应用场景中，执行任何DDL语句前，最好先停止消息队列两端的生产者和消费者，以避免可能的数据同步错误。此时不能再像初始装载时那样停止MySQL复制，因为主库的修改需要实时同步到从库。


# 停止Canal Server，构成Canal HA的126、127两台都执行
~/canal_113/deployer/bin/stop.sh
# 停止Canal Adapter，126执行
~/canal_113/adapter/bin/stop.sh

2. 修改表结构
我们需要在已经存在的表上增加列。

（1）修改源数据库表结构
使用下面的SQL语句修改MySQL中的源数据库模式。


-- 126主库执行
use source;  
alter table customer  
  add shipping_address varchar(50) after customer_state  
, add shipping_zip_code int after shipping_address  
, add shipping_city varchar(30) after shipping_zip_code  
, add shipping_state varchar(2) after shipping_city ;  
alter table sales_order add order_quantity int after order_amount ;

以上语句给客户表增加了四列，表示客户的送货地址。销售订单表在销售金额列后面增加了销售数量列。after关键字是MySQL对标准SQL的扩展，Greenplum不支持这种扩展，只能把新增列加到已有列的后面。在关系理论中，列是没有顺序的。

（2）修改目标数据库表结构


-- 修改RDS数据库模式里的表
set search_path to rds;
alter table customer 
add column shipping_address varchar(30),
add column shipping_zip_code int, 
add column shipping_city varchar(30), 
add column shipping_state varchar(2);  
alter table sales_order add order_quantity int;  
 
-- 修改TDS数据库模式里的表
set search_path to tds, rds;
alter table customer_dim
add column shipping_address varchar(30),
add column shipping_zip_code int, 
add column shipping_city varchar(30), 
add column shipping_state varchar(2);  
alter table sales_order_fact add order_quantity int;

3. 修改Canal Adapter表映射
在customer.yml和sales_order.yml文件中添加新增字段的映射。


[mysql@node2~/canal_113/adapter/conf/rdb]$cat ~/canal_113/adapter/conf/rdb/customer.yml 
dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: Greenplum
concurrent: true
dbMapping:
  database: source
  table: customer
  targetTable: rds.customer
  targetPk:
    customer_number: customer_number
#  mapAll: true
  targetColumns:
    customer_number: customer_number
    customer_name: customer_name
    customer_street_address: customer_street_address
    customer_zip_code: customer_zip_code
    customer_city: customer_city
    customer_state: customer_state
    shipping_address: shipping_address
    shipping_zip_code: shipping_zip_code
    shipping_city: shipping_city
    shipping_state: shipping_state
  commitBatch: 30000 # 批量提交的大小
 
[mysql@node2~/canal_113/adapter/conf/rdb]$cat ~/canal_113/adapter/conf/rdb/sales_order.yml 
dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: Greenplum
concurrent: true
dbMapping:
  database: source
  table: sales_order
  targetTable: rds.sales_order
  targetPk:
    order_number: order_number
#  mapAll: true
  targetColumns:
    order_number: order_number
    customer_number: customer_number
    product_code: product_code
    order_date: order_date
    entry_date: entry_date
    order_amount: order_amount
    order_quantity: order_quantity
  commitBatch: 30000 # 批量提交的大小

4. 创建自定义操作符<=>
rule的定义中需要使用了一个新的关系操作符“<=>”，因为原来的少判断了一种情况。在源系统库中，客户地址和送货地址列都是允许为空的，这样的设计是出于灵活性和容错性的考虑。我们以送货地址为例进行讨论。

使用“shipping_address <> new.shipping_address”条件判断送货地址是否更改，根据不等号两边的值是否为空，会出现以下三种情况：

shipping_address和new.shipping_address都不为空。这种情况下如果两者相等则返回false，说明地址没有变化，否则返回true，说明地址改变了，逻辑正确。
shipping_address和new.shipping_address都为空。两者的比较会演变成null<>null，根据Greenplum对“<>”操作符的定义，会返回NULL。因为查询语句中只会返回判断条件为true的记录，所以不会返回数据行，这符合我们的逻辑，说明地址没有改变。
shipping_address和 new.shipping_address只有一个为空。就是说地址列从NULL变成非NULL，或者从非NULL变成NULL，这种情况明显应该新增一个版本，但根据“<>”的定义，此时返回值是NULL，查询不会返回行，不符合我们的需求。

现在使用“not (shipping_address <=> new.shipping_address)”作为判断条件，我们先看一下“<=>”操作符的定义：A <=> B — Returns same result with EQUAL(=) operator for non-null operands, but returns TRUE if both are NULL, FALSE if one of the them is NULL。从这个定义可知，当A和B都为NULL时返回TRUE，其中一个为NULL时返回FALSE，其他情况与等号返回相同的结果。下面再来看这三种情况：

shipping_address和new.shipping_address都不为空。这种情况下如果两者相等则返回not (true)，即false，说明地址没有变化，否则返回not (false)，即true，说明地址改变了，符合逻辑。
shipping_address和new.shipping_address都为空。两者的比较会演变成not (null<=>null)，根据“<=>”的定义，会返回not (true)，即返回false。因为查询语句中只会返回判断条件为true的记录，所以查询不会返回行，这符合我们的逻辑，说明地址没有改变。
shipping_address和new.shipping_address只有一个为空。根据“<=>”的定义，此时会返回not (false)，即true，查询会返回行，符合需求。

空值的逻辑判断有其特殊性，为了避免不必要的麻烦，数据库设计时应该尽量将字段设计成非空，必要时用默认值代替NULL，并将此作为一个基本的设计原则。Greenplum没有提供<=>比较操作符，但可以自定义：


create or replace function fn_exactly_equal(left_o anyelement, right_o anyelement) returns boolean as
$$
  select left_o is null and right_o is null or (left_o is not null and right_o is not null and left_o = right_o)
$$
language sql;
 
create operator <=> (procedure = fn_exactly_equal, leftarg = anyelement, rightarg = anyelement, commutator = <=>);

Greenplum中的anyelement、anyarray、anynonarray和anyenum四种伪类型被称为多态类型。使用这些类型声明的函数叫做多态函数。多态函数的同一参数在每次调用函数时可以有不同数据类型，实际使用的数据类型由调用函数时传入的参数所确定。当一个查询调用多态函数时，特定的数据类型在运行时解析。每个声明为anyelement的位置（参数或返回值）允许是任何实际的数据类型，但是在任何一次给定的函数调用中，anyelement必须具有相同的实际数据类型。同样，每个声明为anyarray的位置允许是任何实际的数组数据类型，但是在任何一次给定的函数调用中，anyarray也必须具有相同类型。如果某些位置声明为anyarray，而另外一些位置声明为anyelement，那么实际的数组元素类型必须与anyelement的实际数据类型相同。

anynonarray在操作上与anyelement完全相同，它只是在anyelement的基础上增加了一个额外约束，即实际类型不能是数组。anyenum在操作上也与anyelement完全相同，它只是在anyelement的基础上增加了一个额外约束，即实际类型必须是枚举（enum）类型。anynonarray和anyenum并不是独立的多态类型，它们只是在anyelement上增加了约束而已。例如，f(anyelement, anyenum)与f(anyenum, anyenum)是等价的，实际参数都必须是同样的枚举类型。

如果一个函数的返回值被声明为多态类型，那么它的参数中至少应该有一个是多态的，并且参数与返回结果的实际数据类型必须匹配。例如，函数声明为assubscript(anyarray, integer) returns anyelement。此函数的的第一个参数为数组类型，而且返回值必须是实际数组元素的数据类型。再比如一个函数的声明为asf(anyarray) returns anyenum，那么参数只能是枚举类型的数组。

5. 重建相关rule
（1）重建customer_dim维度表rule
因为新增列采用SCD2处理，需要在rule中增加对shipping_address的判断，其中使用<=>比较操作符。Greenplum没有提供alter rule命令，只能先删除再新建。


drop rule r_insert_customer on customer;
create rule r_insert_customer as on insert to customer do also 
(insert into customer_dim (customer_number,customer_name,customer_street_address,customer_zip_code,customer_city,customer_state,version,effective_dt,expiry_dt,
shipping_address,shipping_zip_code,shipping_city,shipping_state
) 
 values (new.customer_number,new.customer_name,new.customer_street_address,new.customer_zip_code,new.customer_city,new.customer_state,1,now(),'2200-01-01',
new.shipping_address,new.shipping_zip_code,new.shipping_city,new.shipping_state
 ););
 
drop rule r_update_customer on customer;
create rule r_update_customer as on update to customer do also 
(update customer_dim set expiry_dt=now() 
  where customer_number=new.customer_number and expiry_dt='2200-01-01' 
    and ( not (customer_street_address <=> new.customer_street_address) or not (shipping_address <=> new.shipping_address));
 
insert into customer_dim (customer_number,customer_name,customer_street_address,customer_zip_code,customer_city,customer_state,version,effective_dt,expiry_dt,
shipping_address,shipping_zip_code,shipping_city,shipping_state) 
select new.customer_number,new.customer_name,new.customer_street_address,new.customer_zip_code,new.customer_city,new.customer_state,version + 1,expiry_dt,'2200-01-01',
new.shipping_address,new.shipping_zip_code,new.shipping_city,new.shipping_state
  from customer_dim 
 where customer_number=new.customer_number 
   and (not (customer_street_address <=> new.customer_street_address) or not (shipping_address <=> new.shipping_address))
   and version=(select max(version) 
                  from customer_dim 
                 where customer_number=new.customer_number);
 
update customer_dim set customer_name=new.customer_name 
 where customer_number=new.customer_number and customer_name<>new.customer_name);

（2）重建sales_order_fact事实表rule
对于装载销售订单事实表的修改很简单，只要将新增的销售数量列order_quantity添加到查询语句中即可。


drop rule r_insert_sales_order on sales_order;
create rule r_insert_sales_order as on insert to sales_order do also 
(insert into order_dim (order_number,version,effective_dt,expiry_dt) 
 values (new.order_number,1,'2021-06-01','2200-01-01');
 
insert into sales_order_fact(order_sk,customer_sk,product_sk,order_date_sk,year_month,order_amount,order_quantity) 
 select e.order_sk, customer_sk, product_sk, date_sk, to_char(order_date, 'YYYYMM')::int, order_amount, order_quantity
   from rds.sales_order a, customer_dim b, product_dim c, date_dim d, order_dim e
  where a.order_number = new.order_number and e.order_number = new.order_number
    and a.customer_number = b.customer_number and b.expiry_dt = '2200-01-01'
    and a.product_code = c.product_code and c.expiry_dt = '2200-01-01'
    and date(a.order_date) = d.date);

6. 启动Canal Server、Canal Adapter


# 启动Canal Server，在构成Canal HA的126、127两台顺序执行
~/canal_113/deployer/bin/startup.sh
 
# 在126执行
~/canal_113/adapter/bin/startup.sh

7. 测试
执行下面的SQL脚本，在MySQL的源数据库中增加客户和销售订单测试数据。


-- 126 MySQL主库执行
use source;    
update customer set shipping_address = customer_street_address, 
shipping_zip_code = customer_zip_code,
shipping_city = customer_city, 
shipping_state = customer_state ;    
 
insert into customer     
(customer_name,customer_street_address,customer_zip_code,customer_city,customer_state,shipping_address,shipping_zip_code, shipping_city,shipping_state)    
values 
('online distributors','2323 louise dr.', 17055,'pittsburgh','pa','2323 louise dr.',17055,'pittsburgh', 'pa') ;
  
-- 新增订单日期为2021年12月30日的9条订单。
set sql_log_bin = 0;
  
set @start_date := unix_timestamp('2021-12-30');    
set @end_date := unix_timestamp('2021-12-31');    
drop table if exists temp_sales_order_data;    
create table temp_sales_order_data as select * from sales_order where 1=0;     
 
set @customer_number := floor(1 + rand() * 9);
set @product_code := floor(1 + rand() * 4);  
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date));    
set @amount := floor(1000 + rand() * 9000);  
set @quantity := floor(10 + rand() * 90);  
insert into temp_sales_order_data 
values (1, @customer_number, @product_code, @order_date, @order_date, @amount, @quantity);    
 
... 新增9条订单 ...   
 
set sql_log_bin = 1;
insert into sales_order    
select null,customer_number,product_code,order_date,entry_date,order_amount,order_quantity 
  from temp_sales_order_data 
 order by order_date;      
commit ;

上面的语句生成了两个表的测试数据。客户表更新了已有八个客户的送货地址，并新增编号为9的客户。销售订单表新增了九条记录。查询customer_dim表，应该看到已存在客户的新记录有了送货地址。老的过期记录的送货地址为空。9号客户是新加的，具有送货地址。查询sales_order_fact表，应该只有9个订单有销售数量，老的销售数据数量字段为空。

7.2 维度子集

有些需求不需要最细节的数据。例如更想要某个月的销售汇总，而不是事务数据。再比如相对于全部的销售数据，可能对某些特定状态的数据更感兴趣等。此时事实数据需要关联到特定的维度，这些特定维度包含在从细节维度选择的行中，所以叫维度子集。维度子集比细节维度的数据少，因此更易使用，查询也更快。

有时称细节维度为基本维度，维度子集为子维度，基本维度表与子维度表具有相同的属性或内容，我们称这样的维度表具有一致性。一致的维度具有一致的维度关键字、一致的属性列名字、一致的属性定义以及一致的属性值。如果属性的含义不同或者包含不同的值，维度表就不是一致的。

子维度是一种一致性维度，由基本维度的列与行的子集构成。当构建聚合事实表，或者需要获取粒度级别较高的数据时，需要用到子维度。例如，有一个进销存业务系统，零售过程获取原子产品级别的数据，而预测过程需要建立品牌级别的数据。无法跨两个业务过程模式，共享单一产品维度表，因为它们需要的粒度是不同的。如果品牌表属性是产品表属性的严格的子集，则产品和品牌维度仍然是一致的。在这个例子中需要建立品牌维度表，它是产品维度表的子集。对基本维度和子维度表来说，属性（例如，品牌和分类描述）是公共的，其标识和定义相同，两个表中的值相同，然而，基本维度和子维度表的主键是不同的。注意：如果子维度的属性是基本维度属性的真子集，则子维度与基本维度保持一致。

还有另外一种情况，就是当两个维度具有同样粒度级别的细节数据，但其中一个仅表示行的部分子集时，也需要一致性维度子集。例如，某公司产品维度包含跨多个不同业务的所有产品组合，如服装类、电器类等等。对不同业务的分析可能需要浏览企业级维度的子集，需要分析的维度仅包含部分产品行。与该子维度连接的事实表必须被限制在同样的产品子集。如果用户试图使用子集维度，访问包含所有产品的集合，则因为违反了参照完整性，他们可能会得到预料之外的查询结果。需要认识到这种造成用户混淆或错误的维度行子集的情况。

ETL数据流应当根据基本维度建立一致性子维度，而不是独立于基本维度，以确保一致性。本节中将准备两个特定子维度，月份维度与Pennsylvania州客户维度。它们均取自现有的维度，月份维度是日期维度的子集，Pennsylvania州客户维度是客户维度的子集。

1. 建立包含属性子集的子维度
当事实表获取比基本维度更高粒度级别的度量时，需要上卷到子维度。在销售订单示例中，当除了需要实时事务销售数据外，还需要月销售汇总数据时，会出现这样的需求。我们可以通过在Greenplum创建物化视图简单实现。


-- 建立月份维度物化视图
create materialized view mv_month_dim as
select row_number() over (order by t1.year,t1.month) month_sk, t1.*
  from (select distinct month, month_name, quarter, year from date_dim) t1
distributed by (month_sk);
 
-- 查询月份维度物化视图
select * from mv_month_dim order by month_sk;

这种方案有三个明显的优点：一是实现简单，只要用一个聚合查询定义好物化视图，数据就自动初始装载好了；二是由于物理存储数据，能提供更好的查询性能；三是物化视图也是视图，除刷新外不能更新其数据，因此不存在数据不一致问题。但是，在Greenplum中没有任何办法能做到实时自动刷新物化视图。首先Greenplum的物化视图没有提供类似于Oracle的refresh on commit刷新机制，其次Greenplum的rule与refresh materialized view不兼容，最后在PostgreSQL中通常使用触发器来实现物化视图实时刷新，而Greenplum又不支持触发器。基于以上原因，Greenplum的物化视图通常需要依赖于cron似的调度系统，定时周期性刷新数据，适合非实时场景，如这里的月份维度。

refresh命令用于刷新物化视图：


-- 全量刷新
refresh materialized view mv_month_dim;

concurrently选项用于增量刷新，但要求物化视图上存在唯一索引：


dw=> refresh materialized view concurrently mv_month_dim;
ERROR:  cannot refresh materialized view "rds.mv_month_dim" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

物化视图需要额外的空间，因为新创建的子维度需要物理存储数据。如果需要实时更新的数据，又不想占用空间，常用的做法是在基本维度上建立普通视图生成子维度。


-- 建立月份维度视图
create view month_dim as
select row_number() over (order by t1.year,t1.month) month_sk, t1.*
  from (select distinct month, month_name, quarter, year from date_dim) t1;
 
-- 查询份维度视图
select * from month_dim;

这种方法的缺点也十分明显：当基本维度表和子维度表的数据量相差悬殊时，性能会比物理表差得多；如果定义视图的查询很复杂，并且视图很多的话，可能会对元数据存储系统造成压力，影响查询性能。如果数据量不是特别大，该方法是一个不错的选择，它实现简单，不占用存储空间，能提供实时数据并消除数据不一致的可能，而对海量数据提供高性能查询正是Greenplum的强项。

视图是与存储无关的纯粹的逻辑对象，当查询引用了一个视图，视图的定义被评估后产生一个行集，用作查询后续的处理。这只是一个概念性的描述，实际上，作为查询优化的一部分，Greenplum可能把视图的定义和查询结合起来考虑，而不一定是先生成视图所定义的行集。例如，优化器可能将查询的过滤条件下推到视图中。

2. 建立包含行子集的子维度
当两个维度处于同一细节粒度，但是其中一个仅仅是行的子集时，会产生另外一种一致性维度构造子集。例如，销售订单示例中，客户维度表包含多个州的客户信息。对于不同州的销售分析可能需要浏览客户维度的子集，需要分析的维度仅包含部分客户数据。通过使用行的子集，不会破坏整个客户集合。当然，与该子集连接的事实表必须被限制在同样的客户子集中。

月份维度是一个上卷维度，包含基本维度的上层数据。而特定维度子集是选择基本维度的行子集。同样可以考虑物化视图和普通视图两种实现方式。


-- 建立PA维度视图
create view pa_customer_dim as 
select * from customer_dim where customer_state = 'pa';
 
-- 建立PA维度物化视图
create materialized view mv_pa_customer_dim as 
select * from customer_dim where customer_state = 'pa'
distributed by (customer_sk);

如果必须兼顾查询性能与实时性，还有一种实现方案是在rule中增加子维度逻辑。例如对于PA维度，可以在上一篇定义的r_delete_customer、r_insert_customer、r_update_customer中增加一套几乎完全相同的逻辑，只要在where条件中增加customer_state = 'pa'。除了情非得已，我还是建议采用普通视图方案，毕竟复制一份相同逻辑会让人感到索然无味。

注意，PA客户维度子集与月份维度子集有两点区别：

pa_customer_dim表和customer_dim表有完全相同的列，而month_dim不包含date_dim表的日期列。
pa_customer_dim表的代理键就是客户维度的代理键，而month_dim表里的月份维度代理键并不来自日期维度，而是独立生成的。

定义好视图后，执行下面的SQL脚本往客户源数据里添加一个PA州的客户和四个OH州的客户：


use source;  
insert into customer  
(customer_name, customer_street_address, customer_zip_code,   
 customer_city, customer_state, shipping_address,  
 shipping_zip_code, shipping_city, shipping_state)  
values  
('pa customer', '1111 louise dr.', '17050', 
'mechanicsburg', 'pa', '1111 louise dr.', 
'17050', 'mechanicsburg', 'pa'),  
('bigger customers', '7777 ridge rd.', '44102', 
'cleveland', 'oh', '7777 ridge rd.', 
'44102', 'cleveland', 'oh'),   
('smaller stores', '8888 jennings fwy.', '44102', 
'cleveland', 'oh', '8888 jennings fwy.', 
'44102', 'cleveland', 'oh'),  
('small-medium retailers', '9999 memphis ave.', '44102', 
'cleveland', 'oh', '9999 memphis ave.', 
'44102', 'cleveland', 'oh'),  
('oh customer', '6666 ridge rd.', '44102', 
'cleveland', 'oh', '6666 ridge rd.', 
'44102','cleveland', 'oh') ;  
commit;

在目标库执行下面的查询验证结果，pa_customer_dim表此时应该有20条记录。


select customer_number, customer_state, effective_dt, expiry_dt 
  from pa_customer_dim 
 order by customer_number, version;

7.3 角色扮演维度

单个物理维度可以被事实表多次引用，每个引用连接逻辑上存在差异的角色维度。例如，事实表可以有多个日期，每个日期通过外键引用不同的日期维度，原则上每个外键表示不同的日期维度视图，这样引用具有不同的含义。这些不同的维度视图具有唯一的代理键列名，被称为角色，相关维度被称为角色扮演维度。

当一个事实表多次引用一个维度表时会用到角色扮演维度。例如，一个销售订单有一个是订单日期，还有一个请求交付日期，这时就需要引用日期维度表两次。我们期望在每个事实表中设置日期维度，因为总是希望按照时间来分析业务情况。在事务型事实表中，主要的日期列是事务日期，例如，订单日期。有时会发现其他日期也可能与每个事实关联，例如，订单事务的请求交付日期。每个日期应该成为事实表的外键。

本节将说明两类角色扮演维度的实现，分别是表别名和数据库视图。表别名是在SQL语句里引用维度表多次，每次引用都赋予维度表一个别名。而数据库视图，则是按照事实表需要引用维度表的次数，建立相同数量的视图。我们先修改销售订单数据库表结构，添加一个请求交付日期字段，并对数据抽取和装载脚本做相应的修改。这些表结构修改好后，插入测试数据，演示别名和视图在角色扮演维度中的用法。

1. 停止Canal Server、Canal Adapter


# 停止Canal Server，构成Canal HA的126、127两台都执行
~/canal_113/deployer/bin/stop.sh
# 停止Canal Adapter，126执行
~/canal_113/adapter/bin/stop.sh

2. 修改表结构
（1）修改源数据库表结构
源库中销售订单表sales_order增加request_delivery_date字段。


-- 126 MySQL主库执行
use source;    
alter table sales_order add request_delivery_date date after order_date;

（2）修改目标数据库表结构


-- 修改RDS数据库模式里的表
set search_path to rds;
alter table sales_order add column request_delivery_date date; 
 
-- 修改TDS数据库模式里的表
set search_path to tds, rds;
alter table sales_order_fact add column request_delivery_date_sk int;

修改后的数据仓库模式如图7-2所示。

图7-2 数据仓库中增加请求交付日期属性

从图中可以看到，销售订单事实表和日期维度表之间有两条连线，表示订单日期和请求交付日期都是引用日期维度表的外键。注意，虽然图中显示了表之间的关联关系，但Greenplum中并不强制外键数据库约束。

3. 修改Canal Adapter表映射
在sales_order.yml文件中添加新增字段的映射。


[mysql@node2~/canal_113/adapter/conf/rdb]$cat sales_order.yml 
...
    request_delivery_date: request_delivery_date
  commitBatch: 30000 # 批量提交的大小

4. 重建sales_order_fact事实表rule
在装载销售订单事实表时，关联日期维度表两次，分别赋予别名e和f。事实表和两个日期维度表关联，取得日期代理键。e.date_sk表示订单日期代理键，f.date_sk表示请求交付日期的代理键。


drop rule r_insert_sales_order on sales_order;
create rule r_insert_sales_order as on insert to sales_order do also 
(insert into order_dim (order_number,version,effective_dt,expiry_dt) 
 values (new.order_number,1,'2021-06-01','2200-01-01');
 
insert into sales_order_fact(order_sk,customer_sk,product_sk,order_date_sk,year_month,order_amount,order_quantity,request_delivery_date_sk) 
 select d.order_sk, customer_sk, product_sk, e.date_sk, to_char(order_date, 'YYYYMM')::int, order_amount, order_quantity, f.date_sk
   from rds.sales_order a, customer_dim b, product_dim c, order_dim d, date_dim e, date_dim f
  where a.order_number = new.order_number and d.order_number = new.order_number
    and a.customer_number = b.customer_number and b.expiry_dt = '2200-01-01'
    and a.product_code = c.product_code and c.expiry_dt = '2200-01-01'
    and date(a.order_date) = e.date
    and date(a.request_delivery_date) = f.date);

5. 启动Canal Server、Canal Adapter


# 启动Canal Server，在构成Canal HA的126、127两台顺序执行
~/canal_113/deployer/bin/startup.sh
 
# 在126执行
~/canal_113/adapter/bin/startup.sh

6. 测试
执行下面的SQL脚本在源库中增加三个带有交货日期的销售订单。


-- 126 MySQL主库执行
use source;    
-- 新增订单日期为2021年12月31日的3条订单。
set sql_log_bin = 0;
  
set @start_date := unix_timestamp('2021-12-31');    
set @end_date := unix_timestamp('2022-01-01');
-- 请求交付日期为2022年1月4日。
set @request_delivery_date := '2022-01-04';    
drop table if exists temp_sales_order_data;    
create table temp_sales_order_data as select * from sales_order where 1=0;     
 
set @customer_number := floor(1 + rand() * 14);
set @product_code := floor(1 + rand() * 4);  
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date));    
set @amount := floor(1000 + rand() * 9000);  
set @quantity := floor(10 + rand() * 90);  
insert into temp_sales_order_data 
values (1, @customer_number, @product_code, @order_date, @request_delivery_date, @order_date, @amount, @quantity);    
 
... 新增3条订单 ...   
 
set sql_log_bin = 1;
insert into sales_order    
select null,customer_number,product_code,order_date,request_delivery_date,entry_date,order_amount,order_quantity 
  from temp_sales_order_data 
 order by order_date;      
commit ;

使用查询验证结果，可以看到只有三个新的销售订单具有request_delivery_date_sk值，735对应的日期是2022年1月4日。


dw=> select a.order_sk, request_delivery_date_sk, c.date  
dw->   from sales_order_fact a, date_dim b, date_dim c  
dw->  where a.order_date_sk = b.date_sk   
dw->    and a.request_delivery_date_sk = c.date_sk 
dw->  order by order_sk;
 order_sk | request_delivery_date_sk |    date    
----------+--------------------------+------------
      126 |                      735 | 2022-01-04
      127 |                      735 | 2022-01-04
      128 |                      735 | 2022-01-04
(3 rows)

使用角色扮演维度查询：


-- 使用表别名查询  
select order_date_dim.date order_date,    
        request_delivery_date_dim.date request_delivery_date,    
        sum(order_amount),count(*)    
  from sales_order_fact a,
        date_dim order_date_dim,    
        date_dim request_delivery_date_dim    
 where a.order_date_sk = order_date_dim.date_sk    
   and a.request_delivery_date_sk = request_delivery_date_dim.date_sk    
 group by order_date_dim.date , request_delivery_date_dim.date    
 order by order_date_dim.date , request_delivery_date_dim.date;  
  
-- 使用视图查询  
-- 创建订单日期视图  
create view order_date_dim 
(order_date_sk, 
 order_date, 
 month, 
 month_name,  
 quarter, 
 year) 
as select * from date_dim;    
-- 创建请求交付日期视图
create view request_delivery_date_dim
(request_delivery_date_sk, 
 request_delivery_date, 
 month, 
 month_name, 
 quarter, 
 year)   
as select * from date_dim;  
-- 查询
select order_date,request_delivery_date,sum(order_amount),count(*)    
  from sales_order_fact a,order_date_dim b,request_delivery_date_dim c    
 where a.order_date_sk = b.order_date_sk    
   and a.request_delivery_date_sk = c.request_delivery_date_sk    
 group by order_date , request_delivery_date    
 order by order_date , request_delivery_date;

上面两个查询是等价的。尽管不能连接到单一的日期维度表，但可以建立并管理单独的物理日期维度表，然后使用视图或别名建立两个不同日期维度的描述。注意在每个视图或别名列中需要唯一的标识。例如，订单日期属性应该具有唯一标识order_date以便与请求交付日期request_delivery_date区别。别名与视图在查询中的作用并没有本质的区别，都是为了从逻辑上区分同一个物理维度表。许多BI工具也支持在语义层使用别名。但是，如果有多个BI工具，连同直接基于SQL的访问，都同时在组织中使用的话，不建议采用语义层别名的方法。当某个维度在单一事实表中同时出现多次时，则会存在维度模型的角色扮演。基本维度可能作为单一物理表存在，但是每种角色应该被当成标识不同的视图展现到BI工具中。

7. 一种有问题的设计
为处理多日期问题，一些设计者试图建立单一日期维度表，该表使用一个键表示每个订单日期和请求交付日期的组合，例如：


create table date_dim (date_sk int, order_date date, delivery_date date);
create table sales_order_fact (date_sk int, order_amount int);

这种方法存在两个方面的问题。首先，如果需要处理所有日期维度的组合情况，则包含大约每年365行的清楚、简单的日期维度表将会极度膨胀。例如，订单日期和请求交付日期存在如下多对多关系：
订单日期         请求交付日期
2021-12-17        2021-12-20
2021-12-18        2021-12-20
2021-12-19        2021-12-20
2021-12-17        2021-12-21
2021-12-18        2021-12-21
2021-12-19        2021-12-21
2021-12-17        2021-12-22
2021-12-18        2021-12-22
2021-12-19        2021-12-22

如果使用角色扮演维度，日期维度表中只需要2021-12-17到2021-12-22六条记录。而采用单一日期表设计方案，每一个组合都要唯一标识，明显需要九条记录。当两种日期及其组合很多时，这两种方案的日期维度表记录数会相去甚远。

其次，合并的日期维度表不再适合其他经常使用的日、周、月等日期维度。日期维度表每行记录的含义不再指唯一一天，因此无法在同一张表中标识出周、月等一致性维度，进而无法简单地处理按时间维度的上卷、聚合等需求。

7.4 层次维度

大多数维度都具有一个或多个层次。示例数据仓库中的日期维度就有一个四级层次：年、季度、月和日。这些级别用date_dim表里的列表示。日期维度是一个单路径层次，因为除了年-季度-月-日这条路径外，它没有任何其他层次。为了识别数据仓库里一个维度的层次，首先要理解维度中列的含义，然后识别两个或多个列是否具有相同的主题。年、季度、月和日具有相同的主题，因为它们都是关于日期的。具有相同主题的列形成一个组，组中的一列必须包含至少一个组内的其他成员（除了最低级别的列），前面提到的组中，月包含日。这些列的链条形成了一个层次，例如，年-季度-月-日这个链条是一个日期维度的层次。除了日期维度，邮编维度中的地理位置信息，产品维度的产品与产品分类，也都构成层次关系。表7-1显示了三个维度的层次。

customer_dim		product_dim	date_dim
customer_street_address	shipping_address	product_name	date
customer_zip_code	shipping_zip_code	product_category	month
customer_city	shipping_city		quarter
customer_state	shipping_state		year

表7-1 销售订单数据仓库中的层次维度

本节描述处理层次关系的方法，包括在固定深度的层次上进行分组和钻取查询，多路径层次和参差不齐层次的处理等，从最基本的情况开始讨论。

7.4.1 固定深度的层次

固定深度层次是一种一对多关系，如一年中有四个季度，一个季度包含三个月等等。当固定深度层次定义完成后，层次就具有了固定的名称，层次级别作为维度表中的不同属性出现。只要满足上述条件，固定深度层次就是最容易理解和查询的层次关系，固定层次也能够提供可预测的、快速的查询性能。可以在固定深度层次上进行分组和钻取查询。

分组查询是把度量按照一个维度的一个或多个级别进行分组聚合。下面的脚本是一个分组查询的例子。该查询按产品（product_category列）和日期维度的三个层次级别（year、quarter和month列）分组返回销售金额。


select product_category,year,quarter,month,sum(order_amount) s_amount    
  from  sales_order_fact a,product_dim b,date_dim c      
 where  a.product_sk = b.product_sk      
   and  a.year_month = c.year * 100 + c.month      
 group by product_category, year, quarter, month      
 order by product_category, year, quarter, month;

这是一个非常简单的分组查询，结果输出的每一行度量（销售订单金额）都沿着年-季度-月的层次分组。

与分组查询类似，钻取查询也把度量按照一个维度的一个或多个级别进行分组。但与分组查询不同的是，分组查询只显示分组后最低级别，即本例中月级别上的度量，而钻取查询显示分组后维度每一个级别的度量。下面使用UNION ALL和GROUPING SETS两种方法进行钻取查询，结果显示了每个日期维度级别，即年、季度和月各级别的订单汇总金额。


-- 使用union all     
select product_category, time, order_amount    
  from (select product_category,     
               case when sequence = 1 then 'year: '||time  
                    when sequence = 2 then 'quarter: '||time   
                    else 'month: '||time    
               end time,    
               order_amount,   
               sequence,   
               date   
          from (select product_category, min(date) date, year time, 1 sequence, sum(order_amount) order_amount    
                  from sales_order_fact a, product_dim b, date_dim c      
                 where a.product_sk = b.product_sk      
                   and a.year_month = c.year * 100 + c.month      
                 group by product_category , year    
                 union all     
                select product_category, min(date) date, quarter time, 2 sequence, sum(order_amount) order_amount    
                  from sales_order_fact a, product_dim b, date_dim c      
                 where a.product_sk = b.product_sk      
                   and a.year_month = c.year * 100 + c.month      
                 group by product_category , year , quarter     
                 union all     
                select product_category, min(date) date, month time, 3 sequence, sum(order_amount) order_amount    
                  from sales_order_fact a, product_dim b, date_dim c      
                 where a.product_sk = b.product_sk      
                   and a.year_month = c.year * 100 + c.month      
                 group by product_category , year , quarter , month) x) y  
 order by product_category , date , sequence , time;  
 
-- 使用grouping sets  
select product_category,     
       case when gid = 3 then 'year: '||year    
            when gid = 1 then 'quarter: '||quarter    
            else 'month: '||month    
        end time,    
       order_amount  
  from (select product_category, year, quarter, month, min(date) date, sum(order_amount) order_amount,grouping(product_category,year,quarter,month) gid  
          from sales_order_fact a, product_dim b, date_dim c      
         where a.product_sk = b.product_sk   
           and a.year_month = c.year * 100 + c.month     
         group by grouping sets ((product_category,year,quarter,month),(product_category,year,quarter),(product_category,year))) x  
 order by product_category , date , gid desc, time;

以上两种不同写法的查询语句结果相同：


 product_category |    time    | order_amount 
------------------+------------+--------------
 monitor          | year: 2021 |   2332781.00
 monitor          | quarter: 4 |   2332781.00
 monitor          | month: 12  |   2332781.00
 peripheral       | year: 2021 |    809131.00
 peripheral       | quarter: 4 |    809131.00
 peripheral       | month: 12  |    809131.00
 storage          | year: 2021 |  19350932.00
 storage          | quarter: 2 |   3848670.00
 storage          | month: 6   |   3848670.00
 storage          | quarter: 3 |  13661172.00
 storage          | month: 7   |   4149133.00
 storage          | month: 8   |   4292849.00
 storage          | month: 9   |   5219190.00
 storage          | quarter: 4 |   1841090.00
 storage          | month: 12  |   1841090.00
(15 rows)

第一条语句的子查询中使用union all集合操作，将年、季度、月三个级别的汇总数据联合成一个结果集。注意union all的每个查询必须包含相同个数和类型的字段。附加的min(date)和sequence导出列用于对输出结果排序显示。这种写法使用标准的SQL语法，具有通用性。

第二条语句使用Greenplum提供的grouping函数和group by grouping sets子句。grouping set对列出的每一个字段组进行group by操作，如果字段组为空，则不进行分组处理。因此该语句会生成按产品类型、年、季度、月；类型、年、季度；类型、年分组的聚合数据行。grouping(<column> [, …])函数用于区分查询结果中的null值是属于列本身的还是聚合的结果行。该函数为每个参数产生一位0或1，1代表结果行是聚合行，0表示结果行是正常分组数据行。函数值使用了位图策略（bitvector，位向量），即它的二进制形式中的每一位表示对应列是否参与分组，如果某一列参与了分组，对应位就被置为1，否则为0。最后将二进制数转换为十进制数返回。通过这种方式可以区分出数据本身中的null值。

7.4.2 多路径层次

多路径层次是对单路径层次的扩展。现在数据仓库的月维度只有一条层次路径，即年-季度-月这条路径。现在增加一个新的“促销期”级别，并且加一个新的年-促销期-月的层次路径。这时月维度将有两条层次路径，因此是多路径层次维度。下面的脚本给month_dim表添加一个叫做campaign_session的新列，并建立rds.campaign_session过渡表。


-- 增加促销期列  
set search_path=tds;
 
alter view month_dim rename to month_dim_old;  
 
create table tds.month_dim (      
    month_sk bigint,      
    month smallint,    
    month_name varchar(9),   
    campaign_session varchar(30),  
    quarter smallint,    
    year smallint     
) distributed by (month_sk);
 
insert into tds.month_dim 
select month_sk,month,month_name,null,quarter,year from tds.month_dim_old;  
 
drop view month_dim_old;  
 
-- 建立促销期过渡表 
set search_path=rds;
   
create table campaign_session 
(campaign_session varchar(30),month smallint, year smallint) distributed by (campaign_session);

假设所有促销期都不跨年，并且一个促销期可以包含一个或多个月份，但一个月份只能属于一个促销期。为了理解促销期如何工作，表7-2给出了一个促销期定义的示例。

促销期	月份
2021 年第一促销期	1月—4月
2021 年第二促销期	5月—7月
2021 年第三促销期	8月
2021 年第四促销期	9月—12月

表7-2 2021年促销期

每个促销期有一个或多个月。一个促销期也许并不是正好一个季度，也就是说，促销期级别不能上卷到季度，但是促销期可以上卷至年级别。假设2021年促销期的数据如下，并保存在/home/gpadmin/campaign_session.csv文件中。


2020 First Campaign,1,2020
2020 First Campaign,2,2020
2020 First Campaign,3,2020
2020 First Campaign,4,2020
2020 Second Campaign,5,2020
2020 Second Campaign,6,2020
2020 Second Campaign,7,2020
2020 Third Campaign,8,2020
2020 Last Campaign,9,2020
2020 Last Campaign,10,2020
2020 Last Campaign,11,2020
2020 Last Campaign,12,2020

现在可以执行下面的语句把2021年的促销期数据装载进月维度。本地文件必须在Greenplum master主机上的本地目录中，并且copy命令需要使用gpadmin用户执行。


# 用gpadmin执行
psql -d dw -c "copy rds.campaign_session from '/home/gpadmin/campaign_session.csv' with delimiter ',';"
 
# 用dwtest连接
psql -U dwtest -h mdw -d dw
 
-- 设置搜索路径
set search_path = tds;  
 
-- 两表关联更新，注意set中不能使用别名
update month_dim as t1
   set campaign_session = t2.campaign_session
  from rds.campaign_session as t2
 where t1.year = t2.year   
   and t1.month = t2.month;

此时查询月份维度表，可以看到2021年的促销期已经有数据，其他年份的campaign_session字段值为空。

7.4.3 参差不齐的层次

在一个或多个级别上没有数据的层次称为不完全层次。例如，在特定月份没有促销期，那么月维度就具有不完全促销期层次。下面是一个不完全促销期的例子，数据存储在ragged_campaign.csv文件中。2022年1月、4月、6月、9月、10月、11月和12月没有促销期。


,1,2021
2021 Early Spring Campaign,2,2021
2021 Early Spring Campaign,3,2021
,4,2021
2021 Spring Campaign,5,2021
,6,2021
2021 Last Campaign,7,2021
2021 Last Campaign,8,2021
,9,2021
,10,2021
,11,2021
,12,2021

向month_dim表装载2021年的促销期数据。


# 用gpadmin执行
psql -d dw -c "copy rds.campaign_session from '/home/gpadmin/ragged_campaign.csv' with delimiter ',';"
 
# 用dwtest连接
psql -U dwtest -h mdw -d dw
 
-- 设置搜索路径
set search_path = tds;  
 
-- 两表关联更新，注意set中不能使用别名
update month_dim as t1
   set campaign_session = case when t2.campaign_session != '' then t2.campaign_session else t1.month_name end
  from rds.campaign_session as t2
 where t1.year = t2.year   
   and t1.month = t2.month;

在有促销期的月份，campaign_session列填写促销期名称，而对于没有促销期的月份，该列填写月份名称。轻微参差不齐层次没有固定的层次深度，但层次深度有限。如地理层次深度通常包含3 ~ 6层。与其使用复杂的机制构建难以预测的可变深度层次，不如将其变换为固定深度位置设计，针对不同的维度属性确立最大深度，然后基于业务规则放置属性值。

下面的语句查询年-促销期-月层次。


select product_category,         
       case when gid = 3 then cast(year as varchar(10))        
            when gid = 1 then campaign_session       
            else month_name  
        end time,     
       order_amount      
  from (select product_category, year, campaign_session, month, month_name,   
               sum(order_amount) order_amount,   
               sum(order_quantity) order_quantity,  
               grouping(product_category,year,campaign_session,month) gid,  
               min(month) min_month 
          from sales_order_fact a, product_dim b, month_dim c
         where a.product_sk = b.product_sk       
           and a.year_month = c.year * 100 + c.month    
           and c.year = 2021                  
         group by grouping sets ((product_category,year,campaign_session,month,month_name),(product_category,year,campaign_session),(product_category,year))) x      
 order by product_category, min_month, gid desc, month;

查询结果如下：


 product_category |        time        | order_amount 
------------------+--------------------+--------------
 monitor          | 2021               |     75251.00
 monitor          | dec                |     75251.00
 monitor          | dec                |     75251.00
 peripheral       | 2021               |     26101.00
 peripheral       | dec                |     26101.00
 peripheral       | dec                |     26101.00
 storage          | 2021               |    633974.00
 storage          | jun                |    128289.00
 storage          | jun                |    128289.00
 storage          | 2021 Last Campaign |    272322.00
 storage          | jul                |    133843.00
 storage          | aug                |    138479.00
 storage          | sep                |    173973.00
 storage          | sep                |    173973.00
 storage          | dec                |     59390.00
 storage          | dec                |     59390.00
(16 rows)

min_month列用于排序。在有促销期月份的路径，月级别的行的汇总与促销期级别的行相同。而对于没有促销期的月份，其促销期级别的行与月级别的行相同。也就是说，在没有促销期级别的月份，月上卷了它们自己。例如，2021年6月没有促销期，所以在输出看到，每种产品分类有两个相同的6月的行，其中后一行是月份级别的行，前一行表示是没有促销期的行。对于没有促销期的月份，促销期行的销售订单金额（输出里的order_amount列）与月分行的相同。

7.5 退化维度

退化维度技术减少维度的数量，简化多维数据仓库模式。简单的模式比复杂的更容易理解，也有更好的查询性能。有时，维度表中除了业务主键外没有其他内容。在本销售订单示例中，订单维度表除了订单号，没有任何其他属性，而订单号是事务表的主键，这种维度就是退化维度。业务系统中的主键通常是不允许修改的。销售订单只能新增，不能修改已经存在的订单号，也不会删除订单记录。因此订单维度表也不会有历史数据版本问题。

销售订单事实表中的每行记录都包括作为退化维度的订单号代理键。在操作型系统中，销售订单表是最细节事务表，订单号是订单表的主键，每条订单都可以通过订单号定位，订单中的其他属性，如客户、产品等，都依赖于订单号。也就是说，订单号把与订单属性有关的表联系起来。但是，在维度模型中，事实表中的订单号代理键通常与订单属性的其他表没有直接关联，而是将订单事实表所有关心的属性分类到不同的维度中。例如，订单日期关联到日期维度，客户关联到客户维度等。在事实表中保留订单号最主要的原因是用于连接数据仓库与操作型系统，它也可以起到事实表主键的作用。某些情况下，可能会有一个或两个属性仍然属于订单而不属于其他维度。当然，此时订单维度就不再是退化维度了。

退化维度常被保留作为操作型事务的标识符。实际上可以将订单号作为一个属性加入到事实表中。这样订单维度就没有数据仓库需要的任何数据，此时就可以退化订单维度。需要把退化维度的相关数据迁移到事实表中，然后删除退化的维度。操作型事务中的控制号码，如订单号码、发票号码、提货单号码等通常产生空的维度并表示为事务事实表中的退化维度。

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

图7-3 退化订单维度

退化order_dim维度表逻辑上需要执行以下四步操作：

给sales_order_fact表添加order_number列
把order_dim表里的订单号迁移到sales_order_fact表
删除sales_order_fact表里的order_sk列
删除order_dim表

在Greenplum分布式数据库上还需要考虑主键、唯一索引和分布键的问题。回想“在Greenplum中创建目标库对象”时，我们将sales_order_fact表的主键定义为(order_sk, customer_sk, product_sk, order_date_sk, year_month)，并将分布键指定为order_sk，现在是修正这个遗留问题的时候了。

Greenplum要求分布键必须是唯一索引列的子集，要指定order_number列作为分布键，先要在order_number列上创建唯一索引，而order_number列上创建的唯一索引，要包含现有分布键order_sk。但order_sk列最终是要被移除的，没必要在它上面建索引。再者，改变表的分布键会导致重新分布全部数据，几乎相当于重建表。基于这两点原因，我们选择重建sales_order_fact表。对于分布式数据库，分布键选择的重要性怎么强调都不为过，最好是在建表时就考虑清楚，如果非要重新选择分布键，建议重建表以简化操作。

因为涉及修改表结构，同样需要在执行这些操作前停止Canal以暂停数据同步。

1. 停止Canal Server、Canal Adapter


# 停止Canal Server，构成Canal HA的126、127两台都执行
~/canal_113/deployer/bin/stop.sh
# 停止Canal Adapter，126执行
~/canal_113/adapter/bin/stop.sh

2. 退化订单维度
（1）新建事实表


set search_path=tds;
create table sales_order_fact_new
(order_number             bigint, 
 customer_sk              bigint,
 product_sk               bigint,
 order_date_sk            integer,
 request_delivery_date_sk integer,
 year_month               integer,
 order_amount             numeric(10,2),
 order_quantity           integer,
 primary key (order_number, year_month))
 distributed by (order_number)
partition by range (year_month)  
( partition p202106 start (202106) inclusive ,
  partition p202107 start (202107) inclusive ,
  partition p202108 start (202108) inclusive ,
  partition p202109 start (202109) inclusive ,
  partition p202110 start (202110) inclusive ,
  partition p202111 start (202111) inclusive ,
  partition p202112 start (202112) inclusive ,  
  partition p202201 start (202201) inclusive ,  
  partition p202202 start (202202) inclusive ,  
  partition p202203 start (202203) inclusive   
                     end (202204) exclusive );

（2）装载新事实表


insert into sales_order_fact_new
select t2.order_number, t1.customer_sk, t1.product_sk, t1.order_date_sk, t1.request_delivery_date_sk, t1.year_month, t1.order_amount, t1.order_quantity
  from sales_order_fact t1, order_dim t2
 where t1.order_sk = t2.order_sk;

（3）删除老事实表

drop table sales_order_fact cascade;

由于rds.sales_order表上的规则r_insert_sales_order使用了sales_order_fact表，存在依赖关系，所以要加上cascade级联删除，否则报错：


dw=> drop table sales_order_fact;
ERROR:  cannot drop table sales_order_fact because other objects depend on it
DETAIL:  rule r_insert_sales_order on table rds.sales_order depends on table sales_order_fact
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

（4）改表名

alter table sales_order_fact_new rename to sales_order_fact;

（5）分析表

analyze sales_order_fact;

（6）删除订单维度表

drop table order_dim;

3. 重建被级联删除的r_insert_sales_order规则
主要做以下两点修改：

去掉装载order_dim维度表的语句。
事实表中的order_number字段从rds.sales_order表获得。


create rule r_insert_sales_order as on insert to rds.sales_order do also 
(insert into sales_order_fact(order_number,customer_sk,product_sk,order_date_sk,request_delivery_date_sk,year_month,order_amount,order_quantity) 
 select new.order_number, customer_sk, product_sk, d.date_sk, e.date_sk, to_char(new.order_date, 'YYYYMM')::int, new.order_amount, new.order_quantity
   from customer_dim b, product_dim c, date_dim d, date_dim e
  where new.customer_number = b.customer_number and b.expiry_dt = '2200-01-01'
    and new.product_code = c.product_code and c.expiry_dt = '2200-01-01'
    and date(new.order_date) = d.date
    and date(new.request_delivery_date) = e.date);

4. 启动Canal Server、Canal Adapter


# 启动Canal Server，在构成Canal HA的126、127两台顺序执行
~/canal_113/deployer/bin/startup.sh
 
# 在126执行
~/canal_113/adapter/bin/startup.sh

5. 测试
执行下面的SQL语句在源库上生成2021年12月31日的两条销售订单。为了保证自增订单号与订单时间顺序相同，注意一下@order_date变量的赋值。


-- 126 MySQL主库执行
use source;    
-- 新增订单日期为2021年12月31日的2条订单。
set sql_log_bin = 0;
  
set @start_date := unix_timestamp('2021-12-31 12:00:00');    
set @end_date := unix_timestamp('2022-01-01');
-- 请求交付日期为2022年1月4日。
set @request_delivery_date := '2022-01-04';    
drop table if exists temp_sales_order_data;    
create table temp_sales_order_data as select * from sales_order where 1=0;     
 
set @customer_number := floor(1 + rand() * 14);
set @product_code := floor(1 + rand() * 4);  
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date));    
set @amount := floor(1000 + rand() * 9000);  
set @quantity := floor(10 + rand() * 90);  
insert into temp_sales_order_data 
values (1, @customer_number, @product_code, @order_date, @request_delivery_date, @order_date, @amount, @quantity);    
 
... 新增2条订单 ...   
 
set sql_log_bin = 1;
insert into sales_order    
select null,customer_number,product_code,order_date,request_delivery_date,entry_date,order_amount,order_quantity 
  from temp_sales_order_data 
 order by order_date;      
commit ;

脚本执行成功后，查询sales_order_fact表，验证新增的两条订单是否正确装载。


select a.order_number onum, customer_name cname, product_name pname, e.date odate, f.date rdate, 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;

查询结果如下，可以看到新增两条记录的订单号被正确装载。


 onum |         cname          |      pname      |   odate    |   rdate    | amount  | quantity 
------+------------------------+-----------------+------------+------------+---------+----------
  130 | loyal clients          | floppy drive    | 2021-12-31 | 2022-01-04 | 8237.00 |       33
  129 | small-medium retailers | keyboard        | 2021-12-31 | 2022-01-04 | 3703.00 |       21
  128 | loyal clients          | hard disk drive | 2021-12-31 | 2022-01-04 | 8663.00 |       65
  127 | subsidiaries           | flat panel      | 2021-12-31 | 2022-01-04 | 7426.00 |       99
  126 | small-medium retailers | flat panel      | 2021-12-31 | 2022-01-04 | 3081.00 |       65
(5 rows)

7.6 杂项维度

1. 什么是杂项维度
简单地说，杂项维度就是一种包含的数据具有很少可能值的维度。事务型商业过程通常产生一系列混杂的、低基数的标志位或状态信息。与其为每个标志或属性定义不同的维度，不如建立单独的、将不同维度合并到一起的杂项维度。这些维度，通常在一个模式中标记为事务型概要维度，一般不需要所有属性可能值的笛卡尔积，但应该至少包含实际发生在源数据中的组合值。例如，在销售订单中，可能存在有很多离散数据（yes-no这种开关类型的值）：

verification_ind（如果订单已经被审核，值为yes）
credit_check_flag（表示此订单的客户信用状态是否已经被检查）
new_customer_ind（如果这是新客户的首个订单，值为yes）
web_order_flag（表示一个订单是在线上订单还是线下订单）

这类数据常被用于增强销售分析，其特点是属性可能很多但每种属性的可能值却很少。

2. 处理杂项维度的常用方法
在建模复杂的操作型源系统时，经常会遭遇大量五花八门的标志或状态信息，它们包含小范围的离散值。处理这些较低基数的标志或状态位可以采用以下几种方法。

（1）忽略这些标志和指标
姑且将这种回避问题的处理方式也算作方法之一吧。在开发ETL系统时，ETL开发小组可以向业务用户询问有关忽略这些标志的必要问题，如果它们是微不足道的。但是这样的方案通常立即就被否决了，因为有人偶尔还需要它们。

（2）保持事实表行中的标志位不变
以销售订单为例，和源数据库一样，我们可以在事实表中也建立这四个标志位字段。在装载事实表时，除了订单号以外，同时装载这四个字段的数据，这些字段没有对应的维度表，而是作为订单的属性保留在事实表中。

这种处理方法简单直接，装载过程不需要做大量修改，也不需要建立相关的维度表。但是一般我们不希望在事实表中存储难以识别的标志位，尤其是当每个标志位还配有一个文字描述字段时。不要在事实表行中存储包含大量字符的描述符，因为每一行都会有文字描述，它们可能会使表快速膨胀。在行中保留一些文本标志是令人反感的，比较好的做法是分离出单独的维度表保存这些标志位字段的数据，它们的数据量很小，并且极少改变。事实表通过维度表的代理键引用这些标志。

（3）将每个标志位放入其自己的维度中
例如，为销售订单的四个标志位分别建立四个对应的维度表。装载事实表数据前先处理这四个维度表，必要时生成新的代理键，然后在事实表中引用这些代理键。这种方法是将杂项维度当做普通维度来处理，多数情况下这也是不合适的。

首先，当类似的标志或状态位字段比较多时，需要建立很多的维度表，其次事实表的外键数也会大量增加。处理这些新增的维度表和外键需要大量修改数据装载脚本，还会增加出错的机会，同时会给ETL的开发、维护、测试过程带来很大的工作量。最后，杂项维度的数据有自己明显的特点，即属性多但每个属性的值少，并且极少修改，这种特点决定了它应该与普通维度的处理区分开。

作为一个经验值，如果外键的数量处于合理的范围中，即不超过20个，则在事实表中增加不同的外键是可以接受的。若外键列表已经很长，则应该避免将更多外键加入事实表中。

（4）将标志位字段存储到订单维度中
可以将标志位字段添加到订单维度表中。上一节我们将订单维度表作为退化维度删除了，因为它除了订单号，没有其他任何属性。与其将订单号当成是退化维度，不如视其为将低基数标志或状态作为属性的普通维度。事实表通过引用订单维度表的代理键，关联到所有的标志位信息。

尽管该方法精确地表示了数据关系，但依然存在前面讨论的问题。在订单维度表中，每条业务订单都会存在对应的一条销售订单记录，该维度表的记录数会膨胀到跟事实表一样多，而在如此多的数据中，每个标志位字段都存在大量的冗余。通常维度表应该比事实表小得多。

（5）使用杂项维度
处理这些标志位的适当替换方法是将它们包装为一个杂项维度，其中放置各种离散的标志或状态数据。对杂项维度数据量的估算会影响其建模策略。如果某个简单的杂项维度包含10个二值标识，则最多将包含1024（2^10）行。杂项维度可提供所有标识的组合，并用于基于这些标识的约束和报表。事实表与杂项维度之间存在一个单一的、小型的代理键。

如果具有高度非关联的属性，包含更多的数量值，则将它们合并为单一的杂项维度是不合适的。假设存在5个标识，每个仅包含3个值，则单一杂项维度是这些属性的最佳选择，因为维度最多仅有243（3^5）行。但如果5个没有关联的标识，每个具有100个可能值，建议建立不同维度，因为单一杂项维度表最大可能存在1亿（100^5）行。

关于杂项维度的一个微妙的问题是，在杂项维度中行的组合确定并已知的前提下，是应该事先为所有组合的完全笛卡尔积建立行，还是建立杂项维度行，只用于保存那些在源系统中出现的组合情况的数据。答案要看大概有多少可能的组合，最大行数是多少。一般来说，理论上组合的数量较小，比如只有几百行时，可以预装载所有组合的数据。而组合的数量大，那么在数据获取时，当遇到新标志或指标时再建立杂项维度行。当然，如果源数据中用到了全体组合时，那别无选择，只能预先装载好全部杂项维度数据。

3. 新增销售订单属性杂项维度
图7-4显示了增加杂项维度表后的数据仓库模式，这里只显示了和销售订单事务相关的表。

图7-4 杂项维度

给现有数据仓库新增一个销售订单属性杂项维度。需要新增一个名为sales_order_attribute_dim的杂项维度表，该表包括四个yes-no列：verification_ind、credit_check_flag、new_customer_ind和web_order_flag，各列的含义已经在本节开头说明。每个列可以有两个可能值中的一个，Y 或 N，因此sales_order_attribute_dim表最多有16（2^4）行。假设这16行已经包含了所有可能的组合，并且不考虑杂项维度修改的情况，则可以预装载这个维度，并且只需装载一次。

（1）停止Canal Server、Canal Adapter


# 停止Canal Server，构成Canal HA的126、127两台都执行
~/canal_113/deployer/bin/stop.sh
# 停止Canal Adapter，126执行
~/canal_113/adapter/bin/stop.sh

（2）修改表结构


-- 给源库的销售订单表增加对应的属性，126 MySQL主库执行
use source;       
alter table sales_order      
  add verification_ind char (1) after product_code,   
  add credit_check_flag char (1) after verification_ind,   
  add new_customer_ind char (1) after credit_check_flag,   
  add web_order_flag char (1) after new_customer_ind ;  
 
-- 修改RDS数据库模式里的表
set search_path to rds;
alter table sales_order 
  add verification_ind char (1),
  add credit_check_flag char (1),
  add new_customer_ind char (1),
  add web_order_flag char (1);  
  
-- 修改TDS数据库模式里的表
set search_path to tds;
 
-- 建立杂项维度表      
create table sales_order_attribute_dim (      
    sales_order_attribute_sk int primary key,      
    verification_ind char(1),      
    credit_check_flag char(1),      
    new_customer_ind char(1),      
    web_order_flag char(1) );   
 
-- 生成杂项维度数据，共插入16条记录
insert into sales_order_attribute_dim values 
(1, 'n', 'n', 'n', 'n'), (2, 'n', 'n', 'n', 'y'), (3, 'n', 'n', 'y', 'n'), (4, 'n', 'n', 'y', 'y'),
(5, 'n', 'y', 'n', 'n'), (6, 'n', 'y', 'n', 'y'), (7, 'n', 'y', 'y', 'n'), (8, 'n', 'y', 'y', 'y'),
(9, 'y', 'n', 'n', 'n'), (10, 'y', 'n', 'n', 'y'), (11, 'y', 'n', 'y', 'n'), (12, 'y', 'n', 'y', 'y'),
(13, 'y', 'y', 'n', 'n'), (14, 'y', 'y', 'n', 'y'), (15, 'y', 'y', 'y', 'n'), (16, 'y', 'y', 'y', 'y');  
 
-- 建立杂项维度外键      
alter table sales_order_fact add column sales_order_attribute_sk int;

（3）重建相关rule
杂项属性维度数据已经预装载，所以在中只需要修改处理事实表的部分规则。源数据中有四个属性列，而事实表中只对应一列，因此需要使用四列关联条件的组合确定杂项维度表的代理键值，并装载到事实表中。


drop rule r_insert_sales_order on rds.sales_order;
create rule r_insert_sales_order as on insert to rds.sales_order do also 
(insert into sales_order_fact(order_number,customer_sk,product_sk,order_date_sk,request_delivery_date_sk,year_month,order_amount,order_quantity, sales_order_attribute_sk) 
 select new.order_number, customer_sk, product_sk, d.date_sk, e.date_sk, to_char(new.order_date, 'YYYYMM')::int, new.order_amount, new.order_quantity, f.sales_order_attribute_sk
   from customer_dim b, product_dim c, date_dim d, date_dim e, sales_order_attribute_dim f
  where new.customer_number = b.customer_number and b.expiry_dt = '2200-01-01'
    and new.product_code = c.product_code and c.expiry_dt = '2200-01-01'
    and date(new.order_date) = d.date
    and date(new.request_delivery_date) = e.date
    and new.verification_ind = f.verification_ind
    and new.credit_check_flag = f.credit_check_flag
    and new.new_customer_ind = f.new_customer_ind
    and new.web_order_flag = f.web_order_flag);

（4）修改Canal Adapter表映射
sales_order.yml文件中添加新增四个字段的映射。


[mysql@node2~/canal_113/adapter/conf/rdb]$cat ~/canal_113/adapter/conf/rdb/sales_order.yml
...
    verification_ind: verification_ind
    credit_check_flag: credit_check_flag
    new_customer_ind: new_customer_ind
    web_order_flag: web_order_flag
  commitBatch: 30000 # 批量提交的大小

（5）启动Canal Server、Canal Adapter


# 启动Canal Server，在构成Canal HA的126、127两台顺序执行
~/canal_113/deployer/bin/startup.sh
 
# 在126执行
~/canal_113/adapter/bin/startup.sh

4. 测试
执行下面的语句添加八个销售订单。


-- 126 MySQL主库执行
use source;    
-- 新增订单日期为2021年12月31日的8条订单。
set sql_log_bin = 0;
  
set @start_date := unix_timestamp('2021-12-31 16:00:00');    
set @end_date := unix_timestamp('2022-01-01');
-- 请求交付日期为2022年1月4日。
set @request_delivery_date := '2022-01-04';    
drop table if exists temp_sales_order_data;    
create table temp_sales_order_data as select * from sales_order where 1=0;     
 
set @customer_number := floor(1 + rand() * 14);
set @product_code := floor(1 + rand() * 4);  
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date));    
set @amount := floor(1000 + rand() * 9000);  
set @quantity := floor(10 + rand() * 90);  
insert into temp_sales_order_data 
values (1, @customer_number, @product_code, 'y', 'y', 'y', 'n', @order_date, @request_delivery_date, @order_date, @amount, @quantity);    
 
... 添加各种属性组合的8条记录 ...   
 
set sql_log_bin = 1;
insert into sales_order    
select null,customer_number,product_code,verification_ind,credit_check_flag,new_customer_ind,web_order_flag,order_date,request_delivery_date,entry_date,order_amount,order_quantity 
  from temp_sales_order_data 
 order by order_date;      
commit ;

可以使用下面的分析性查询确认装载是否正确。该查询分析出检查了信用状态的新用户有所占的比例。


select round(cast(checked as float) / (checked + not_checked) * 100)||' % '
  from (select sum(case when credit_check_flag='y' then 1 else 0 end) checked,                
               sum(case when credit_check_flag='n' then 1 else 0 end) not_checked        
          from sales_order_fact a, sales_order_attribute_dim b      
 where new_customer_ind = 'y'                  
   and a.sales_order_attribute_sk = b.sales_order_attribute_sk) t;

查询结果如下：


 ?column? 
----------
 67 % 
(1 row)

7.7 维度合并

有一种合并维度的情况，就是本来属性相同的维度，因为某种原因被设计成重复的维度属性。随着数据仓库中维度的增加，我们会发现有些通用的数据存在于多个维度中。例如，在销售订单示例中，客户维度的客户地址相关信息、送货地址相关信息里都有邮编、城市和省份。下面说明如何把客户维度里的两个邮编相关信息合并到一个新的维度中。

1. 停止Canal Server、Canal Adapter


# 停止Canal Server，构成Canal HA的126、127两台都执行
~/canal_113/deployer/bin/stop.sh
# 停止Canal Adapter，126执行
~/canal_113/adapter/bin/stop.sh

2. 修改表结构
为了合并维度，需要改变数据仓库表结构。图7-5显示了修改后的模式。新增了一个zip_code_dim邮编信息维度表，sales_order_fact事实表的结构也做了相应的修改。图中只显示了与邮编维度相关的表。

图7-5 合并邮编信息维度

zip_code_dim维度表与销售订单事实表相关联。这个关系替换了事实表与客户维度的关系。sales_order_fact表需要两个关系，一个关联到客户地址邮编，另一个关联到送货地址邮编，相应的增加了两个外键字段。再次强调，Greenplum语法上虽然支持外键定义，但并不强制外键约束。

（1）创建邮编维度表
执行上面的语句创建邮编维度表。该维度表有邮编、城市、省份三个业务属性。邮编数量不多，也不会经常改变，所以主键zip_code_sk没有使用自增序列。


set search_path=tds;
create table zip_code_dim (    
    zip_code_sk int primary key,    
    zip_code int,    
    city varchar(30),    
    state varchar(2),    
    version int,    
    effective_date date,    
    expiry_date date );

（2）创建两个邮编视图
基于邮编维度表创建客户邮编和送货邮编视图，分别用作两个地理信息的角色扮演维度。


create view customer_zip_code_dim 
(customer_zip_code_sk, customer_zip_code, customer_city, 
 customer_state, version, effective_date, expiry_date) as    
select zip_code_sk, zip_code, city, state, version,  effective_date, expiry_date    
  from zip_code_dim;    
    
create view shipping_zip_code_dim 
(shipping_zip_code_sk, shipping_zip_code, shipping_city, 
 shipping_state, version, effective_date, expiry_date) as    
select zip_code_sk, zip_code, city, state, version, effective_date, expiry_date    
  from zip_code_dim;

（4）事实表sales_order_fact上增加两个邮编外键列


alter table sales_order_fact 
  add column customer_zip_code_sk int,
  add column shipping_zip_code_sk int;

3. 初始装载邮编数据
（1）装载邮编维度表


insert into zip_code_dim
select row_number() over (order by t1.customer_zip_code), 
           customer_zip_code, 
           customer_city, 
           customer_state,
           1,'2021-06-01','2200-01-01' 
  from (select distinct customer_zip_code, customer_city, customer_state
          from customer_dim
         where customer_zip_code is not null
         union 
        select distinct shipping_zip_code, shipping_city, shipping_state 
          from customer_dim
         where shipping_zip_code is not null) t1;

初始数据是从客户维度表中来，这只是为了演示数据装载的过程。客户的邮编信息很可能覆盖不到所有邮编，所以更好的方法是装载一个完整的邮编信息表。由于客户地址和送货地址可能存在交叉的情况，因此使用union联合两个查询。注意这里不能使用union all，因为需要去除重复的数据。送货地址的三个字段是在7.1节中后加的，在此之前数据的送货地址为空，邮编维度表中不能含有NULL值，所以要加上where shipping_zip_code is not null过滤条件去除邮编信息为NULL的数据行。

（2）装载事实表


update sales_order_fact t1
   set customer_zip_code_sk = t2.customer_zip_code_sk, shipping_zip_code_sk = t2.shipping_zip_code_sk
  from (select t1.order_number, t2.customer_zip_code_sk, t3.shipping_zip_code_sk     
          from sales_order_fact t1  
          left join (select a.order_number order_number, c.customer_zip_code_sk customer_zip_code_sk    
                       from sales_order_fact a, customer_dim b, customer_zip_code_dim c    
                      where a.customer_sk = b.customer_sk    
                        and b.customer_zip_code = c.customer_zip_code) t2 
            on t1.order_number = t2.order_number  
          left join (select a.order_number order_number, c.shipping_zip_code_sk shipping_zip_code_sk    
                       from sales_order_fact a, customer_dim b, shipping_zip_code_dim c    
                      where a.customer_sk = b.customer_sk    
                        and b.shipping_zip_code = c.shipping_zip_code) t3 
            on t1.order_number = t3.order_number) t2
 where t1.order_number = t2.order_number;

更新邮编外键时，同时需要关联两个邮编角色维度视图，查询出两个代理键，装载到事实表中。注意老的事实表与新的邮编维度表是通过客户维度表关联起来的，所以在子查询中需要三表连接，然后用两个左外连接查询出所有原事实表数据，装载到新的增加了邮编维度代理键的事实表中。4. 修改客户维度表及其依赖它的对象


-- 级联删除邮编相关的6列
alter table customer_dim 
 drop column customer_zip_code cascade,
 drop column customer_city,
 drop column customer_state,
 drop column shipping_zip_code,
 drop column shipping_city,
 drop column shipping_state; 
 
-- 重建规则，去掉邮编相关的6列
create rule r_insert_customer as on insert to customer do also 
(insert into customer_dim (customer_number,customer_name,customer_street_address,version,effective_dt,expiry_dt,shipping_address
) 
 values (new.customer_number,new.customer_name,new.customer_street_address,1,now(),'2200-01-01',new.shipping_address
 ););
 
create rule r_update_customer as on update to customer do also 
(update customer_dim set expiry_dt=now() 
  where customer_number=new.customer_number and expiry_dt='2200-01-01' 
    and ( not (customer_street_address <=> new.customer_street_address) or not (shipping_address <=> new.shipping_address));
 
insert into customer_dim (customer_number,customer_name,customer_street_address,version,effective_dt,expiry_dt,shipping_address) 
select new.customer_number,new.customer_name,new.customer_street_address,version + 1,expiry_dt,'2200-01-01',new.shipping_address
  from customer_dim 
 where customer_number=new.customer_number 
   and (not (customer_street_address <=> new.customer_street_address) or not (shipping_address <=> new.shipping_address))
   and version=(select max(version) 
                  from customer_dim 
                 where customer_number=new.customer_number);
 
update customer_dim set customer_name=new.customer_name 
 where customer_number=new.customer_number and customer_name<>new.customer_name);
 
-- 建立PA维度视图
create view pa_customer_dim as
select distinct a.*              
  from customer_dim a,  
       sales_order_fact b,  
       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; 
 
-- 建立PA维度物化视图
create materialized view mv_pa_customer_dim as 
select distinct a.*              
  from customer_dim a,  
       sales_order_fact b,  
       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
distributed by (customer_sk);

因为customer_dim维度表上存在依赖它的r_insert_customer规则、r_update_customer规则、pa_customer_dim视图和mv_pa_customer_dim物化视图，需要级联删除，然后重建这些对象。州代码已经从客户维度表删除，被放到了新的邮编维度表中，而客户维度和邮编维度并没有直接关系，它们是通过事实表的客户代理键和邮编代理键产生联系，因此必须关联事实表、客户维度表、邮编维度表三个表才能取出PA子维度数据。

5. 重建装载事实表的规则


drop rule r_insert_sales_order on rds.sales_order;
create rule r_insert_sales_order as on insert to rds.sales_order do also 
(insert into sales_order_fact(order_number,customer_sk,product_sk,order_date_sk,request_delivery_date_sk,year_month,order_amount,order_quantity, sales_order_attribute_sk,customer_zip_code_sk,shipping_zip_code_sk) 
 select new.order_number, customer_sk, product_sk, d.date_sk, e.date_sk, to_char(new.order_date, 'YYYYMM')::int, new.order_amount, new.order_quantity, f.sales_order_attribute_sk, g.customer_zip_code_sk, h.shipping_zip_code_sk
   from customer_dim b, product_dim c, date_dim d, date_dim e, sales_order_attribute_dim f, customer_zip_code_dim g, shipping_zip_code_dim h, rds.customer i
  where new.customer_number = b.customer_number and b.expiry_dt = '2200-01-01'
    and new.product_code = c.product_code and c.expiry_dt = '2200-01-01'
    and date(new.order_date) = d.date
    and date(new.request_delivery_date) = e.date
    and new.verification_ind = f.verification_ind
    and new.credit_check_flag = f.credit_check_flag
    and new.new_customer_ind = f.new_customer_ind
    and new.web_order_flag = f.web_order_flag
    and new.customer_number = i.customer_number 
    and i.customer_zip_code = g.customer_zip_code and g.expiry_date = '2200-01-01'
    and i.shipping_zip_code = h.shipping_zip_code and h.expiry_date = '2200-01-01'
);

装载事实表数据时，除了关联两个邮编维度视图外，还要关联过渡区的rds.customer表。这是因为要取得邮编维度代理键，必须连接邮编代码字段，而邮编代码已经从客户维度表中删除，只有在源数据的客户表中保留。

6. 启动Canal Server、Canal Adapter


# 启动Canal Server，在构成Canal HA的126、127两台顺序执行
~/canal_113/deployer/bin/startup.sh
 
# 在126执行
~/canal_113/adapter/bin/startup.sh

7. 测试
按照以下步骤进行测试，代码从略。
（1）对源数据的客户邮编相关信息做一些修改。
（2）装载新的客户数据前，查询最后的客户和送货邮编，后面可以用改变后的信息和此查询的输出作对比。
（3）新增销售订单源数据。
（4）查询客户维度表、销售订单事实表和PA子维度表，确认数据已经正确装载。

7.8 分段维度

在客户维度中，最具有分析价值的属性就是各种分类，这些属性的变化范围比较大。对某个个体客户来说，可能的分类属性包括：性别、年龄、民族、职业、收入和状态，例如，新客户、活跃客户、不活跃客户、已流失客户等。在这些分类属性中，有一些能够定义成包含连续值的分段，例如年龄和收入这种数值型的属性，天然就可以分成连续的数值区间，而象状态这种描述性的属性，可能需要用户根据自己的实际业务仔细定义，通常定义的根据是某种可度量的数值。

组织还可能使用为其客户打分的方法刻画客户行为。分段维度模型通常以不同方式按照积分将客户分类，例如，基于他们的购买行为、支付行为、流失走向等。每个客户用所得的分数标记。

一个常用的客户评分及分析系统是考察客户行为的相关度（R）、频繁度（F）和强度（I），该方法被称为RFI方法。有时将强度替换为消费度（M），因此也被称为RFM度量。相关度是指客户上次购买或访问网站距今的天数。频繁度是指一段时间内客户购买或访问网站的次数，通常是指过去一年的情况。强度是指客户在某一固定时间周期中消费的总金额。在处理大型客户数据时，某个客户的行为可以按照如图7-6所示的RFI多维数据仓库建模。在此图中，每个维度形成一条数轴，某个轴的积分度量值从1~5，代表某个分组的实际值，三条数轴组合构成客户积分立方体，每个客户的积分都在这个立方体之中。

图7-6 RFI立方体

定义有意义的分组至关重要。应该由业务人员和数据仓库开发团队共同定义可能会利用的行为标识，更复杂的场景可能包含信用行为和回报情况，例如定义如下8个客户标识：

A：活跃客户，信誉良好，产品回报多
B：活跃客户，信誉良好，产品回报一般
C：最近的新客户，尚未建立信誉等级
D：偶尔出现的客户，信誉良好
E：偶尔出现的客户，信誉不好
F：以前的优秀客户，最近不常见
G：只逛不买的客户，几乎没有效益
H：其它客户

至此可以考察客户时间序列数据，并将某个客户关联到报表期间的最近分类中。例如，某个客户在最近10个考察期间的情况可以表示为：CCCDDAAABB。这一行为时间序列标记来自于固定周期度量过程，观察值是文本类型的，不能计算或求平均值，但是它们可以被查询。例如，可以发现在以前的第5个、第4个或第3个周期中获得A且在第2个或第1个周期中获得B的所有客户。通过这样的进展分析还可以发现那些可能失去的有价值的客户，进而用于提高产品回报率。

行为标记可能不会被当成普通事实存储，因为它虽然由事实表的度量所定义，但其本身不是度量值。行为标记的主要作用在于为前面描述的例子制定复杂的查询模式。推荐的处理行为标记的方法是为客户维度建立分段属性的时间序列。这样BI接口比较简单，因为列都在同一个表中，性能也较好，因为可以对它们建立时间戳索引。除了为每个行为标记时间周期建立不同的列，建立单一的包含多个连续行为标记的连接字符串，也是较好的一种方法，例如，CCCDDAAABB。该列支持通配符模糊搜索模式，例如，“D后紧跟着B”可以简单实现为“where flag like '%DB%'”。

下面以销售订单为例，说明分段维度的实现技术。分段维度包含连续的分段度量值。例如，年度销售订单分段维度可能包含有叫做“低”、“中”、“高”的三个档次，各档定义分别为消费额在0.01到3000、3000.01到6000.00、6000.01到99999999.99区间。如果一个客户的年度销售订单金额累计为1000，则被归为“低”档。分段维度可以存储多个分段集合。例如，可能有一个用于促销分析的分段集合，另一个用于市场细分，可能还有一个用于销售区域计划。分段一般由用户定义，而且很少能从源事务数据直接获得。

1. 年度销售订单星型模式
为了实现年度订单分段维度，我们需要两个新的星型模式，如图7-7所示。

图7-7 年度销售额分段维度

第一个星型模式由annual_sales_order_fact事实表、customer_dim维度表和year_dim维度表构成。年维度是新建的维度表，是日期维度的子集。年度销售额事实表存储客户一年的消费总额，数据从现有的销售订单事实表汇总而来。第二个星型模式由annual_customer_segment_fact事实表、annual_order_segement_dim维度表、customer_dim维度表和year_dim维度表构成。客户年度分段事实表中没有度量，只有来自三个相关维度表的代理键，因此它是一个无事实的事实表，存储的数据实际上就是前面所说的行为标记时间序列。下一篇会详细讨论无事实事实表技术。年度订单分段维度表用于存储分段的定义，在本例中，它只与年度分段事实表有关系。

如果多个分段的属性相同，可以将它们存储到单一维度表中，因为分段通常都有很小的基数。本例中annual_order_segment_dim表存储了“project”和“grid”两种分段集合，它们都是按照客户的年度销售订单金额将其分类。分段维度按消费金额的定义如表10-4所示，project 分六段，grid分三段

分段类别	分段名称	开始值	结束值
Project	bottom	0.01	2500.00
Project	low	2500.01	3000.00
Project	mid-low	3000.01	4000.00
Project	mid	4000.00	5500.00
Project	mid-high	5500.01	6500.00
Project	top	6500.01	99999999.99
Grid	low	0.01	3000.00
Grid	mid	3000.01	6000.00
Grid	high	6000.01	99999999.99

表7-3 客户年度消费分段维度定义

每一分段有一个开始值和一个结束值。分段的粒度就是本段和下段之间的间隙。粒度必须是度量的最小可能值，在销售订单示例中，金额的最小值是0.01。最后一个分段的结束值是销售订单金额可能的最大值。下面的脚本用于建立分段维度数据仓库表，包括年份维度表、分段维度表、年度销售事实表和年度客户消费分段事实表，只有分段维度表需要SCD处理。因为都是新建表，不涉及对已有表的DDL修改，所以不用事先停止Canal服务。


set search_path=tds;
   
create table annual_order_segment_dim (    
    segment_sk int primary key,    
    segment_name varchar(30),    
    band_name varchar(50),    
    band_start_amount decimal(10,2),  
    band_end_amount decimal(10,2),  
    version int,  
    effective_date date,    
    expiry_date date );   
  
insert into annual_order_segment_dim values  
(1, 'project', 'bottom', 0.01, 2500.00, 1, '2021-06-01', '2200-01-01'),
(2, 'project', 'low', 2500.01, 3000.00, 1, '2021-06-01', '2200-01-01'),  
(3, 'project', 'mid-low', 3000.01, 4000.00, 1, '2021-06-01', '2200-01-01'),
(4, 'project', 'mid', 4000.01, 5500.00, 1, '2021-06-01', '2200-01-01'),
(5, 'project', 'mid_high', 5500.01, 6500.00, 1, '2021-06-01', '2200-01-01'),
(6, 'project', 'top', 6500.01, 99999999.99, 1, '2021-06-01', '2200-01-01'),
(7, 'grid', 'low', 0.01, 3000, 1, '2021-06-01', '2200-01-01'),
(8, 'grid', 'med', 3000.01, 6000.00, 1, '2021-06-01', '2200-01-01'),
(9, 'grid', 'high', 6000.01, 99999999.99, 1, '2021-06-01', '2200-01-01');    
  
create table year_dim (    
    year_sk int primary key,    
    year int);    
    
create table annual_sales_order_fact (    
    customer_sk int,    
    year_sk int,    
    annual_order_amount decimal(10, 2),
    primary key (customer_sk, year_sk));   
    
create table annual_customer_segment_fact (    
    segment_sk int,    
    customer_sk int,    
    year_sk int,
    primary key (segment_sk, customer_sk, year_sk));

2. 初始装载
执行下面的脚本初始装载分段相关数据。


insert into year_dim    
select row_number() over (order by t1.year), year  
  from (select distinct year from order_date_dim) t1;   
  
insert into annual_sales_order_fact    
select a.customer_sk,   
       year_sk,   
       sum(order_amount)    
  from sales_order_fact a,  
       year_dim c,  
       order_date_dim d    
 where a.order_date_sk = d.order_date_sk    
   and c.year = d.year    
   and d.year < 2022    
 group by a.customer_sk, c.year_sk;    
    
insert into annual_customer_segment_fact    
select d.segment_sk,   
       a.customer_sk,   
       a.year_sk    
  from annual_sales_order_fact a,   
       annual_order_segment_dim d    
 where annual_order_amount >= band_start_amount    
   and annual_order_amount <= band_end_amount;

初始装载脚本将订单日期角色扮演维度表（date_dim表的一个视图）里的去重年份数据导入年份维度表，将销售订单事实表中按年客户和分组求和的汇总金额数据导入年度销售事实表。因为装载过程不能导入当年的数据，所以使用year < 2022过滤条件作为演示。这里是按客户代理键customer_sk分组求和来判断分段，实际情况可能是以customer_number进行分组的，因为无论客户的SCD属性如何变化，一般还是认为是一个客户。将年度销售事实表里与分段维度表关联，把年份、客户和分段三个维度的代理键插入年度客户消费分段事实表。注意，数据装载过程中并没有引用客户维度表，因为客户代理键可以直接从销售订单事实表得到。分段定义中，每个分段结束值与下一分段的开始值是连续的，并且分段之间不存在数据重叠，所以装载分段事实表时，订单金额判断条件两端都使用闭区间。

执行初始装载脚本后，使用下面的语句查询客户分段事实表，确认装载的数据是正确的。


select a.customer_sk csk,  
        a.year_sk ysk,  
        annual_order_amount amt,  
        segment_name sn,  
        band_name bn  
  from annual_customer_segment_fact a,  
        annual_order_segment_dim b,  
        year_dim c,  
        annual_sales_order_fact d  
 where a.segment_sk = b.segment_sk  
   and a.year_sk = c.year_sk  
   and a.customer_sk = d.customer_sk  
   and a.year_sk = d.year_sk  
 order by csk, ysk, sn, bn;

3. 定期装载
年度销售额是一年的汇总数据，不存在实时装载问题，只需每年调度执行下面的定期装载脚本，装载前一年的销售数据。除了无需装载年份表以外，定期装载与初始装载类似，年度销售事实表里的数据被导入分段事实表。


insert into annual_sales_order_fact    
select a.customer_sk,   
       year_sk,   
       sum(order_amount)    
  from sales_order_fact a,  
       year_dim c,  
       order_date_dim d    
 where a.order_date_sk = d.order_date_sk    
   and c.year = d.year    
   and d.year = extract(year from current_date) - 1    
 group by a.customer_sk, c.year_sk;    
    
insert into annual_customer_segment_fact    
select d.segment_sk,   
        a.customer_sk,   
        c.year_sk    
  from annual_sales_order_fact a,   
        year_dim c,  
        annual_order_segment_dim d    
 where a.year_sk = c.year_sk  
   and c.year = extract(year from current_date) - 1  
   and annual_order_amount >= band_start_amount    
   and annual_order_amount <= band_end_amount;

小结

修改数据仓库模式时，要注意空值的处理，必要时使用自定义的<=>比较操作符代替等号。
子维度通常有包含属性子集的子维度和包含行子集的子维度两种，常用视图实现。
单个物理维度可以被事实表多次引用，每个引用连接逻辑上存在差异的角色扮演维度。视图和表别名是实现角色扮演维度的两种常用方法。
处理层次维度时，经常使用grouping、grouping sets等函数或语句。
除了业务主键外没有其它其他内容的维度表通常是退化维度。将业务主键作为一个属性加入到事实表中是处理退化维度的适当方式。
杂项维度是一种包含的数据具有很少可能值的维度。有时与其为每个标志或属性定义不同的维度，不如建立单独的、将不同维度合并到一起的杂项维度表。
如果几个相关维度的基数都很小，或者具有多个公共属性时，可以考虑将它们进行维度合并。
分段维度的定义中包含连续的分段度量值，通常用作客户维度的行为标记时间序列，分析客户行为。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小惠珠哦/article/detail/826440