数据仓库建立多维模型多维数据仓库事实表

转载

mob64ca13fb6939 2024-06-25 10:22:26

文章标签 数据仓库建立多维模型 mysql多维数据仓库数据仓库 mysql Storage 文章分类 数据仓库大数据

第二类缓慢变化维技术(SCD2)

我在第1章“基本组成”中讨论过代理键，但是到目前为止，还没有让你理解，为什么你一定需要一个代理键。你可能仍然感到困惑，为什么你需要另外建立一个键，你已经有一个来自源数据的键了(也称为自然键).很快你就会明白，你可以用代理键（结合生效、失效日期）来应用SCD2以维护维成员的历史记录。

你无法用自然键来应用SCD2。比如产品维的自然键，存储在product_code字段中，应用SCD2的时候，如果产品名称改变了，为了维护历史记录，你必须保持原来的记录，并用新的名称来增加新的记录。然而，你不可以有两个记录而用一个相同的键，就是product_code产品编号。这就是为什么你需要一个代理键。

用生效、失效日期来定义一个记录是否有效。当需要对一个现有的维成员记录增加新的版本时，你可以对原有记录设置一个早于新记录生效日期的失效日期来使它失效。例如，假如新记录的生效日期是2008-01-19, 你可以设置原有记录的失效日期为2008-01-18。同时你可以设置新记录的失效期为9999–12–31，相反，SCD1不需要用到生效、失效日期。

列表2-3 的脚本对product_dim表应用SCD2。同理，不管是product_name或者product_category字段发生变化，SCD2使得原有的记录失效，并且增加新的记录来描述同一个产品。注意这里列表2-3中的脚本假设新的产品信息已经存在名为product_stg的临时表。

列表2-3：应用SCD2到product_dim 表的product_name 和 product_category 字段。

/*****************************************************************/
/*                                                               */
/* scd2.sql                                                      */
/*                                                               */
/*****************************************************************/
 
/* default database to dw                                        */
USE dw;
 
/* expire the existing product                                   */
 
UPDATE
  product_dim a
, product_stg b
SET
     expiry_date = SUBDATE (CURRENT_DATE, 1)
WHERE
     a.product_code = b.product_code
AND ( a.product_name <> b.product_name
     OR a.product_category <> b.product_category )
AND expiry_date = '9999-12-31'
;
 
/* add a new row for the changing product                          */
 
INSERT INTO product_dim
SELECT
  NULL
, b.product_code
, b.product_name
, b.product_category
, CURRENT_DATE
, '9999-12-31'
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 = SUBDATE (CURRENT_DATE, 1)
      AND NOT EXISTS
( SELECT *
   FROM product_dim y
   WHERE    b.product_code = y.product_code
         AND y.expiry_date = '9999-12-31' )
;
 
/* add new product                                              */
INSERT INTO product_dim
SELECT
  NULL
, product_code
, product_name
, product_category
, CURRENT_DATE
, '9999-12-31'
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 )
;
/* end of script

注意在实际生产环境中，你将计划调度该脚本，在数据仓库的载入循环周期中，定期运行该脚本。(定期载入会在第8章“定期载入”中讨论)。

在你运行列表2-3之前，首先你需要先准备数据然后建立product_stg表。实际上，这是两个准备阶段。第一个阶段包含三步：

1．准备产品信息源数据文件。

2．通过运行create_product_stg.sql脚本(列表2-4 )和load_product_stg.sql脚本（列表2-5），建立product_stg表并且载入产品信息源文件。

3．运行scd2.sql脚本，初始化product_dim表，载入表数据。

第二个阶段包含2步：

1．改变产品信息源文件，并把数据载入到临时表中。

2．再次运行scd2.sql 脚本来实施SCD2，并确认SCD2已经被有效的应用了。

所有步骤在接下来的小节中讨论。

准备产品信息源数据文件

你将从名为product.txt的文件中导入两个产品信息到product_dim表中，product.txt是固定宽度格式的文件，包含在本书随书附带的zip文件中。以下是product.txt文件的内容。

产品编号产品名称产品分组

1              Hard Disk      Storage
2              Floppy Drive   Storage

你需要做的是将该文件拷贝到c:\mysql\data\dw目录中。事实上，你需要拷贝所有的特定格式的文件到该目录下面。

建立临时表及载入数据

运行列表2-4中的create_product_stg.sql脚本来建立product_stg表。

列表 2.4: 建立 product_stg 表

/*****************************************************************/
/*                                                               */
/* create_product_stg.sql                                        */
/*                                                               */
/*****************************************************************/
 
/* default database to dw                                        */
 
USE dw;
 
CREATE TABLE product_stg
( product_code INT
, product_name CHAR (30)
, product_category CHAR (30))
;
 
/* end of script

现在运行create_product_stgsql脚本：

mysql> \. c:\mysql\scripts\create_product_stg.sql

接下来用mysql的LOAD DATA INFILE命令，将特定格式的文本文件内容载入到mysql数据库的表中。列表2-5中的load_product_stg.sql脚本将product.txt文件内容载入到product_stg表中。

列表 2-5: 导入产品信息到临时表中

/*****************************************************************/
/*                                                               */
/* load_product_stg.sql                                          */
/*                                                               */
/*****************************************************************/
 
