维度模型数据仓库（九） —— 角色扮演维度

用户1148526

发布于 2022-12-02 14:12:35

4920

发布于 2022-12-02 14:12:35

文章被收录于专栏：Hadoop数据仓库

（五）进阶技术 4. 角色扮演维度当一个事实表多次引用一个维度表时会用到角色扮演维度。例如，一个销售订单有一个是订单日期，还有一个交货日期，这时就需要引用日期维度表两次。本篇将说明两类角色扮演维度的实现，分别是表别名和数据库视图。这两种都使用了MySQL的功能。表别名是在SQL语句里引用维度表多次，每次引用都赋予维度表一个别名。而数据库视图，则是按照事实表需要引用维度表的次数，建立相同数量的视图。修改数据库模式使用清单（五）-4-1里的SQL脚本修改数据库模式。分别给数据仓库里的事实表sales_order_fact和源数据库中订单销售表sales_order增加request_delivery_date_sk和request_delivery_date列。图（五）- 4-1 显示了修改后的模式。

USE dw;
ALTER TABLE sales_order_fact ADD request_delivery_date_sk INT AFTER order_date_sk ;

USE source;
ALTER TABLE sales_order ADD request_delivery_date DATE AFTER order_date ;

清单（五）-4-1

图（五）- 4-1

由于表sales_order_fact的结构做了修改，所以需要更新该表的定期装载脚本。新的脚本如清单（五）-4-2所示。

USE dw;

-- 设置SCD的截止时间和生效时间
SET @pre_date = SUBDATE(CURRENT_DATE,1) ;

-- 设置CDC的上限时间
UPDATE cdc_time SET current_load = CURRENT_DATE ;

-- 装载客户维度
TRUNCATE TABLE customer_stg;
INSERT INTO customer_stg
SELECT 
  customer_number
, customer_name
, customer_street_address
, customer_zip_code
, customer_city
, customer_state
, shipping_address
, shipping_zip_code
, shipping_city
, shipping_state
FROM source.customer ;
/* 在所有地址列上 SCD2                           */
/* 置过期                          */
UPDATE customer_dim a,
    customer_stg b 
SET 
    expiry_date = @pre_date
WHERE
    a.customer_number = b.customer_number
        AND (a.customer_street_address <> b.customer_street_address
        OR a.customer_city <> b.customer_city
        OR a.customer_zip_code <> b.customer_zip_code
        OR a.customer_state <> b.customer_state
        OR a.shipping_address <> b.shipping_address
        OR a.shipping_city <> b.shipping_city
        OR a.shipping_zip_code <> b.shipping_zip_code
        OR a.shipping_state <> b.shipping_state
        OR a.shipping_address IS NULL
        OR a.shipping_city IS NULL
        OR a.shipping_zip_code IS NULL
        OR a.shipping_state IS NULL)
        AND expiry_date = '2200-01-01';
/* 加新行                          */
INSERT INTO customer_dim
SELECT
  NULL
, b.customer_number
, b.customer_name
, b.customer_street_address
, b.customer_zip_code
, b.customer_city
, b.customer_state
, b.shipping_address
, b.shipping_zip_code
, b.shipping_city
, b.shipping_state
, a.version + 1
, @pre_date
, '2200-01-01'
FROM
  customer_dim a
, customer_stg b
WHERE
    a.customer_number = b.customer_number
AND ( a.customer_street_address <> b.customer_street_address
     OR a.customer_city <> b.customer_city
     OR a.customer_zip_code <> b.customer_zip_code
     OR a.customer_state <> b.customer_state
     OR a.shipping_address <> b.shipping_address
     OR a.shipping_city <> b.shipping_city
     OR a.shipping_zip_code <> b.shipping_zip_code
     OR a.shipping_state <> b.shipping_state
     OR a.shipping_address IS NULL
     OR a.shipping_city IS NULL
     OR a.shipping_zip_code IS NULL
     OR a.shipping_state IS NULL)
