原创

维度模型数据仓库(二十一) —— 分段维度

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/50039763
(五)进阶技术
        16. 分段维度
        本篇说明分段维度的实现技术。分段维度包含连续值的分段。例如,年度销售订单分段维度可能包含有叫做“低”、“中”、“高”的三档;各档定义分别为0.01到15000、15000.01到30000.00、30000.01到99999999.99。如果一个客户的年度销售订单金额为10000,则被归为“低”档。
分段维度可以存储多个分段集合。例如,可能有一个用于促销分析的分段集合,另一个用于市场细分,可能还有一个用于销售区域计划。分段一般由用户定义,而且很少能从交易源数据直接获得。本篇要使用(五)进阶技术 10. 多重星型模式的开发经验实现分段维度。

        年度销售订单星型模式
        本节说明如何实现一个年度订单分段维度。你需要两个新的星型模式,如图(五)- 16-1所示。星型模式的事实表使用(关联到)已有的customer_dim和一个新的year_dim表。年维度是日期维度的子集。annual_customer_segment_fact是唯一用到annual_order_segment_dim表的表。annual_order_segement_dim是分段维度。
图(五)- 16-1

        annual_order_segment_dim表存储多个分段集合。在下面的例子里将两个分段集合“PROJECT ALPHA”和“Grid”导入annual_order_segment_dim表。这两种分段集合都是按照用户的年度销售订单金额将其分类。PROJECT ALPHA分六段,Grid分三段。表(五)- 16-1显示了这个分段的例子。

Segment Name

Band Name

Start Value

End Value

 PROJECT ALPHA

 Bottom

 0.01

 2500.00

 PROJECT ALPHA

 Low

 2500.01

 3000.00

 PROJECT ALPHA

 Mid-low

 3000.01

 4000.00

 PROJECT ALPHA

 Mid

 4000.01

 5500.00

 PROJECT ALPHA

 Mid-high

 5500.01

 6500.00

 PROJECT ALPHA

 Top

 6500.01

 99999999.99

 Grid

 LOW

 0.01

 3000.00

 Grid

 MED

 3000.01

 6000.00

 Grid

 HIGH

 6000.01

 99999999.99

表(五)- 16-1

        每一分段有一个开始值和一个结束值。 分段的粒度就是本段和下段之间的间隙。粒度必须是度量的最小可能值,在销售订单金额的示例中是0.01。最后一个分段的结束值是销售订单金额可能的最大值。
        清单(五)-16-1里的脚本用于建立分段维度数据仓库模式。
USE dw;
CREATE TABLE annual_order_segment_dim (
    segment_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    segment_name VARCHAR(30),
    band_name VARCHAR(50),
    band_start_amount DEC(10 , 2 ),
    band_end_amount DEC(10 , 2 ),
    effective_date DATE,
    expiry_date DATE
);

INSERT INTO annual_order_segment_dim VALUES
  (NULL, 'PROJECT ALPHA', 'Bottom', 0.01, 2500.00, '1900-01-01',
       '2200-01-01')
, (NULL, 'PROJECT ALPHA', 'Low', 2500.01, 3000.00, '1900-01-01',
       '2200-01-01')
, (NULL, 'PROJECT ALPHA', 'Mid-Low', 3000.01, 4000.00, '1900-01-01',
       '2200-01-01')
, (NULL, 'PROJECT ALPHA', 'Mid', 4000.01, 5500.00, '1900-01-01',
       '2200-01-01')
, (NULL, 'PROJECT ALPHA', 'Mid_High', 5500.01, 6500.00, '1900-01-01', 
       '2200-01-01')
, (NULL, 'PROJECT ALPHA', 'Top', 6500.01, 99999999.99, ' 1900-01-01',
       '2200-01-01')
, (NULL, 'Grid', 'LOW', 0.01, 3000, '1900-01-01', '2200-01-01')
, (NULL, 'Grid', 'MED', 3000.01, 6000.00, ' 1900-01-01', '2200-01-01')
, (NULL, 'Grid', 'HIGH', 6000.01, 99999999.99, '1900-01-01', '2200-01-01');

commit;

CREATE TABLE year_dim (
    year_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    year INT(4),
    effective_date DATE,
    expiry_date DATE
);

CREATE TABLE annual_sales_order_fact (
    customer_sk INT,
    year_sk INT,
    annual_order_amount DEC(10 , 2 )
);

alter table annual_sales_order_fact 
add foreign key (customer_sk) references customer_dim(customer_sk),
add foreign key (year_sk) references year_dim(year_sk);

CREATE TABLE annual_customer_segment_fact (
    segment_sk INT,
    customer_sk INT,
    year_sk INT
);

alter table annual_customer_segment_fact
add foreign key (segment_sk) references annual_order_segment_dim(segment_sk),
add foreign key (customer_sk) references customer_dim(customer_sk),
add foreign key (year_sk) references year_dim(year_sk);
清单(五)-16-1

        初始装载
        本节说明初始装载并进行测试。清单(五)-16-2里的初始装载脚本将order_date维度表(date_dim表的一个视图)里的数据导入year_dim表,将sales_order_fact表里的数据导入annual_sales_order_fact表,将annual_sales_order_fact表里的数据导入annual_customer_segment_fact表。此脚本装载所有历史数据。
use dw;

INSERT INTO year_dim
SELECT DISTINCT
  NULL