/* default database to dw                                        */
 
USE dw;
 
/* clean up the staging table                                    */
 
TRUNCATE product_stg;
 
/* use LOAD DATA INFILE                                          */
 
LOAD DATA INFILE 'product.txt'
INTO TABLE product_stg
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
( product_code
, product_name
, product_category )
;
/* end of script

用下面的命令格式运行列表2-5中的脚本：

mysql> \. c:\mysql\scripts\load_product_stg.sql

响应信息将如下所示：

Database changed
Query OK, 1 row affected (0.09 sec)
Query OK, 2 rows affected (0.09 sec)
Records: 2  Deleted: 0  Skipped: 0 Warnings: 0

接下来查询product_stg表，确认已经成功载入两个产品信息。

mysql> select * from product_stg;

product_stg表包含内容将如下所示：

+----------------+---------------+-------------------+
| product_code | product_name | product_category |
+----------------+---------------+-------------------+
|         1  | Hard Disk   |  Storage        |
|         2  | Floppy Drive |  Storage        |
+----------------+---------------+-------------------+
2 rows in set   (0.00 sec)

执行初始化导入

将系统时间设置为2007-02-03,然后运行列表2-3中的scd2.sql脚本向product_dim表初始化载入两个产品信息。

mysql> \. c:\mysql\scripts\scd2.sql

在你的控制台界面上将显示：

Database changed
Query OK, 0 rows affected (0.16 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

查询product_dim表确认已经成功载入两个产品信息。

mysql> select * from product_dim \G

在你的控制台界面上将显示：

*************************** 1. row ***************************
      product_sk: 1
    product_code: 1
    product_name: Hard Disk
product_category: Storage
  effective_date: 2007-02-03
     expiry_date: 9999-12-31
*************************** 2 row ***************************
      product_sk: 2
    product_code: 2
    product_name: Floppy Drive
product_category: Storage
  effective_date: 2007-02-03
     expiry_date: 9999-12-31
2 rows in set (0.00 sec)

改变产品信息源文件

接下来用文本编辑器改变刚才用到的product.txt文件，你需要改变编号1的产品名称为“Hard Disk Drive”，并新增编号3的产品信息。

PRODUCT CODE PRODUCT NAME PRODUCT GROUP
1           Hard Disk Drive               Storage
2           Floppy Drive                  Storage
3           LCD Panel                     Monitor

应用SCD2

设置mysql的日期为2007-02-05。模拟一个比刚才初始化载入的日期（2007-02-03）更晚的日期是必要的。

现在多次运行列表2-5所示的load_product_stg.sql脚本，然后运行列表2-3中的scd2.sql。

mysql> \. c:\mysql\scripts\load_product_stg.sql

在你的控制台界面上将显示：

Database changed
Query OK, 2 rows affected (0.11 sec)
 
Query OK, 3 rows affected (0.06 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> \. c:\mysql\scripts\scd2.sql
Database changed
Query OK, 1 row affected (0.43 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

为了确保SCD2已经实施正常，查询product_dim表

mysql> select * from product_dim \G
*************************** 1. row ***************************
      product_sk: 1
    product_code: 1
    product_name: Hard Disk
product_category: Storage
  effective_date: 2007-02-03
     expiry_date: 2007-02-04
*************************** 2. row ***************************
      product_sk: 2
    product_code: 2
    product_name: Floppy Drive
product_category: Storage
  effective_date: 2007-02-03
     expiry_date: 9999-12-31
*************************** 3. row ***************************
      product_sk: 3
    product_code: 1
    product_name: Hard Disk Drive
product_category: Storage
  effective_date: 2007-02-05
     expiry_date: 9999-12-31
*************************** 4. row ***************************
      product_sk: 4
    product_code: 3
    product_name: LCD Panel
product_category: Monitor
  effective_date: 2007-02-05
     expiry_date: 9999-12-31
4 rows in set (0.00 sec)

结果分析

下面对product_dim的分析表明SCD2已经正确的实施。

l 产品1有两条记录，其中一条记录（product_sk字段值是1）已经过期失效，其失效日期是2007-02-04,这是一个早于你实施SCD2的日期（2007-02-05）。另外一条记录（product_sk字段值是3）有一个新的名称。它的生效日期是2007-02-05，失效日期是9999–12–31，也就是说它仍未失效。

l 编号是3的新产品是已经被加入，它的生效日期是2007-02-05。

小结

本章你学习到什么是维成员和维成员的历史记录，以及为什么你需要维护维成员的历史记录。你学习了SCD（缓慢变化维）技术和分别对客户维和产品维实施SCD1和SCD2。

在第7章的“初始化载入”和第8章的“定期载入”你开始相应的应用这些技术在你的数据仓库的初始化载入和定期载入中。然而在这之前你还要在接下来的章节中学习一个事实表的重要特性（度量的可加性）。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。