Pre-population


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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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)



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net