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

第12章: 子集维度

       一些用户从来就不需要大部分的详细数据。例如：他们通常想要某个月的记录而不是某些具体日期的。他们可能对某个地区的数据远比全国的销售数据更感兴趣。这些特定的维是由详细维数据中选取的几个记录组成的,因此称它们为维度的子集。由于维度子集远比详细维度更小，所以它们更加容易被使用而且能提供更快捷的响应。

    在本章，你将预备两个特定的维度，这两个维产生自现有的维：月份向上钻取维（日期维的子集）和宾夕法尼亚州地区客户维（一个客户维的子集）。

月份向上钻取维

       本节，我将解释月份向上钻取维的装载过程以及它的测试。

       列表12-1所示的脚本中，产生了月份向上钻取维表，并通过日期维度初始化装载了月份数据。注意到 promo_ind 字段不包括在该维中。该字段不适用于月份这个级别，因为在一个月份中你可能有不止一个的促销活动。确切的，促销应该应用在日期级别上。

 

列表 12-1：实现月份向上钻取维

/*****************************************************************/

/*                                                             */

/* month_rollup_dim.sql                                        */

/*                                                             */

/*****************************************************************/

 

USE dw;

 

CREATE TABLE month_dim

( month_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY

, month_name CHAR (9)

, month INT (2)

, quarter INT (1)

, year INT (4)

, effective_date DATE

, expiry_date DATE )

;

 

INSERT INTO month_dim

SELECT DISTINCT

  NULL

, month_name

, month

, quarter

, year

, effective_date

, expiry_date

FROM date_dim

;

 

/* end of script                                                */

 

    列表12-1中的脚本产生了month_dim表并向month_dim表插入不重复的月份数据,这些数据包含了从2005-03-01到2010-12-31这段时期的月份数据。当你通过运行列表12-1中的脚本装载month_dim表后，该表将有70个月份（从2005-03-01到2010-12-31）。

    用如下方式运行该脚本：

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

    你将在你的控制台上看到：

Database changed

Query OK, 0 rows affected (0.14 sec)

 

Query OK, 70 rows affected (0.07 sec)

Records: 70  Duplicates: 0  Warnings: 0

 

mysql>

    现在查询month_dim表以确保装载正确：

mysql> select month_sk msk, month_name, month, quarter q, year,

    -> effective_date efdate, expiry_date exdate

-> from month_dim;

查询结果是：

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

| msk  |  month_name |  month | q  |  year  | efdate     | exdate     |

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

|   1  |  March      |      3 |  1 |  2005  | 0000-00-00 | 9999-12-31 |

|   2  |  April      |      4 |  2 |  2005  | 0000-00-00 | 9999-12-31 |

|   3  |  May        |      5 |  2 |  2005  | 0000-00-00 | 9999-12-31 |

|   4  |  June       |      6 |  2 |  2005  | 0000-00-00 | 9999-12-31 |

|   6  |  August     |      8 |  3 |  2005  | 0000-00-00 | 9999-12-31 |

|   5  |  July       |      7 |  3 |  2005  | 0000-00-00 | 9999-12-31 |

|   7  |  September  |      9 |  3 |  2005  | 0000-00-00 | 9999-12-31 |

|   9  |  November   |     11 |  4 |  2005  | 0000-00-00 | 9999-12-31 |

|   8  |  October    |     10 |  4 |  2005  | 0000-00-00 | 9999-12-31 |

|  10  |  December   |     12 |  4 |  2005  | 0000-00-00 | 9999-12-31 |

|  11  |  January    |      1 |  1 |  2006  | 0000-00-00 | 9999-12-31 |

|  12  |  February   |      2 |  1 |  2006  | 0000-00-00 | 9999-12-31 |

|  13  |  March      |      3 |  1 |  2006  | 0000-00-00 | 9999-12-31 |

|  14  |  April      |      4 |  2 |  2006  | 0000-00-00 | 9999-12-31 |

|  15  |  May        |      5 |  2 |  2006  | 0000-00-00 | 9999-12-31 |

|  16  |  June       |      6 |  2 |  2006  | 0000-00-00 | 999 -12-31 |

|  17  |  July       |      7 |  3 |  2006  | 0000-00-00 | 9999-12-31 |

