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:mysqlscriptsmonth_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 | | 5 | July | 7 | 3 | 2005 | 0000-00-00 | 9999-12-31 | | 6 | August | 8 | 3 | 2005 | 0000-00-00 | 9999-12-31 | | 7 | September | 9 | 3 | 2005 | 0000-00-00 | 9999-12-31 | | 8 | October | 10 | 4 | 2005 | 0000-00-00 | 9999-12-31 | | 9 | November | 11 | 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 | 9999-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 | | 25 | March | 3 | 1 | 2007 | 0000-00-00 | 9999-12-31 | | 26 | April | 4 | 2 | 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:mysqlscriptspre_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)..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.