Revising the Regular Population Script


In this section I explain the regular population script and how to test it. The regular population is similar to the initial population, except that the former does not need to load the year_dim table. The annual_customer_segment_fact table is populated with data form the annual_sales_order_fact table.

You schedule the regular population script in Listing 25.4 annually. The script loads the previous year sales order data.

Listing 25.4: The revised regular population script

image from book
 /*****************************************************************/ /*                                                               */ /* band_regular.sql                                              */ /*                                                               */ /*****************************************************************/ INSERT INTO annual_sales_order_fact SELECT   b.customer_sk , year_sk , SUM (order_amount) FROM   sales_order_fact a , customer_dim b , year_dim c , order_date_dim d WHERE     a.customer_sk = b.customer_sk AND a.order_date_sk = d.order_date_sk AND c.year = d.year AND c.year = YEAR (CURRENT_DATE) - 1 GROUP BY   customer_number , c.year ; INSERT INTO annual_customer_segment_fact SELECT   d.segment_sk , b.customer_sk , c.year_sk FROM   annual_sales_order_fact a , customer_dim b , year_dim c , annual_order_segment_dim d WHERE     a.customer_sk = b.customer_sk AND a.year_sk = c.year_sk AND c.year = YEAR (CURRENT_DATE) - 1 AND annual_order_amount >= band_start_amount AND annual_order_amount <= band_end_amount ; /* end of script                                                 */
image from book



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