|  18  |  August     |      8 |  3 |  2006  | 0000-00-00 | 9999-12-31 |

|  19  |  September  |      9 |  3 |  2006  | 0000-00-00 | 9999-12-31 |

|  20  |  October    |     10 |  4 |  2006  | 0000-00-00 | 9999-12-31 |

|  21  |  November   |     11 |  4 |  2006  | 0000-00-00 | 9999-12-31 |

|  22  |  December   |     12 |  4 |  2006  | 0000-00-00 | 9999-12-31 |

|  23  |  January    |      1 |  1 |  2007  | 0000-00-00 | 9999-12-31 |

|  24  |  February   |      2 |  1 |  2007  | 0000-00-00 | 9999-12-31 |

|  26  |  April      |      4 |  2 |  2007  | 0000-00-00 | 9999-12-31 |

|  25  |  March      |      3 |  1 |  2007  | 0000-00-00 | 9999-12-31 |

|  27  |  May        |      5 |  2 |  2007  | 0000-00-00 | 9999-12-31 |

|  28  |  June       |      6 |  2 |  2007  | 0000-00-00 | 9999-12-31 |

|  29  |  July       |      7 |  3 |  2007  | 0000-00-00 | 9999-12-31 |

|  30  |  August     |      8 |  3 |  2007  | 0000-00-00 | 9999-12-31 |

|  31  |  September  |      9 |  3 |  2007  | 0000-00-00 | 9999-12-31 |

|  32  |  October    |     10 |  4 |  2007  | 0000-00-00 | 9999-12-31 |

|  33  |  November   |     11 |  4 |  2007  | 0000-00-00 | 9999-12-31 |

|  34  |  December   |     12 |  4 |  2007  | 0000-00-00 | 9999-12-31 |

|  35  |  January    |      1 |  1 |  2008  | 0000-00-00 | 9999-12-31 |

|  36  |  February   |      2 |  1 |  2008  | 0000-00-00 | 9999-12-31 |

|  37  |  March      |      3 |  1 |  2008  | 0000-00-00 | 9999-12-31 |

|  38  |  April      |      4 |  2 |  2008  | 0000-00-00 | 9999-12-31 |

|  39  |  May        |      5 |  2 |  2008  | 0000-00-00 | 9999-12-31 |

|  40  |  June       |      6 |  2 |  2008  | 0000-00-00 | 9999-12-31 |

|  41  |  July       |      7 |  3 |  2008  | 0000-00-00 | 9999-12-31 |

|  42  |  August     |      8 |  3 |  2008  | 0000-00-00 | 9999-12-31 |

|  43  |  September  |      9 |  3 |  2008  | 0000-00-00 | 9999-12-31 |

|  44  |  October    |     10 |  4 |  2008  | 0000-00-00 | 9999-12-31 |

|  45  |  November   |     11 |  4 |  2008  | 0000-00-00 | 9999-12-31 |

|  46  |  December   |     12 |  4 |  2008  | 0000-00-00 | 9999-12-31 |

|  47  |  January    |      1 |  1 |  2009  | 0000-00-00 | 9999-12-31 |

|  48  |  February   |      2 |  1 |  2009  | 0000-00-00 | 9999-12-31 |

|  49  |  March      |      3 |  1 |  2009  | 0000-00-00 | 9999-12-31 |

|  50  |  April      |      4 |  2 |  2009  | 0000-00-00 | 9999-12-31 |

|  51  |  May        |      5 |  2 |  2009  | 0000-00-00 | 9999-12-31 |

|  52  |  June       |      6 |  2 |  2009  | 0000-00-00 | 9999-12-31 |

|  53  |  July       |      7 |  3 |  2009  | 0000-00-00 | 9999-12-31 |

|  54  |  August     |      8 |  3 |  2009  | 0000-00-00 | 9999-12-31 |

|  55  |  September  |      9 |  3 |  2009  | 0000-00-00 | 9999-12-31 |

|  56  |  October    |     10 |  4 |  2009  | 0000-00-00 | 9999-12-31 |

|  57  |  November   |     11 |  4 |  2009  | 0000-00-00 | 9999-12-31 |

|  58  |  December   |     12 |  4 |  2009  | 0000-00-00 | 9999-12-31 |

