One-Date-Every-Day


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

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

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>



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