Regular Population


You need two scripts for the regular population. The first script is for January population and is presented in Listing 24.3. The second script is for the other months and is given in Listing 24.4.

The January regular population script is similar to the January initial population in that the script must reset the month end balance. However, while the January initial population is part of the whole initial population script, the January regular population is a separate script from the other months. You run the January regular population script at the end of January.

The regular population for the other months (February to December) is also similar to the initial population in that they both have three Insert statements. However, while the initial population must load historical data from January 2006 to March 2007 all at once, the regular population loads the current month data only. The other difference is that the regular population does not have a specific year condition. You run the regular population at the end of every month (February to December).

Listing 24.3: January month end balance regular population script

image from book
 /*****************************************************************/ /*                                                    */ /* jan_month_end_balance_regular.sql                  */ /*                                                    */ /*****************************************************************/ USE dw; INSERT INTO month_end_balance_fact SELECT m.* FROM   month_end_sales_order_fact m , month_dim n WHERE     month = 1 AND m.month_order_sk = n.month_sk AND n.year - YEAR (CURRENT_DATE) ; /* end of script                                      */
image from book

Listing 24.4: Month end balance regular population for all months except January

image from book
 /*****************************************************************/ /*                                                               */ /* month_end_balance_regular.sql                                 */ /*                                                               */ /*****************************************************************/ USE dw; INSERT INTO month_end_balance_fact SELECT   n.month_order_sk , n.product_sk , (n.month_order_amount + m.month_end_amount_balance) , (n.month_order_quantity + m.month_end_quantity_balance) FROM   month_end_balance_fact m , month_end_sales_order_fact n , month_dim o , month_dim p WHERE     o.month - MONTH (CURRENT_DATE) - 1 AND p.month - MONTH (CURRENT_DATE) AND m.month_sk = o.month_sk AND n.month_order_sk = p.month_sk AND o.year = p.year AND m.product_sk = n.product_sk ; INSERT INTO month_end_balance_fact SELECT m.* FROM   month end sales_order_fact m , month_dim n WHERE n.month - MONTH (CURRENT_DATE) AND m.month_order_sk = n.month_sk AND m.product_sk NOT IN ( SELECT x.product_sk FROM   month_end_balance_fact x , month_dim y WHERE     x.month_sk = y.month_sk AND y.month = (MONTH (CURRENT_DATE)-1) AND y.year = n.year ) ; INSERT INTO month_end_balance_fact SELECT   o.month_sk , m.product_sk , m.month_end_amount_balance , m.month_end_quantity_balance FROM   month_end_balance_fact m , month_dim n , month_dim o WHERE     n.month - MONTH (CURRENT_DATE)-1 AND m.month_sk = n.month_sk AND o.month = MONTH (CURRENT_DATE) AND n.year = o.year AND m.product_sk NOT IN ( SELECT x.product_sk FROM   month_end_sales_order_fact x , month_dim y WHERE     x.month_order_sk = y.month_sk AND y.month - MONTH (CURRENT_DATE) AND y.year = n.year) ; /* end of script                                                 */
image from book

As the regular population scripts are similar to the initial population script, you don't need to test the regular population.



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