|  59  |  January    |      1 |  1 |  2010  | 0000-00-00 | 9999-12-31 |

|  60  |  February   |      2 |  1 |  2010  | 0000-00-00 | 9999-12-31 |

|  61  |  March      |      3 |  1 |  2010  | 0000-00-00 | 9999-12-31 |

|  62  |  April      |      4    2 |  2010  | 0000-00-00 | 9999-12-31 |

|  63  |  May        |      5 |  2 |  2010  | 0000-00-00 | 9999-12-31 |

|  64  |  June       |      6 |  2 |  2010  | 0000-00-00 | 9999-12-31 |

|  65  |  July       |      7 |  3 |  2010  | 0000-00-00 | 9999-12-31 |

|  66  |  August     |      8 |  3 |  2010  | 0000-00-00 | 9999-12-31 |

|  67  |  September  |      9 |  3 |  2010  | 0000-00-00 | 9999-12-31 |

|  68  |  October    |     10 |  4 |  2010  | 0000-00-00 | 9999-12-31 |

|  69  |  November   |     11 |  4 |  2010  | 0000-00-00 | 9999-12-31 |

|  70  |  December   |     12 |  4 |  2010  | 0000-00-00 | 9999-12-31 |

+------+-------------+--------+----+--------+------------+-----------70 rows in set (0.00 sec)   

    为了能够定期的从日期维载入数据到月份维，你可以插入它的装载脚本到日期维度的装载脚本中。你需要更新之前在第6章讨论过的日期维装载的存储过程。修改后的存储过程如列表12-2所示。修改的地方用粗体表示。现在，只要在装载一个新的日期条目并且该日期的月份没有在月份维表中的时候，该修改后的脚本将能够装载月份向上钻取维表。

 

列表 12-2：修正后的日期预装载脚本

/*****************************************************************/

/*                                                               */

/* pre_populate_date_12.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;

 

INSERT INTO month_dim

SELECT DISTINCT

  NULL

, month_name

, month

, quarter

, year

, effective_date

, expiry_date

FROM date_dim

WHERE CONCAT (month, year) NOT IN

(SELECT CONCAT (month, year) FROM month_dim)

;

 

END

//

 

DELIMITER ; //

 

/* end of script                                                  */

 

用如下命令调用列表12-2所示的脚本来重新编译该存储过程：

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

你将在你的控制台上看到如下的响应信息：

Database changed

 

Query OK, 0 rows affected (0.22 sec)

Query OK, 0 rows affected (0.04 sec)

 

为了测试该修正后的预装载日期机制，运行该存储过程来增加从2011-01-01 到 2011-12-31期间的日期，用如下的命令方式：

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

 

Mysql将显示有12个记录生效。

Query OK, 12 rows affected (23.07 sec)

为了确认12个月份数据被正确载入，用该语句查询month_dim表：

mysql> select * from month_dim where year = 2011 \G

下面是查询的结果：

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

      month_sk: 71

    month_name: January

         month: 1

       quarter: 1

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 2 row ***************************

      month_sk: 72

    month_name: February

         month: 2

       quarter: 1

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 3 row ***************************

      month_sk: 73

    month_name: March

         month: 3

       quarter: 1

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

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

      month_sk: 74

    month_name: April

         month: 4

       quarter: 2

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 5 row ***************************

      month_sk: 75

    month_name: May

         month: 5

       quarter: 2

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

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

      month_sk: 76

    month_name: June

         month: 6

       quarter: 2

          year: 2011

effective date: 0000-00-00

   expiry_date: 9999-12-31

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

      month_sk: 77

    month_name: July

         month: 7

       quarter: 3

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

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

      month_sk: 78

    month_name: August

         month: 8

       quarter: 3

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 9 row ***************************

      month_sk: 79

    month_name: September

         month: 9

       quarter: 3

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

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

      month_sk: 80

    month_name: October

         month: 10

       quarter: 4

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 11. row ***************************

      month_sk: 81

    month_name: November

         month: 11

       quarter: 4

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

*************************** 12. row ***************************

      month_sk: 82

    month_name: December

         month: 12

       quarter: 4

          year: 2011

effective_date: 0000-00-00

   expiry_date: 9999-12-31

12 rows in set (0.00 sec)