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

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

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