The second technique for populating the date dimension is one-date-every-day, which is similar to pre-population. However, with one-date-every-day you pre-populate one date every day, not all dates in a period. Using this method, you still have all dates regardless of their usage, but you do not load all the dates at once.
The script in Listing 6.2 loads the current date into the date_dim table. You should schedule this script to run everyday. If your data warehouse loading also takes place daily, you can merge this daily date population in its dimension loading part.
Listing 6.2: Daily date population
/*****************************************************************/ /* */ /* daily_date.sql */ /* */ /*****************************************************************/ USE dw; INSERT INTO date_dim VALUES ( NULL , CURRENT_DATE , MONTHNAME (CURRENT_DATE) , MONTH (CURRENT_DATE) , QUARTER (CURRENT_DATE) , YEAR (CURRENT_DATE) , '0000-00-00' , '9999-12-31' ) /* end of script */
Clear the date_dim table and run the daily_date.sql script in Listing 6.2.
mysql> \. c:\mysql\scripts\daily_date.sql
Assuming you run this script after setting your MySQL date to February 7, 2007, you’ll get
Database changed Query OK, 1 row affected (0.27 sec)
To verify that the date_dim table was populated successfully, query the table.
mysql> select * from date_dim \G
This is the content of the date_dim table.
*************************** 1. row *************************** date_sk: 1 date: 2007-02-07 month_name: February month: 2 quarter: 1 year: 2007 effective_date: 0000-00-00 expiry_date: 9999-12-31 1 row in set (0.00 sec) mysql>