Before you can run the new regular population script (dw_regular_14.sql) in Listing 14.6, you need to prepare some data. In fact, there are six steps you need to do to track the life cycles of two sales orders:
1. | Adding two new sales orders |
2. | Running the dw_regular_14.sql script and confirming correct population |
3. | Adding sales orders for the two orders with Allocate and/or Pack milestones |
4. | Running the dw_regular_14.sql script and confirming correct population |
5. | Adding sales orders for the two orders with their next milestones: Allocate, Ship, and/or Receive. Note that the four dates can be the same |
6. | Running the dw_regular_14.sql script and confirming correct population. |
The following sub-sections guide you to perform these six steps.
The script in Listing 14.7 adds two new orders placed on March 5, 2007.
/******************************************************************/ /* */ /* add_two_sales_orders.sql */ /* */ /******************************************************************/ USE source; INSERT INTO sales_order VALUES (50, 1, 1, '2007-03-05', 'N', '2007-03-10', '2007-03-05', 7500, 75) , (51, 2, 2, '2007-03-05', 'N', '2007-03-10', '2007-03-05', 1000, 10) ; /* end of script */ |
Run the preceding script using this command.
mysql> . c:mysqlscriptsadd_two_sales_orders.sql
You must now set your MySQL date to March 5, 2007, the entry date of the two new sales orders that you have just added. Run the dw_regular_14.sql script using this command.
mysql> . c:mysqlscriptsdw_regular_14.sql
You can confirm its success by querying the two sales orders in the sales_order_fact table using this statement.
mysql> select order_number, a.order_date_sk, allocate_date_sk, -> packing_date_sk, ship_date_sk, receive_date_sk -> from sales_order_fact a, order_dim b, order_date_dim c -> where order_number IN(50, 51) -> and a.order_sk = b.order_sk -> and a.order_date_sk = c.order_date_sk G
Here are the correct records.
*************************** 1. row *************************** order_number: 50 order_date_sk: 735 allocate_date_sk: NULL packing_date_sk: NULL ship_date_sk: NULL receive_date_sk: NULL *************************** 2. row *************************** order_number: 51 order_date_sk: 735 allocate_date_sk: NULL packing_date_sk: NULL ship_date_sk: NULL receive_date_sk: NULL 2 rows in set (0.01 sec)
Note
Only the order_date_sk column has values, the other dates are NULL because these two orders are new and have not been allocated, packed, shipped, or received.
You can run the script in Listing 14.8 to add two sales order transaction records with allocate dates and packing dates as well as one transaction record with an allocate date. These transactions are for the same two orders (order no 50 and 51) you added in the preceding step.
/******************************************************************/ /* */ /* sales_orders_step3.sql */ /* */ /******************************************************************/ USE source; INSERT INTO sales_order VALUES (50, 1, 1, '2007-03-06', 'A', '2007-03-10', '2007-03-06', 7500, 75) , (50, 1, 1, '2007-03-06', 'P', '2007-03-10', '2007-03-06', 7500, 75) , (51, 2, 2, '2007-03-06', 'A', '2007-03-10', '2007-03-06', 1000, 10) ; /* end of script */ |
Run the script by issuing this command.
mysql> . c:mysqlscriptssales_orders_step3.sql
Set your MySQL date to March 6, 2007, then run the dw_regular_14.sql script using this command.
mysql> . c:mysqlscriptsdw_regular_14.sql
Query the two sales orders in the sales_order_fact table to confirm correct population using this SQL statement.
mysql> select order_number, a.order_date_sk, allocate_date_sk, -> packing_date_sk, ship_date_sk, receive_date_sk -> from sales_order_fact a, order_dim b, order_date_dim c -> where order_number IN(50, 51) -> and a.order_sk = b.order_sk -> and a.order_date_sk = c.order_date_sk G
Here are the result of the query.
*************************** 1. row *************************** order_number: 50 order_date_sk: 735 allocate_date_sk: 736 packing_date_sk: 736 ship_date_sk: NULL receive_date_sk: NULL *************************** 2. row *************************** order_number: 51 order_date_sk: 735 allocate_date_sk: 736 packing_date_sk: NULL ship_date_sk: NULL receive_date_sk: NULL 2 rows in set (0.00 sec) mysql>
Note
The first order gets the allocate_date_sk and packing_date_sk, the second two gets the allocate_date_sk only.
The script in Listing 14.9 can be used to add three sales order transaction records with ship and receive dates, which complete the cycle of this order. The script also adds a record with allocate and packing dates. Again, these transactions are for the same two orders (orders 50 and 51).
/******************************************************************/ /* */ /* sales_orders_step5.sql */ /* */ /******************************************************************/ USE source; INSERT INTO sales_order VALUES (50, 1, 1, '2007-03-07', 'S', '2007-03-10', '2007-03-07', 7500, 75) , (50, 1, 1, '2007-03-07', 'R', '2007-03-10', '2007-03-07', 7500, 75) , (51, 2, 2, '2007-03-07', 'P', '2007-03-10', '2007-03-07', 1000, 10) ; /* end of script */ |
Run the script using this command.
mysql> . c:mysqlscriptssales_orders_step5.sql
Set your MySQL date to March 7, 2007, then run the dw_regular_14.sql script again.
mysql> . c:mysqlscriptsdw_regular_14.sql
Now query the two sales order in the sales_order_fact table using this statement.
mysql> select order_number, a.order_date_sk, allocate_date_sk, -> packing_date_sk, ship_date_sk, receive_date_sk -> from sales_order_fact a, order_dim b, order_date_dim c -> where order_number IN(50, 51) -> and a.order_sk = b.order_sk -> and a.order_date_sk = c.order_date_sk G
Here is the result of the query.
*************************** 1. row *************************** order_number: 50 order_date_sk: 735 allocate_date_sk: 736 packing_date_sk: 736 ship_date_sk: 737 receive_date_sk: 737 *************************** 2. row *************************** order_number: 51 order_date_sk: 735 allocate_date_sk: 736 packing_date_sk: 737 ship_date_sk: NULL receive_date_sk: NULL 2 rows in set (0.00 sec)
Note
The first order, order number 50, gets all the date_sk’s, meaning this order is completed (already received by the customer). The second order is packed, but not shipped yet.
3.138.34.226