AND EXISTS(
SELECT *
FROM customer_dim x
WHERE
    b.customer_number=x.customer_number
AND a.expiry_date = @pre_date )
AND NOT EXISTS (
SELECT *
FROM customer_dim y
WHERE
    b.customer_number = y.customer_number
AND y.expiry_date = '2200-01-01') ;
/* 在 customer_name 列上 SCD1                                             */
UPDATE customer_dim a, customer_stg b
SET a.customer_name = b.customer_name
WHERE a.customer_number = b.customer_number
      AND a.customer_name <> b.customer_name ;
/* 新增的客户                                                   */
INSERT INTO customer_dim
SELECT
  NULL
, customer_number
, customer_name
, customer_street_address
, customer_zip_code
, customer_city
, customer_state
, shipping_address
, shipping_zip_code
, shipping_city
, shipping_state
, 1
, @pre_date
,'2200-01-01'
FROM customer_stg
WHERE customer_number NOT IN(
SELECT y.customer_number
FROM customer_dim x, customer_stg y
WHERE x.customer_number = y.customer_number) ;

/* 重建PA客户维度                               */
TRUNCATE 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
, shipping_address
, shipping_zip_code
, shipping_city
, shipping_state
, version
, effective_date
, expiry_date
FROM customer_dim
WHERE customer_state = 'PA' ;

/* 装载产品维度                                           */
TRUNCATE TABLE product_stg ;
INSERT INTO product_stg
SELECT 
  product_code
, product_name
, product_category
FROM source.product ;
/* 在 product_name 和 product_category 列上 SCD2                                    */
/* 置过期                                 */
UPDATE
  product_dim a
, product_stg b
SET
  expiry_date = @pre_date
WHERE
    a.product_code = b.product_code
AND (   a.product_name <> b.product_name
     OR a.product_category <> b.product_category)
AND expiry_date = '2200-01-01';
/* 加新行                                */
INSERT INTO product_dim
SELECT
  NULL
, b.product_code
, b.product_name
, b.product_category
, a.version + 1
, @pre_date
,'2200-01-01'
FROM
  product_dim a
, product_stg b
WHERE
    a.product_code = b.product_code
AND (   a.product_name <> b.product_name
     OR a.product_category <> b.product_category)
AND EXISTS(
SELECT *
FROM product_dim x
WHERE     b.product_code = x.product_code
      AND a.expiry_date = @pre_date)
AND NOT EXISTS (
SELECT *
FROM product_dim y
WHERE     b.product_code = y.product_code
      AND y.expiry_date = '2200-01-01') ;
/* 新增的产品                                                    */
INSERT INTO product_dim
SELECT
  NULL
, product_code
, product_name
, product_category
, 1
, @pre_date
, '2200-01-01'
FROM product_stg
WHERE product_code NOT IN(
SELECT y.product_code
FROM product_dim x, product_stg y
WHERE x.product_code = y.product_code) ;

-- 装载订单维度，新增前一天的订单号
INSERT INTO order_dim (
  order_number
, effective_date
, expiry_date)
SELECT
  order_number
, order_date
, '2200-01-01'
FROM source.sales_order, cdc_time
WHERE entry_date >= last_load AND entry_date < current_load ;

-- 装载事实表，新增前一天的订单
INSERT INTO sales_order_fact
SELECT
  order_sk
, customer_sk
, product_sk
, e.date_sk
, f.date_sk
, order_amount
, order_quantity
FROM
  source.sales_order a
, order_dim b
, customer_dim c
, product_dim d
, date_dim e
, date_dim f
, cdc_time g
WHERE
    a.order_number = b.order_number
AND 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 a.order_date = e.date
AND a.request_delivery_date = f.date
AND a.entry_date >= g.last_load AND a.entry_date < g.current_load ;

-- 更新时间戳表的last_load字段
UPDATE cdc_time SET last_load = current_load ;

COMMIT ;

清单（五）-4-2

Kettle需要修改“装载事实表”步骤，如图（五）- 4-2到（五）- 4-6所示。

图（五）- 4-2

图（五）- 4-3

