维度模型数据仓库（十五） —— 多重星型模式

（五）进阶技术         10. 多重星型模式         从（五）进阶技术1.  “增加列”开始，已经通过增加列和表扩展了数据仓库，在（五）进阶技术5. “快照”里增加了第二个事实表，month_end_sales_order_fact表。这之后数据仓库模式就有了两个事实表（第一个是在开始建立数据仓库时创建的sales_order_fact表）。有了这两个事实表的数据仓库就是一个正式的双星型模式。         本篇将在现有的维度数据仓库上增加一个新的星型结构。与现有的与销售关联的星型结构不同，新的星型结构关注的是产品业务领域。新的星型结构有一个事实表和一个维度表，用于存储数据仓库中的产品数据。         一个新的星型模式         图（五）- 10-1 显示了扩展后的数据仓库模式。模式中有三个星型结构。sales_order_fact表是第一个星型结构的事实表，与其相关的维度表是customer_dim、product_dim、date_dim和sales_order_attribute_dim表。month_end_sales_order_fact表是第二个星型结构的事实表。product_dim和month_dim是其对应的维度表。第一个和第二个星型结构共享product_dim维度表。第二个星型结构的事实表和月份维度数据分别来自于第一个星型结构的事实表和date_dim维度表。它们不从源数据获得数据。第三个星型模式的事实表是新建的production_fact表。它的维度除了存储在已有的date_dim和product_dim表，还有一个新的factory_dim表。第三个星型结构的数据来自源数据。

图（五）- 10-1

        执行清单（五）- 10-1里的脚本建立第三个星型模式中的新表和对应的源数据表。

USE dw;
CREATE TABLE factory_dim (
    factory_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    factory_code INT,
    factory_name VARCHAR(30),
    factory_street_address VARCHAR(50),
    factory_zip_code INT(5),
    factory_city VARCHAR(30),
    factory_state VARCHAR(2),
    version int,
    effective_date DATE,
    expiry_date DATE
);

CREATE TABLE production_fact (
  product_sk INT
, production_date_sk INT
, factory_sk INT
, production_quantity INT
);

ALTER TABLE production_fact ADD FOREIGN KEY (factory_sk) 
REFERENCES factory_dim(factory_sk)  ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE production_fact ADD FOREIGN KEY (product_sk) 
REFERENCES product_dim(product_sk)  ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE production_fact ADD FOREIGN KEY (production_date_sk) 
REFERENCES date_dim(date_sk)  ON DELETE CASCADE ON UPDATE CASCADE ;

USE source;
CREATE TABLE factory_master (
    factory_code INT,
    factory_name CHAR(30),
    factory_street_address CHAR(50),
    factory_zip_code INT(5),
    factory_city CHAR(30),
    factory_state CHAR(2)
);

清单（五）- 10-1

        向新的星型结构表中导入数据         假设factory_dim表存储工厂信息，并且从称为factory_master的MySQL表获得数据。可以使用清单（五）- 10-2里的脚本初始装载工厂数据。

USE dw;
INSERT INTO factory_dim
SELECT
  NULL
, factory_code
, factory_name
, factory_street_address
, factory_zip_code
, factory_city
, factory_state
, 1
, CURRENT_DATE
, '2200-01-01'
FROM source.factory_master;
COMMIT;

清单（五）- 10-2

        使用Kettle转换初始装载工厂数据只需要一个表输入和一个表输出步骤即可，如图（五）- 10-2到图（五）- 10-4所示。

图（五）- 10-2

图（五）- 10-3

图（五）- 10-4

        工厂的信息很少改变，所以可能希望在一个CSV文件里提供任何关于工厂的最新信息。在/root/data-integration/factory.csv文件中有一些示例工厂信息。 就像其它CSV源文件，需要一个过渡表装载factory.csv文件。执行清单（五）- 10-3里的脚本创建过渡表。

USE dw;
CREATE TABLE factory_stg (
    factory_code INT,
    factory_name VARCHAR(30),
    factory_street_address VARCHAR(50),
    factory_zip_code INT(5),
    factory_city VARCHAR(30),
    factory_state VARCHAR(2)
);

清单（五）- 10-3

        第三个星型结构中的production_fact事实表，从源数据库的daily_production表获取数据。执行清单（五）- 10-4里的脚本创建这个表。

USE source;
CREATE TABLE daily_production (
    product_code INT,
    production_date DATE,
    factory_code INT,
    production_quantity INT
);

清单（五）- 10-4

        可以使用清单（五）- 10-5里的脚本定期装载工厂维度表和产品事实表。注意对factory_dim表的所有列都应用SCD1。该脚本每天执行，装载前一天的产品数据。         说明：脚本里没有使用cdc_time表，是出于简化测试的目的。实际生产环境每天定期装载应该共用一个调度，也即应该把清单（五）- 10-5里的脚本并入每天定期装载脚本中，并且针对使用cdc_time表做相应的修改。

USE dw;

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

TRUNCATE factory_stg;
LOAD DATA INFILE '/root/data-integration/factory.csv'
INTO TABLE factory_stg
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ""
LINES TERMINATED BY '\n'
IGNORE 1 LINES
( factory_code
, factory_name
, factory_street_address
, factory_zip_code
, factory_city
, factory_state );

/* SCD1                                                         */
UPDATE
  factory_dim a
, factory_stg b
SET
  a.factory_name = b.factory_name
, a.factory_street_address = b.factory_street_address
, a.factory_zip_code = b.factory_zip_code
, a.factory_city = b.factory_city
, a.factory_state = b.factory_state
WHERE a.factory_code = b.factory_code;

/* add new factory                                              */
INSERT INTO factory_dim
SELECT
  NULL
