Preparing Data for Regular Population | Dimensional Data Warehousing with MySQL: A Tutorial

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:

Adding two new sales orders
Running the dw_regular_14.sql script and confirming correct population
Adding sales orders for the two orders with Allocate and/or Pack milestones
Running the dw_regular_14.sql script and confirming correct population
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
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:\mysql\scripts\add_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:\mysql\scripts\dw_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.

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:\mysql\scripts\sales_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:\mysql\scripts\dw_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.

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_prder 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:\mysql\scripts\sales_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:\mysql\scripts\dw_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.