Initial Population

In this section I explain the initial population and show you how to test it. The script in Listing 25.3 initially populates the year_dim table with data from the order_date dimension (a view of the date_dim table), the annual_sales_order_fact table with data from sales_order_ fact, and the annual_customer_segment_fact table with data from annual_sales_order_fact. The script loads all previous years' data (historical data).

Listing 25.3: The initial population script

 /*****************************************************************/ /*                                                               */ /* band_ini.sql                                                  */ /*                                                               */ /*****************************************************************/ INSERT INTO year_dim SELECT DISTINCT   NULL , year , effective_date , expiry_date FROM order date dim ; 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 d.year < YEAR (CURRENT_DATE) GROUP BY a.customer_sk, d.year ; INSERT INTO annual_customer_segment_fact SELECT   d.segment_sk , a.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 year < YEAR (CURRENT_DATE) AND annual_order_amount >= band_start_amount AND annual order amount <= band end amount ; /* end of script                                                 */

To test the initial population script, set your MySQL date to any date in 2006 to load year 2005 data. You load the 2006 sales orders later in the regular testing section.

Now run the script in Listing 25.3:

 mysql> \. c:\mysql\scripts\band_ini.sql

You should get this as the response.

 Query OK, 7 rows affected (0.10 sec) Records: 7  Duplicates: 0  Warnings: 0 Query OK, 6 rows affected (0.05 sec) Records: 6  Duplicates: 0  Warnings: 0 Query OK, 12 rows affected (0.06 sec) Records: 12  Duplicates: 0  Warnings: 0

To confirm the initial population was successful, query the annual_customer_segment_fact table using this statement.

 mysql> select a.customer_sk csk, a.year_sk ysk,     -> annual_order_amount amt, segment_name sn, band_name bn     -> from annual_customer_segment_fact a, annual_order_segment_dim        b,     -> year_dim c, annual_sales_order_fact d     -> where a.segment_sk = b.segment_sk AND a.year_sk = c.year_sk     -> AND a.customer_sk = d.customer_sk AND a.year_sk = d.year_sk     -> order BY a.customer_sk, year, segment_name, band_name;

Here is the query result.

 +-----+-----+---------+---------------+----------+ | csk | ysk | amt     | sn            | bn       | +-----+-----+---------+---------------+----------+ |   1 |   1 | 8000.00 | Grid          | HIGH     | |   1 |   1 | 8000.00 | PROJECT ALPHA | Top      | |   3 |   1 | 4000.00 | Grid          | MED      | |   3 |   1 | 4000.00 | PROJECT ALPHA | Mid-Low  | |   4 |   1 | 4000.00 | Grid          | MED      | |   4 |   1 | 4000.00 | PROJECT ALPHA | Mid-Low  | |   5 |   1 | 6000.00 | Grid          | MED      | |   5 |   1 | 6000.00 | PROJECT ALPHA | Mid_High | |   6 |   1 | 6000.00 | Grid          | MED      | |   6 |   1 | 6000.00 | PROJECT ALPHA | Mid_High | |   7 |   1 | 8000.00 | Grid          | HIGH     | |   7 |   1 | 8000.00 | PROJECT ALPHA | Top      | +-----+-----+---------+---------------+----------+ 12 rows in set (0.00 sec)

The query result proves that every customer who has placed at least one order in 2005 is assigned to a band of each of the two segments. You can verify that the assignments of the annual sales amounts on the bands are correct. You need to refer back to Table 25.1 to find out the definition of the bands.