, year
, effective_date
, expiry_date
FROM order_date_dim;

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 < YEAR(CURRENT_DATE)
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;

commit;
清单(五)-16-2

        为了测试初始装载脚本,先设置系统日期设置为2014年的任何日期以装载2013年的数据。在后面的定期测试小结里将导入2014年的销售订单。

        执行完清单(五)-16-2里的脚本,查询annual_customer_segment_fact表确认初始装载是成功的。查询语句和结果如下所示。
mysql> 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 a.customer_sk , year , segment_name , band_name;
+------+------+---------+---------------+----------+
| csk  | ysk  | amt     | sn            | bn       |
+------+------+---------+---------------+----------+
|    1 |   14 | 8000.00 | Grid          | HIGH     |
|    1 |   14 | 8000.00 | PROJECT ALPHA | Top      |
|    3 |   14 | 4000.00 | Grid          | MED      |
|    3 |   14 | 4000.00 | PROJECT ALPHA | Mid-Low  |
|    4 |   14 | 4000.00 | Grid          | MED      |
|    4 |   14 | 4000.00 | PROJECT ALPHA | Mid-Low  |
|    5 |   14 | 6000.00 | Grid          | MED      |
|    5 |   14 | 6000.00 | PROJECT ALPHA | Mid_High |
|    6 |   14 | 6000.00 | Grid          | MED      |
|    6 |   14 | 6000.00 | PROJECT ALPHA | Mid_High |
|    7 |   14 | 8000.00 | Grid          | HIGH     |
|    7 |   14 | 8000.00 | PROJECT ALPHA | Top      |
+------+------+---------+---------------+----------+
12 rows in set (0.01 sec)

        查询结果表明每个在2013年有订单的客户都被赋予了两个分段集合中的值。可以验证年度销售金额分段赋值是否正确。

        定期装载
        本节说明定期装载脚本和如何测试它。除了无需装载year_dim表以外,定期装载与初始装载类似。annual_sales_order_fact表里的数据被导入annual_customer_segment_fact表。

        每年调度执行清单(五)-16-3里的定期装载,此脚本装载前一年的销售数据。
use dw;

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 c.year = YEAR(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 = YEAR(CURRENT_DATE) - 1
AND annual_order_amount >= band_start_amount
AND annual_order_amount <= band_end_amount;

commit;
清单(五)-16-3

        使用Kettle转换进行定期装载的步骤如图(五)- 16-2到图(五)- 16-14所示。
图(五)- 16-2

图(五)- 16-3

图(五)- 16-4

图(五)- 16-5

图(五)- 16-6

图(五)- 16-7

图(五)- 16-8

图(五)- 16-9

图(五)- 16-10

图(五)- 16-11

图(五)- 16-12

图(五)- 16-13

图(五)- 16-14

        测试

        为了测试定期,设置系统日期为2015年的日期并执行清单(五)-16-3里的脚本会对应的Kettle转换。
        查询customer_order_segment_fact表确认定期装载是否正确。查询语句和结果如下所示。
mysql> 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 a.customer_sk , year , segment_name , band_name;
+------+------+---------+---------------+----------+
| csk  | ysk  | amt     | sn            | bn       |
+------+------+---------+---------------+----------+
|    1 |   14 | 8000.00 | Grid          | HIGH     |
|    1 |   14 | 8000.00 | PROJECT ALPHA | Top      |
|    1 |   15 | 4000.00 | Grid          | MED      |
|    1 |   15 | 4000.00 | PROJECT ALPHA | Mid-Low  |
|    2 |   15 | 5500.00 | Grid          | MED      |
|    2 |   15 | 5500.00 | PROJECT ALPHA | Mid      |
|    3 |   14 | 4000.00 | Grid          | MED      |
|    3 |   14 | 4000.00 | PROJECT ALPHA | Mid-Low  |
|    3 |   15 | 2000.00 | Grid          | LOW      |
|    3 |   15 | 2000.00 | PROJECT ALPHA | Bottom   |
|    4 |   14 | 4000.00 | Grid          | MED      |
|    4 |   14 | 4000.00 | PROJECT ALPHA | Mid-Low  |
|    4 |   15 | 3000.00 | Grid          | LOW      |
|    4 |   15 | 3000.00 | PROJECT ALPHA | Low      |
|    5 |   14 | 6000.00 | Grid          | MED      |
|    5 |   14 | 6000.00 | PROJECT ALPHA | Mid_High |
|    5 |   15 | 2500.00 | Grid          | LOW      |
|    5 |   15 | 2500.00 | PROJECT ALPHA | Bottom   |
|    6 |   14 | 6000.00 | Grid          | MED      |
|    6 |   14 | 6000.00 | PROJECT ALPHA | Mid_High |
|    6 |   15 | 3000.00 | Grid          | LOW      |
|    6 |   15 | 3000.00 | PROJECT ALPHA | Low      |
|    7 |   14 | 8000.00 | Grid          | HIGH     |
|    7 |   14 | 8000.00 | PROJECT ALPHA | Top      |
|    7 |   15 | 3500.00 | Grid          | MED      |
|    7 |   15 | 3500.00 | PROJECT ALPHA | Mid-Low  |
+------+------+---------+---------------+----------+
26 rows in set (0.00 sec)
文章最后发布于: 2015-11-25 17:31:43
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览