Among the three techniques, pre-population is the easiest one for populating the date dimension. With pre-population, you insert all dates for a period of time. For example, you can pre-populate the date dimension with dates in ten years, such as from January 1, 2005 to December 31, 2015. Using this technique you may pre-populate the date dimension once only for the life of your data warehouse. Alternatively, you may add data as required.
The drawbacks of pre-population are:
Early disk space consumption
You might not need all the dates (sparse usage)
Listing 6.1 shows a stored procedure you can use for pre-population. The stored procedure accepts two arguments, start_dt (start date) and end_dt (end date). The WHILE loop in the stored procedure incrementally generates all the dates from start_dt to end_dt and inserts these dates into the date_dim table.
Note | An effective date 0000-00-00 means the dates are effective from anytime in the past. An expiry date of 9999-12-31 means it has not expired. |
Listing 6.1: Stored procedure to pre-populate the date dimension
/*****************************************************************/ /* */ /* 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 */
You compile the stored procedure using this command.
mysql> \. c:\mysql\scripts\pre_populate_date.sql
Confirm the stored procedure has been successfully created in the dw database by using the show procedure command:
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)