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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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.



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net