Testing the Revised Regular Population Script
In this section I explain how to test the revised regular population script shown in Listing 17.2. The test uses two new sales orders with delivery milestones Order, Allocate, Pack, Ship, and Receive. Therefore, you need to add five rows for each order. The script in Listing 17.3 adds ten new rows into the sales_order table in the source database.
Listing 17.3. Sales orders for testing degeneration
/******************************************************************/
/* */
/* sales_order_17.sql */
/* */
/******************************************************************/
USE source;
INSERT INTO sales_order VALUES
(52, 1, 1, '2007-03-11', 'N', '2007-03-20', '2007-03-11', 7500,
75)
, (53, 2, 2, '2007-03-11', 'N', '2007-03-20', '2007-03-11', 1000,
10)
, (52, 1, 1, '2007-03-12', 'A', '2007-03-20', '2007-03-12', 7500,
75)
, (53, 2, 2, '2007-03-12', 'A', '2007-03-20', '2007-03-12', 1000,
10)
, (52, 1, 1, '2007-03-13', 'P', '2007-03-20', '2007-03-13', 7500,
75)
, (53, 2, 2, '2007-03-13', 'P', '2007-03-20', '2007-03-13', 1000,
10)
, (52, 1, 1, '2007-03-14', 'S', '2007-03-20', '2007-03-14', 7500,
75)
, (53, 2, 2, '2007-03-14', 'S', '2007-03-20', '2007-03-14', 1000,
10)
, (52, 1, 1, '2007-03-15', 'R', '2007-03-20', '2007-03-15', 7500,
75)
, (53, 2, 2, '2007-03-15', 'R', '2007-03-20', '2007-03-15', 1000,
10)
;
/* end of script */ |
Run the script in Listing 17.3 using this command.
mysql> . c:mysqlscriptssales_order_17.sql
Here is the response on the console.
Database changed Query OK, 10 rows affected (0.05 sec) Records: 10 Duplicates: 0 Warnings: 0
Now set your MySQL date to March 11, 2007 and run the dw_regular_17.sql script again. Afterward, set your MySQL date to March 12 through March 15, 2005 and run the dw_regular_17.sql script for each date.
After running the script five times, query the two orders in the sales_order_fact table using this command.
mysql> select order_number od, order_date_sk od_sk, allocate_date_sk
ad_sk,
-> packing_date_sk pk_sk, ship_date_sk sd_sk, receive_date_sk
rd_sk
-> from sales_order_fact
-> where order_number IN(52, 53);You should get the following result.
+------+-------+-------+-------+-------+-------+ | od | od_sk | ad_sk | pk_sk | sd_sk | rd_sk | +------+-------+-------+-------+-------+-------+ | 52 | 741 | 742 | 743 | 744 | 745 | | 53 | 741 | 742 | 743 | 744 | 745 | +------+-------+-------+-------+-------+-------+ 2 rows in set (0.00 sec)
Note
741 - 745 are March 11, 2007 - 15, 2007.