Testing


Before you can run the script in Listing 10.3, there are a few things you need to do.

First, prepare the customer data as shown below and save it as customer.csv. The changes from the previous customer data are:

  • The shipping address data of all existing eight customers are available

  • Customer number 9 is a new customer, which has its shipping address data

 CUSTOMER NO, CUSTOMER NAME, STREET ADDRESS, ZIP CODE, CITY, STATE, SHIPPING ADDRESS, ZIP CODE, CITY, STATE 1, Really Large Customers, 7500 Louise Dr., 17050, Mechanicsburg, PA, 7500         Louise Dr., 17050, Mechanicsburg, PA 2, Small Stores, 2500 Woodland St., 17055, Pittsburgh, PA, 2500 Woodland         St., 17055, Pittsburgh, PA 3, Medium Retailers, 1111 Ritter Rd., 17055, Pittsburgh, PA, 1111 Ritter         Rd., 17055, Pittsburgh, PA 4, Good Companies, 9500 Scott St., 17050, Mechanicsburg, PA, 9500 Scott         St., 17050, Mechanicsburg, PA 5, Wonderful Shops, 3333 Rossmoyne Rd., 17050, Mechanicsburg, PA, 3333         Rossmoyne Rd., 17050, Mechanicsburg, PA 6, Extremely Loyal Clients, 7777 Ritter Rd., 17055, Pittsburgh, PA, 7777         Ritter Rd., 17055, Pittsburgh, PA 7, Distinguished Agencies, 9999 Scott St., 17050, Mechanicsburg, PA, 9999         Scott St., 17050, Mechanicsburg, PA 8, Subsidiaries, 10000 Wetline Blvd., 17055, Pittsburgh, PA, 10000 Wetline         Blvd., 17055, Pittsburgh, PA 9, Online Distributors, 2323 Louise Dr., 17055, Pittsburgh, PA, 2323         Louise Dr., 17055, Pittsburgh, PA

Note 

You will need the product.txt file used in the previous test even though there is no change to the file. This is because regular population must access all source data whenever it runs, and this includes the product.txt file.

The second thing you need to do is add the order_quantity column to the sales_order table in the source database using the sales_order_quantity_data.sql script in Listing 10.4. The data in this new column is the source for the new order_quantity column in the sales_order_fact table.

Listing 10.4: Adding the order_quantity column to the sales_order table

image from book
 /*****************************************************************/ /*                                                              */ /* add_sales_order_quantity.sql                                 */ /*                                                              */ /*****************************************************************/ USE source; ALTER TABLE sales_order   ADD order_quantity INT AFTER order_amount ; /* end of script                                                */ 
image from book

Run the script using this command.

 mysql> \. c:\mysql\scripts\add_sales_order_quantity.sql

You’ll see this on your MySQL console.

 Database changed Query OK, 37 rows affected (0.39 sec) Records: 37  Duplicates: 0  Warnings: 0

Now that the sales order source has the order_quantity column, you can add sales order test data. The script in Listing 10.5 adds nine sales orders into the sales_order table. Note that these sales orders have order quantities and their order dates are March 2, 2005.

Listing 10.5: Adding nine sales orders with order quantities

image from book
 /*****************************************************************/ /*                                                              */ /* sales_order_quantity_data.sql                                */ /*                                                              */ /*****************************************************************/ USE source; INSERT INTO sales_order VALUES   (38, 1, 1, '2007-03-02', '2007-03-02', 1000, 10) , (39, 2, 2, '2007-03-02', '2007-03-02', 2000, 20) , (40, 3, 3, '2007-03-02', '2007-03-02', 4000, 40) , (41, 4, 4, '2007-03-02', '2007-03-02', 6000, 60) , (42, 5, 1, '2007-03-02', '2007-03-02', 2500, 25) , (43, 6, 2, '2007-03-02', '2007-03-02', 5000, 50) , (44, 7, 3, '2007-03-02', '2007-03-02', 7500, 75) , (45, 8, 4, '2007-03-02', '2007-03-02', 1000, 10) , (46, 9, 1, '2007-03-02', '2007-03-02', 1000, 10) ; /* end of script                                                */
