Testing

To test the regular population, set your MySQL date to a date in 2007 and run the band_regular.sql script:

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

You should see this message on your console.

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

Query the customer_order_segment_fact table to confirm the regular population was successful.

 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;

 +-----+-----+---------+---------------+----------+ | csk | ysk | amt     | sn            | bn       | +-----+-----+---------+---------------+----------+ |   1 |   1 | 8000.00 | Grid          | HIGH     | |   1 |   1 | 8000.00 | PROJECT ALPHA | Top      | |   1 |   2 | 4000.00 | Grid          | MED      | |   1 |   2 | 4000.00 | PROJECT ALPHA | Mid-Low  | |   2 |   2 | 5500.00 | Grid          | MED      | |   2 |   2 | 5500.00 | PROJECT ALPHA | Mid      | |   3 |   1 | 4000.00 | Grid          | MED      | |   3 |   1 | 4000.00 | PROJECT ALPHA | Mid-Low  | |   3 |   2 | 2000.00 | Grid          | LOW      | |   3 |   2 | 2000.00 | PROJECT ALPHA | Bottom   | |   4 |   1 | 4000.00 | Grid          | MED      | |   4 |   1 | 4000.00 | PROJECT ALPHA | Mid-Low  | |   4 |   2 | 3000.00 | Grid          | LOW      | |   4 |   2 | 3000.00 | PROJECT ALPHA | Low      | |   5 |   1 | 6000.00 | Grid          | MED      | |   5 |   1 | 6000.00 | PROJECT ALPHA | Mid_High | |   5 |   2 | 2500.00 | Grid          | LOW      | |   5 |   2 | 2500.00 | PROJECT ALPHA | Bottom   | |   6 |   1 | 6000.00 | Grid          | MED      | |   6 |   1 | 6000.00 | PROJECT ALPHA | Mid_High | |   6 |   2 | 3000.00 | Grid          | LOW      | |   6 |   2 | 3000.00 | PROJECT ALPHA | Low      | |   7 |   1 | 8000.00 | Grid          | HIGH     | |   7 |   1 | 8000.00 | PROJECT ALPHA | Top      | |   7 |   2 | 3500.00 | Grid          | MED      | |   7 |   2 | 3500.00 | PROJECT ALPHA | Mid-Low  | +-----+-----+---------+---------------+----------+ 26 rows in set (0.00 sec)

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