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:mysqlscriptsand_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.