image from book

Next, you need to set your MySQL date to the order date of your test data, which is March 2, 2007.

Now, you are ready to run the dw_regular_10.sql script in Listing 10.3. You can invoke it using this command.

 mysql> \. c:\mysql\scripts\dw_regular_10.sql

Here is what you'll see on the console.

 Database changed Query OK, 8 rows affected (0.05 sec) Query OK, 9 rows affected (0.06 sec) Records: 9  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 8 rows affected (0.06 sec) Rows matched: 8  Changed: 8  Warnings: 0 Query OK, 8 rows affected (0.05 sec) Records: 8  Duplicates: 0  Warnings: 0 Query OK, 0 rows affected (0.01 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 1 row affected (0.07 sec) Records: 1  Duplicates: 0  Warnings: 0 Query OK, 4 rows affected (0.05 sec) Query OK, 4 rows affected (0.06 sec) Records: 4  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 9 rows affected (0.07 sec) Records: 9  Duplicates: 0  Warnings: 0 Query OK, 9 rows affected (0.10 sec) Records: 9  Duplicates: 0  Warnings: 0

You can confirm if the revised regular population script was successfully executed by querying the customer dimension using this SQL statement.

 mysql> select customer_number no, customer_name name,     -> shipping_city, shipping_zip_code zip, shipping_state st,     -> effective_date eff, expiry_date exp     -> from customer_dim \G

A successful execution of the revised regular population script gives you the following result.

 ******************************1.row************************            no: 1          name: Really Large Customers shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-03-01 ******************************2. row *************************            no: 2          name: Small Stores shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-03-01 ******************************3. row *************************            no: 3          name: Medium Retailers shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-03-01 ******************************4. row **************************            no: 4          name: Good Companies shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-03-01 ******************************5. row ***************************            no: 5          name: Wonderful Shops shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-03-01 ******************************6. row ***************************            no: 6          name: Extremely Loyal Clients shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-02-28 *************************** 7. row ***************************            no: 7          name: Distinguished Agencies shipping_city: NULL           zip: NULL            st: NULL           eff: 2005-03-01           exp: 2007-03-01 *************************** 8. row ***************************            no: 6          name: Extremely Loyal Clients shipping_city: NULL           zip: NULL            st: NULL           eff: 2007-03-01           exp: 2007-03-01 *************************** 9. row ***************************            no: 8          name: Subsidiaries shipping_city: NULL           zip: NULL            st: NULL           eff: 2007-03-01           exp: 2007-03-01 *************************** 10. row ***************************            no: 1          name: Really Large Customers shipping_city: Mechanicsburg           zip: 17050            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 11. row ***************************            no: 2          name: Small Stores shipping_city: Pittsburgh           zip: 17055            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 12. row ***************************            no: 3          name: Medium Retailers shipping_city: Pittsburgh           zip: 17055            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 13. row ***************************            no: 4          name: Good Companies shipping_city: Mechanicsburg           zip: 17050            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 14. 4 row ***************************            no: 5          name: Wonderful Shops shipping_city: Mechanicsburg           zip: 17050            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 15. 5 row ***************************            no: 6          name: Extremely Loyal Clients shipping_city: Pittsburgh           zip: 17055            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 16. row ***************************            no: 7          name: Distinguished Agencies shipping_city: Mechanicsburg           zip: 17050            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 17. row ***************************            no: 8          name: Subsidiaries shipping_city: Pittsburgh           zip: 17055            st: PA           eff: 2007-03-02           exp: 9999-12-31 *************************** 18. row ***************************            no: 9          name: Online Distributors shipping_city: Pittsburgh           zip: 17055            st: PA           eff: 2007-03-02           exp: 9999-12-31 18 rows in set (0.00 sec) 

Note 

The new records of all existing customers have shipping addresses. The older (expired) records do not. Customer number 9 is added and it has a shipping address.

To confirm that the sales data has been populated successfully, query the sales_order_fact table using this statement.

 mysql> select order_sk o_sk, customer_sk c_sk, product_sk p_sk,        order_date_sk od_sk,     -> order_amount amt, order_quantity qty     -> from sales_order_fact;

Here is the content of the fact table.

 +------+------+------+-------+---------+------+ | o_sk | c_sk | p_sk | od_sk | amt     | qty  | +------+------+------+-------+---------+------+ |    1 |    3 |    3 |     1 | 4000.00 | NULL | |    2 |    4 |    1 |    46 | 4000.00 | NULL | |    3 |    5 |    2 |    81 | 6000.00 | NULL | |    4 |    6 |    3 |   152 |  6000.00| NULL | |    5 |    7 |    1 |   185 | 8000.00 | NULL | |    6 |    1 |    2 |   255 | 8000.00 | NULL | |    7 |    2 |    3 |   311 | 1000.00 | NULL | |    8 |    3 |    1 |   347 | 1000.00 | NULL | |    9 |    4 |    2 |   380 | 2000.00 | NULL | |   10 |    5 |    3 |   416 | 2500.00 | NULL | |   11 |    6 |    1 |   456 | 3000.00 | NULL | |   12 |    7 |    2 |   458 | 3500.00 | NULL | |   13 |    1 |    3 |   502 | 4000.00 | NULL | |   14 |    2 |    1 |   548 | 4500.00 | NULL | |   15 |    3 |    2 |   554 | 1000.00 | NULL | |   16 |    4 |    3 |   584 | 1000.00 | NULL | |   17 |    5 |    1 |   681 | 4000.00 | NULL | |   18 |    6 |    2 |   722 | 4000.00 | NULL | |   19 |    7 |    3 |   730 | 4000.00 | NULL | |   20 |    1 |    1 |   731 | 1000.00 | NULL | |   21 |    2 |    2 |   731 | 2000.00 | NULL | |   22 |    3 |    4 |   731 | 3000.00 | NULL | |   23 |    4 |    5 |   731 | 4000.00 | NULL | |   24 |    5 |    2 |   731 | 1000.00 | NULL | |   25 |    8 |    2 |   731 | 3000.00 | NULL | |   26 |    7 |    4 |   731 | 5000.00 | NULL | |   27 |    9 |    5 |   731 | 7000.00 | NULL | |   28 |    1 |    1 |   731 | 1000.00 | NULL | |   29 |    2 |    2 |   731 | 2000.00 | NULL | |   30 |    3 |    4 |   731 | 4000.00 | NULL | |   31 |    4 |    5 |   731 | 6000.00 | NULL | |   32 |    5 |    1 |   731 | 2500.00 | NULL | |   33 |    8 |    2 |   731 | 5000.00 | NULL | |   34 |    7 |    4 |   731 | 7500.00 | NULL | |   35 |    9 |    5 |   731 | 1000.00 | NULL | |   36 |   10 |    1 |   732 | 1000.00 |   10 | |   37 |   11 |    2 |   732 | 2000.00 |   20 | |   38 |   12 |    4 |   732 | 4000.00 |   40 | |   39 |   13 |    5 |   732 | 6000.00 |   60 | |   40 |   14 |    1 |   732 | 2500.00 |   25 | |   41 |   15 |    2 |   732 | 5000.00 |   50 | |   42 |   16 |    4 |   732 | 7500.00 |   75 | |   43 |   17 |    5 |   732 | 1000.00 |   10 | |   44 |   18 |    1 |   732 | 1000.00 |   10 | +------+------+------+-------+---------+------+ 44 rows in set (0.00 sec)

Note 

Only the nine new orders have an order quantity. Older sales data does not.



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

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