Preparing Data for Regular Population
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.
Step 1: Adding Two New Sales Orders
The script in Listing 14.7 adds two new orders placed on March 5, 2007.
Listing 14.7. Adding two sales orders
/******************************************************************/
/* */
/* 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
Step 2: Running the DW Regular Population Script
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 GHere 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.
Step 3: Adding Three Sales Orders with Allocate and Packing Dates
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.
Listing 14.8. Adding three sales orders with Allocate and/or Packing dates
/******************************************************************/
/* */
/* 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
Step 4: Running the DW Regular Population Script
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 GHere 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.
Step 5: Addding Three Sales Orders with Ship, Receive, and Packing Dates
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).
Listing 14.9. Sales orders with Allocate and/or Packing dates
/******************************************************************/
/* */
/* 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
Step 6: Running the DW Regular Population Script
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 GHere 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.