Chapter 12: Subset Dimensions

Some users never need the most detailed data. For example, rather than requiring dates, it is more likely they want records for a certain month. Rather than the national sales data, they may be more interested in the data for a certain state. These special dimensions contain selected rows from the detailed dimensions, hence the name subset dimensions. Since subset dimensions are smaller than detailed dimensions, they are easier to use and provide faster query response.

In this chapter, you will prepare two special dimensions that you derive from existing dimensions: the month roll-up dimension (a subset of the date dimension) and the Pennsylvania state customer dimension (a subset of the customer dimension).

Month Roll-up Dimension

In this section I explain the month roll-up dimension population process, including its testing.

The script in Listing 12.1 creates the month roll-up dimension and initially populates the months from the date dimension. Note that the promo_ind column is not included. This column is not applicable to the month level, as you can have more than one promotion in a month. Rather, the promotion applies to the date level.

Listing 12.1: Implementing the month roll-up dimension

 /*****************************************************************/ /*                                                             */ /* 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                                                */

The script in Listing 12.1 creates the month_dim table and inserts distinct months from the date_dim table, which contains dates from March 1, 2005 to December 31, 2010. Therefore, when you populate the month_dim table by running the script in Listing 12.1, the table gets 70 months (from March 2005 to December 2010).

Run the script as follows:

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

You will see the following on your console.

 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>

Now query the month_dim table to confirm correct population:

 mysql> select month_sk msk, month_name, month, quarter q, year,     -> effective_date efdate, expiry_date exdate     -> from month_dim;

Here is the result of the query.

 +------+-------------+--------+----+--------+------------+------------+ | 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)    

To have the month dimension populated regularly from the date dimension, you embed its population in the date dimension population script. You need to update the date pre-population stored procedure discussed in Chapter 6. The revised stored procedure is shown in Listing 12.2. The change is printed in bold. The revised script now populates the month roll-up dimension whenever it adds a date entry and its month is not in the month dimension.

Listing 12.2: The revised date pre-population script

 /*****************************************************************/ /*                                                               */ /* 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                                                  */

Recompile the stored procedure by invoking the script in Listing 12.2 using this command.

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

Here is the response you'll see on your console.

 Database changed Query OK, 0 rows affected (0.22 sec) Query OK, 0 rows affected (0.04 sec)

To test the revised date pre-population, run the stored procedure to add the dates from January 1, 2011 to December 31, 2011 using this command.

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

MySQL will indicate that there are twelve records affected.

 Query OK, 12 rows affected (23.07 sec)

To confirm the 12 months were loaded correctly, query the month_dim table using this statement.

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

Here is the query result.

 *************************** 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)