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,PANote
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
/******************************************************************/ /* */ /* add_sales_order_quantity.sql */ /* */ /******************************************************************/ USE source; ALTER TABLE sales_order ADD order_quantity INT AFTER order_amount ; /* end of script */ |
Run the script using this command.
mysql> . c:mysqlscriptsadd_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
/******************************************************************/ /* */ /* 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 */ |
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:mysqlscriptsdw_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 GA 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. row ***************************
no: 5
name: Wonderful Shops
shipping_city: Mechanicsburg
zip: 17050
st: PA
eff: 2007-03-02
exp: 9999-12-31
*************************** 15. 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 | 558 | 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.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.