Testing the Revised Regular Population

Now you can test the script in Listing 12.5. Before you do this, add some customer data by running the script in Listing 12.6 to add one PA customer and one OH customer into the customer dimension.

Listing 12.6: Adding two customers

/******************************************************************/ /* */ /* two_more_customers.sql */ /* */ /******************************************************************/ /* default to dw */ USE dw; INSERT INTO customer_dim ( customer_sk , customer_number , customer_name , customer_street_address , customer_zip_code , customer_city , customer_state , shipping_address , shipping_zip_code , shipping_city , shipping_state , effective_date , expiry_date ) VALUES (NULL, 13, 'PA Customer', '1111 Louise Dr.

'

, '17050', 'Mechanicsburg', 'PA', '1111 Louise Dr.', '17050', 'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31') , (NULL, 14, 'OH Customer', '6666 Ridge Rd.', '44102', 'Cleveland', 'OH', '6666 Ridge Rd.', '44102', 'Cleveland', 'OH', CURRENT_DATE, '9999-12-31') ; /* end of script */

Now run the script in Listing 12.6.

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

MySQL should indicate that there are two rows affected.

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

Now change your MySQL date to March 3, 2007 so that old data will not get re-loaded and run the dw_regular_12.sql script by issuing this command.

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

You should see the following response on your console.

Database changed Query OK, 9 rows affected (0.15 sec) Query OK, 9 rows affected (0.14 sec) Records: 9 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 0 rows affected (0.05 sec) Rows matched: 0 Changed: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Records: 0 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) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 18 rows affected (0.04 sec) Query OK, 19 rows affected (0.06 sec) Records: 19 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.09 sec) Query OK, 4 rows affected (0.07 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 0 rows affected (0.06 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.15 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0

Now query the pa_customer_dim table using this statement. You will see that only the new PA customer was inserted into the table.

mysql> select customer_name, customer_state, effective_date -> from pa_customer_dim;

Here is the query result.

+-------------------------+----------------+---------------+  customer_name  customer_state  effective_date +-------------------------+----------------+---------------+  Really Large Customers  PA  2005-03-01   Small Stores  PA  2005-03-01   Medium Retailers  PA  2005-03-01   Good Companies  PA  2005-03-01   Wonderful Shops  PA  2005-03-01   Extremely Loyal Clients  PA  2005-03-01   Distinguished Agencies  PA  2005-03-01   Extremely Loyal Clients  PA  2007-03-01   Subsidiaries  PA  2007-03-01   Really Large Customers  PA  2007-03-02   Small Stores  PA  2007-03-02   Medium Retailers  PA  2007-03-02   Good Companies  PA  2007-03-02   Wonderful Shops  PA  2007-03-02   Extremely Loyal Clients  PA  2007-03-02   Distinguished Agencies  PA  2007-03-02   Subsidiaries  PA  2007-03-02   Online Distributors  PA  2007-03-02   PA Customer  PA  2007-03-03  +-------------------------+----------------+---------------+ 19 rows in set (0.00 sec)