mysql多维数据仓库指南--第二篇第6章(1)

第6章 装载日期维

 

概述

       日期维在维度数据仓库中扮演特殊的角色。首先，也是最主要的，日期维包含时间信息，而时间信息是最重要的，因为数据仓库的一个最基本的功能就是存储历史数据。因此，数据仓库中的数据总是有一个时间特征。日期维的另一个特别的方面是，在数据仓库中，你可以选择产生什么样的日期数据用来装载日期维。

       注意，一些数据仓库既要求日期也要求时间。

       在你的数据仓库中，你从建立date_dim 表开始,该表包含一个日期或者是日期时间字段。该表中的值将成为其他拥有日期值的表的参照。在我们的数据仓库中，date_dim 表通过date_sk代理键关联到sales_order_fact表。比如，在销售订单事实表中，所有的拥有2007-02-06订单日期的订单记录都有一个为1的值，因为在date_dim 表中，2007-02-06日期的date_sk值是1。除了日期外，日期维还包含其他信息，比如月份名称和季度。通过代理键的关系，使得这些其他信息可以关联到销售订单记录中。这意味着你可以通过月份名称和季度查询销售订单。

       本章将教你在多维数据仓库中3种最常用的装载日期维的技术。这3种技术是：

n        预装载

n        每天一个日期

n        从源数据获得日期信息

 

预装载

       三种技术中，预装载是装载日期维最容易的一种技术。通过预装载，你插入一段时期中的所有日期信息。举个例子，你可以预先装载日期维近10年内的所有日期信息，例如从2005-01-01到2015-12-31。通过这个技术，你既可以一次性把你的数据仓库寿命范围的日期预装载到日期维，也可以根据需要在追加日期数据。

n        预装载的缺点是：

n        提早占用磁盘空间资源

n        你可能没有必要使用所有的日期（稀疏使用）

列表6-1显示了你可以执行预装载的存储过程。存储过程有两个输入参数，start_dt (开始日期) 和 end_dt (结束日期)。存储过程中的While循环，增量的产生从start_dt到end_dt的所有日期，并将这些日期数据插入到date_dim 表中。

       注意，生效日期值为0000-00-00表示这些日期记录从过去的任何时间开始生效，到期日期值为9999-12-31表示这些日期记录不会失效。

列表6-1：预装载时间维的存储过程

/*****************************************************************/
/*                                                               */
/* pre_populate_date.sql                                         */
/*                                                               */
/*****************************************************************/
 
USE dw;
 
DELIMITER // ;
 
DROP PROCEDURE IF EXISTS pre_populate_date //
 
CREATE PROCEDURE pre_populate_date (IN start_dt DATE, IN end_dt
       DATE)
BEGIN
       WHILE start_dt <= end_dt DO
             INSERT INTO date_dim(
               date_sk
             , date
             , month_name
             , month
             , quarter
             , year
             , effective_date
             , expiry_date
             )
             VALUES(
               NULL
             , start_dt
             , MONTHNAME (start_dt)
             , MONTH (start_dt)
             , QUARTER (start_dt)
             , YEAR (start_dt)
             , '0000-00-00'
             , '9999-12-31'
     )
             ;
             SET start_dt = ADDDATE (start_dt, 1);
      END WHILE;
END
//
 
DELIMITER ; //
 
/* end of script      

你可以用如下命令编译该存储过程：

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

用show procedure 命令确定这个存储过程已经成功的建立。

mysql> show procedure status like 'pre_populate_date' \G

该存储过程信息如下：

The response from the stored procedure is as follows.

*************************** 1. row ***************************
           Db: dw
         Name: pre_populate_date
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2007-02-07 22:42:03
      Created: 2007-02-07 22:42:03
Security_type: DEFINER
      Comment:
1 row in set (0.43 sec)

预装载测试

       本节中我将向你展示如何确定预装载日期的存储过程已经正确的向date_dim表产生日期数据。

       在我们开始之前，用下面的命令清空date_dim表中的记录。

mysql> truncate date_dim;

Mysql 将显示3条记录被删除：

Query OK, 3 rows affected (0.59 sec)

       现在运行列表6-1中的存储过程，把从2007-01-01到2010-12-31这段时期的日期预装载到日期维中。这可能会花几分钟来插入1461条记录，这就是从2007-01-01到2010-12-31这段时期的日期的个数。

mysql> call pre_populate_date ('2007-01-01', '2010-12-31');

       为了确保日期记录已经被正确的预装载到date_dim表中，查询date_dim表中的记录数：

mysql> select count(0) from date_dim;

你将看到结果是1461。

+----------+

| count(0) |

+----------+

|     1461 |

+----------+

1 row in set (0.06 sec)

如果你要查询前面10条日期记录用这个命令：

mysql> select * from date_dim limit 10 \G

你将看到这些结果：

*************************** 1. row ***************************

       date_sk: 1

          date: 2007-01-01

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 2. row ***************************

       date_sk: 2

          date: 2007-01-02

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 3. row ***************************

       date_sk: 3

          date: 2007-01-03

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 4. row ***************************

       date_sk: 4

          date: 2007-01-04

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 5. row ***************************

       date_sk: 5

          date: 2007-01-05

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 6. row ***************************

       date_sk: 6

          date: 2007-01-06

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 7. row ***************************

       date_sk: 7

          date: 2007-01-07

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 8. row ***************************

       date_sk: 8

          date: 2007-01-08

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 9. row ***************************

       date_sk: 9

          date: 2007-01-09

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 10. row **************************

       date_sk: 10

          date: 2007-01-10

    month_name: January

         month: 1

       quarter: 1

          year: 2007

effective_date: 0000-00-00

   expiry_date: 9999-12-31

10 rows in set (0.00 sec)

If you query the last ten dates using this command

mysql> select * from date_dim limit 1451, 1461 \G

you’ll see these records shown.

*************************** 1. row ***************************

       date_sk: 1452

          date: 2010-12-22

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 2. row ***************************

       date_sk: 1453

          date: 2010-12-23

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 3. row ***************************

       date_sk: 1454

          date: 2010-12-24

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 4. row ***************************

       date_sk: 1455

          date: 2010-12-25

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 5. row **************************

       date_sk: 1456

          date: 2010-12-26

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 6. row ***************************

       date_sk: 1457

          date: 2010-12-27

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 7. row ***************************

       date_sk: 1458

          date: 2010-12-28

   month_name : December

         month: 12

       quarter: 4

          year: 2010

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 8. row ***************************

       date_sk: 1459

          date: 2010-12-29

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 9. row ***************************

       date_sk: 1460

          date: 2010-12-30

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 10. row ***************************

       date_sk: 1461

          date: 2010-12-31

    month_name: December

         month: 12

       quarter: 4

          year: 2010

effective_date: 0000-00-00

   expiry_date: 9999-12-31

10 rows in set (0.00 sec)