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
/*****************************************************************/ /* */ /* 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 */