, factory_code
, factory_name
, factory_street_address
, factory_zip_code
, factory_city
, factory_state
, 1 
, @pre_date
, '2200-01-01'
FROM factory_stg
WHERE factory_code NOT IN (
SELECT y.factory_code
FROM factory_dim x, factory_stg y
WHERE x.factory_code = y.factory_code );

INSERT INTO production_fact
SELECT
  b.product_sk
, c.date_sk
, d.factory_sk
, production_quantity
FROM
  source.daily_production a
, product_dim b
, date_dim c
, factory_dim d
WHERE
    production_date = @pre_date
AND a.product_code = b.product_code
AND a.production_date >= b.effective_date
AND a.production_date <= b.expiry_date
AND a.production_date = c.date
AND a.factory_code = d.factory_code ;

COMMIT ;

清单（五）- 10-5

        Kettle定期装载工厂维度表和产品事实表如图（五）- 10-5到图（五）- 10-22所示。

图（五）- 10-5

图（五）- 10-6

图（五）- 10-7

图（五）- 10-8

图（五）- 10-9

图（五）- 10-10

图（五）- 10-11

图（五）- 10-12

图（五）- 10-13

图（五）- 10-14

图（五）- 10-15

图（五）- 10-16

图（五）- 10-17

图（五）- 10-18

图（五）- 10-19

图（五）- 10-20

图（五）- 10-21

图（五）- 10-22

        测试         到目前为止已经讨论了第三个星型结构里的所有表，现在做一些测试。首先需要一些工厂信息。执行清单（五）- 10-6里的脚本向源数据库的factory_master表中装载四个工厂信息。运行完清单（五）- 10-5里的脚本以后，需要把系统日期设置成任意晚于上一篇“杂项维度”设置的日期。这里设置系统日期为2015年3月18日。之后，执行清单（五）- 10-2里的脚本或对应的Kettle初始装载转换向factory_dim表装载factory_master表里的四个工厂信息。

USE source;
INSERT INTO factory_master VALUES
  ( 1, 'First Factory', '11111 Lichtman St.', 17050,
       'Mechanicsburg', 'PA' )
, ( 2, 'Second Factory', '22222 Stobosky Ave.', 17055, 'Pittsburgh',
       'PA' )
, ( 3, 'Third Factory', '33333 Fritze Rd.', 17050, 'Mechanicsburg',
       'PA' )
, ( 4, 'Fourth Factory', '44444 Jenzen Blvd.', 17055, 'Pittsburgh',
       'PA' );

COMMIT ;

清单（五）- 10-6

        执行下面的语句查询factory_dim表，确认装载正确。查询结果显示如下。 mysql> select * from factory_dim\G *************************** 1. row ***************************             factory_sk: 1           factory_code: 1           factory_name: First Factory factory_street_address: 11111 Lichtman St.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 *************************** 2. row ***************************             factory_sk: 2           factory_code: 2           factory_name: Second Factory factory_street_address: 22222 Stobosky Ave.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 *************************** 3. row ***************************             factory_sk: 3           factory_code: 3           factory_name: Third Factory factory_street_address: 33333 Fritze Rd.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 *************************** 4. row ***************************             factory_sk: 4           factory_code: 4           factory_name: Fourth Factory factory_street_address: 44444 Jenzen Blvd.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 4 rows in set (0.00 sec)         执行清单（五）- 10-7里的脚本向源数据库的daily_production表导入数据 。

USE source;
INSERT INTO daily_production VALUES
  (1, CURRENT_DATE, 4, 100 )
, (2, CURRENT_DATE, 3, 200 )
, (3, CURRENT_DATE, 2, 300 )
, (4, CURRENT_DATE, 1, 400 )
, (1, CURRENT_DATE, 1, 400 )
, (2, CURRENT_DATE, 2, 300 )
, (3, CURRENT_DATE, 3, 200 )
, (4, CURRENT_DATE, 4, 100 );

COMMIT;

清单（五）- 10-7

        现在已经做好了测试产品定期装载的准备。先把系统日期设置为2015年3月19日，然后执行清单（五）- 10-5里的脚本或对应的Kettle定期装载作业。使用下面的SQL语句查询production_fact表，确认每天产品数据的定期装载是正确的。查询结果显示如下。

mysql> select * from production_fact;

+------------+--------------------+------------+---------------------+ | product_sk | production_date_sk | factory_sk | production_quantity | +------------+--------------------+------------+---------------------+ |          1 |               5556 |          4 |                 100 | |          2 |               5556 |          3 |                 200 | |          4 |               5556 |          2 |                 300 | |          5 |               5556 |          1 |                 400 | |          1 |               5556 |          1 |                 400 | |          2 |               5556 |          2 |                 300 | |          4 |               5556 |          3 |                 200 | |          5 |               5556 |          4 |                 100 | +------------+--------------------+------------+---------------------+ 8 rows in set (0.00 sec)         为了确认工厂维度上成功应用了SCD1，使用下面的语句查询factory_dim表。查询结果显示如下。

mysql> select * from factory_dim\G

*************************** 1. row ***************************             factory_sk: 1           factory_code: 1           factory_name: First Factory factory_street_address: 11111 Lichtman St.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 *************************** 2. row ***************************             factory_sk: 2           factory_code: 2           factory_name: Second Factory factory_street_address: 24242 Bunty La.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 *************************** 3. row ***************************             factory_sk: 3           factory_code: 3           factory_name: Third Factory factory_street_address: 37373 Burbank Dr.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 *************************** 4. row ***************************             factory_sk: 4           factory_code: 4           factory_name: Fourth Factory factory_street_address: 44444 Jenzen Blvd.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA                version: 1         effective_date: 2015-03-18            expiry_date: 2200-01-01 4 rows in set (0.00 sec) 注意 第二和第三个工厂已经正确修改了地址。