Testing the Revised Regular Population Script

Before you can run the revised regular population script, there are a few things you need to prepare. First, you need to prepare the following customer.csv file, which contains two changes from the customer.csv in Chapter 10:

  • The street and shipping addresses of customer number 4 changes from zip code 17050 to 17055

  • A new customer number 15 is added.

Here is the content of the revised customer.csv file.

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,9999 Louise Dr.,17055,Pittsburgh,PA,9999 Louise
       Dr.,17055,Pittsburgh,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,E-Distributors,2323 Louise Dr.,17055,Pittsburgh,PA,2323 Louise
       Dr.,17055,Pittsburgh,PA
10,Bigger Customers,7777 Ridge Rd.,44102,Cleveland,OH,7777 Ridge
       Rd.,44102,Cleveland,OH
11,Smaller Stores,8888 Jennings Fwy.,44102,Cleveland,OH,8888
       Jennings Fwy.,44102,Cleveland,OH
12,Small-Medium Retailers,9999 Memphis Ave.,44102,Cleveland,OH,9999
       Memphis Ave.,44102,Cleveland,OH
13,PA Customer,1111 Louise Dr.,17050,Mechanicsburg,PA,1111 Louise
       Dr.,17050,Mechanicsburg,PA
14,OH Customer,6666 Ridge Rd.,44102,Cleveland,OH,6666 Ridge
       Rd.,44102,Cleveland,OH
15,Super Stores,1000 Woodland St.,17055,Pittsburgh,PA,1000 Woodland
       St.,17055,Pittsburgh,PA

Now query the latest customer and shipping zip codes before you load the new customer data. Later you can compare this query output with the one after the changes.

mysql> SELECT order_date_sk odsk, customer_number cn,
    -> customer_zip_code czc, shipping_zip_code szc
    -> FROM customer_zip_code_dim a, shipping_zip_code_dim b,
    -> sales_order_fact c, customer_dim d
    -> WHERE a.customer_zip_code_sk = c.customer_zip_code_sk
    -> AND b.shipping_zip_code_sk = c.shipping_zip_code_sk
    -> AND d.customer_sk = c.customer_sk
    -> GROUP BY customer_number
    -> HAVING MAX(order_date_sk);

+------+------+-------+-------+
| odsk | cn   | czc   | szc   |
+------+------+-------+-------+
|  732 |    1 | 17050 | 17050 |
|  732 |    2 | 17055 | 17055 |
|  732 |    3 | 17055 | 17055 |
|  732 |    4 | 17050 | 17050 |
|  732 |    5 | 17050 | 17050 |
|  732 |    6 | 17055 | 17055 |
|  732 |    7 | 17050 | 17050 |
|  732 |    8 | 17055 | 17055 |
|  732 |    9 | 17055 | 17055 |
|  746 |   11 | 44102 | 44102 |
|  746 |   12 | 44102 | 44102 |
|  746 |   13 | 17050 | 17050 |
|  746 |   14 | 44102 | 44102 |
+------+------+-------+-------+
13 rows in set (0.42 sec)

Next, use the script in Listing 23.7 to add two sales orders.

  • customer number 4 whose address has recently changed

  • new customer number 15

Listing 23.7. Adding two sales orders
/******************************************************************/
/*                                                                */
/* sales_order_23.sql                                             */
/*                                                                */
/******************************************************************/

USE source;

INSERT INTO sales_order VALUES

  (64, 4, 3, 'Y', 'Y', 'Y', 'N', '2007-03-27', 'N', '2007-03-31',
       '2007-03-27', 10000, 100)
, (65, 15, 4, 'Y', 'N', 'Y', 'N', '2007-03-27', 'N', '2007-03-31',
       '2007-03-27', 20000, 200)
;

/* end of script                                                  */

Run the script in Listing 23.7 using this command.

mysql> . c:mysqlscriptssales_order_23.sql

You will see the following response on the console.

Database changed
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

You’re now ready to test the revised regular population. You must set your MySQL date to March 27, 2007 (the entry date of the two sales orders above) and run the dw_regular_23 script:

mysql> . c:mysqlscriptsdw_regular_23.sql

You should see something similar to the following on your console.

Database changed
Query OK, 9 rows affected (0.10 sec)

Query OK, 15 rows affected (0.05 sec)
Records: 15  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected (0.07 sec)

Query OK, 5 rows affected (0.06 sec)
Records: 5  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, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.16 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 19 rows affected (0.05 sec)

Query OK, 21 rows affected (0.08 sec)
Records: 21  Duplicates: 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)
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Confirm that the two customer changes, customer number 4 and 15, have been loaded correctly by querying the customer_dim table.

mysql> select * from customer_dim where customer_number in (4,15) G

Here is the query result.

*************************** 1. row ***************************
            customer_sk: 4
        customer_number: 4
          customer_name: Good Companies
customer_street_address: 9500 Scott St.
       shipping_address: NULL
         effective_date: 2005-03-01
            expiry_date: 2007-03-01
*************************** 2. row ***************************
            customer_sk: 13
        customer_number: 4
          customer_name: Good Companies
customer_street_address: 9500 Scott St.
       shipping_address: 9500 Scott St.
         effective_date: 2007-03-02
            expiry_date: 2007-03-26
*************************** 3. row ***************************
            customer_sk: 24
        customer_number: 4
          customer_name: Good Companies
customer_street_address: 9999 Louise Dr.
       shipping_address: 9999 Louise Dr.
         effective_date: 2007-03-27
            expiry_date: 9999-12-31
*************************** 4. row ***************************
            customer_sk: 25
        customer_number: 15
          customer_name: Super Stores
customer_street_address: 1000 Woodland St.
       shipping_address: 1000 Woodland St.
         effective_date: 2007-03-27
            expiry_date: 9999-12-31
4 rows in set (0.00 sec)

To confirm the zip codes have been correctly loaded, query the sales_order_fact table on the two new sales orders using this SQL statement.

mysql> select * from sales_order_fact where order_number IN(64,65)
       G

You should get the following result.

*************************** 1. row ***************************
             customer_sk: 24
    customer_zip_code_sk: 6
    shipping_zip_code_sk: 6
              product_sk: 4
sales_order_attribute_sk: 3
           order_date_sk: 757
        allocate_date_sk: NULL
         packing_date_sk: NULL
            ship_date_sk: NULL
         receive_date_sk: NULL
           entry_date_sk: 757
            order_number: 64
request_delivery_date_sk: 761
            order_amount: 10000.00
          order_quantity: 100
       allocate_quantity: NULL
        packing_quantity: NULL
           ship_quantity: NULL
        receive_quantity: NULL
*************************** 2. row ***************************
             customer_sk: 25
    customer_zip_code_sk: 6
    shipping_zip_code_sk: 6
              product_sk: 5
sales_order_attribute_sk: 5
           order_date_sk: 757
        allocate_date_sk: NULL
         packing_date_sk: NULL
            ship_date_sk: NULL
         receive_date_sk: NULL
           entry_date_sk: 757
            order_number: 65
request_delivery_date_sk: 761
            order_amount: 20000.00
          order_quantity: 200
       allocate_quantity: NULL
        packing_quantity: NULL
           ship_quantity: NULL
        receive_quantity: NULL
2 rows in set (0.00 sec)

Note

The output confirms correct population of the sales_order_fact table. The zip_code_sk 6 is Mechanicsburg, which is the correct zip code of the customer and shipping addresses.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.148.144.228