图（五）- 4-4

图（五）- 4-5

图（五）- 4-6

为测试修改后的定期装载，先使用下面的命令增加三个带有交货日期的销售订单，并设置系统日期为2015年3月5日，然后执行清单（五）-4-2里的SQL脚本或执行修改后的Kettle步骤进行定期装载。 USE source; INSERT INTO sales_order VALUES (47, 1, 1, '2015-03-04', '2015-03-30', '2015-03-04', 7500, 75) , (48, 2, 2, '2015-03-04', '2015-03-30', '2015-03-04', 1000, 10) , (49, 3, 3, '2015-03-04', '2015-03-30', '2015-03-04', 1000, 10) ; COMMIT ; 如果查询sales_order_fact表，会看到三个新的销售订单具有request_delivery_date_sk值，而老订单则没有。验证结果应该如下所示： mysql> select a.order_sk, request_delivery_date_sk -> from sales_order_fact a, date_dim b -> where a.order_date_sk = b.date_sk ; +----------+--------------------------+ | order_sk | request_delivery_date_sk | +----------+--------------------------+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | | 11 | NULL | | 12 | NULL | | 13 | NULL | | 14 | NULL | | 15 | NULL | | 16 | NULL | | 17 | NULL | | 18 | NULL | | 19 | NULL | | 20 | NULL | | 21 | NULL | | 22 | NULL | | 23 | NULL | | 24 | NULL | | 25 | NULL | | 26 | NULL | | 27 | NULL | | 28 | NULL | | 29 | NULL | | 30 | NULL | | 31 | NULL | | 32 | NULL | | 33 | NULL | | 34 | NULL | | 35 | NULL | | 36 | NULL | | 37 | NULL | | 38 | NULL | | 39 | NULL | | 40 | NULL | | 41 | NULL | | 42 | NULL | | 43 | NULL | | 44 | NULL | | 45 | 5568 | | 46 | 5568 | | 47 | 5568 | +----------+--------------------------+ 47 rows in set (0.00 sec) mysql> select date_sk, date from date_dim where date_sk = 5568; +---------+------------+ | date_sk | date | +---------+------------+ | 5568 | 2015-03-30 | +---------+------------+ 1 row in set (0.00 sec) 现在已经修改了模式和定期装载，可以使用表别名和数据库视图这两种类型的角色扮演维度。表别名的实现清单（五）-4-3里的查询是一个表别名的例子。脚本里的查询实际上使用了日期维度表两次，一次是订单日期（别名是order_date_dim），一次是交货日期（别名是request_delivery_date_dim）。

USE dw;

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;

清单（五）-4-3

通过建立两个数据库视图来实现第二类日期维度的角色扮演，每个视图对应一种日期。可以将这些视图作为维度表来查询。使用清单（五）-4-4里的脚本建立视图。

USE dw;

CREATE VIEW order_date_dim (order_date_sk , order_date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date) AS
    SELECT 
        date_sk,
        date,
        month_name,
        month,
        quarter,
        year,
        promo_ind,
        effective_date,
        expiry_date
    FROM
        date_dim;

CREATE VIEW request_delivery_date_dim (request_delivery_date_sk , request_delivery_date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date) AS
    SELECT 
        date_sk,
        date,
        month_name,
        month,
        quarter,
        year,
        promo_ind,
        effective_date,
        expiry_date
    FROM
        date_dim ;

清单（五）-4-4

清单（五）-4-5里的查询使用两个日期视图实现与上一个使用表别名查询的相同功能。

USE dw;

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;

清单（五）-4-5

两个查询的结果相同，如下所示： +------------+-----------------------+-------------------+----------+ | order_date | request_delivery_date | SUM(order_amount) | COUNT(*) | +------------+-----------------------+-------------------+----------+ | 2015-03-04 | 2015-03-30 | 9500.00 | 3 | +------------+-----------------------+-------------------+----------+ 1 row in set (0.00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2015-11-08，如有侵权请联系 cloudcommunity@tencent.com 删除

http