Testing the Revised Production Regular Population Script
Before you can run the script in Listing 23.9, you need to prepare the factory source and the daily production data source. The following factory.csv file contains the new factory that needs to be added to the factory dimension when you run the production_regular_23.sql script.
FACTORY_CODE,NAME,STREET_ADDRESS,ZIP_CODE,CITY,STATE 5,Fifth Factory,90909 McNicholds Blvd.,17055,Pittsburgh,PA
Then, add three daily production records into the daily_production table using the script in Listing 23.10.
Listing 23.10. Adding three daily production records
/******************************************************************/ /* */ /* daily_production_23.sql */ /* */ /******************************************************************/ USE source; INSERT INTO daily_production VALUES ( 1, CURRENT_DATE, 3, 400 ) , ( 3, CURRENT_DATE, 4, 200 ) , ( 5, CURRENT_DATE, 5, 100 ) ; /* end of script */ |
Make sure that your MySQL date is March 27, 2007 and run the script in Listing 23.10.
mysql> . c:mysqlscriptsdaily_production_23.sql
Next, run the production fact regular population script in Listing 23.9:
mysql> . c:mysqlscriptsproduction_regular_23.sql
You will see the following response on the console.
Database changed Query OK, 1 row affected (0.06 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Rows matched: 4 Changed: 0 Warnings: 0 Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0
To confirm correct population, query the factory_dim and the sales_order_fact tables.
mysql> select * from factory_dim G
Here is the result.
*************************** 1. row ***************************
factory_sk: 1
factory_code: 1
factory_name: First Factory
factory_street_address: 11111 Lichtman St.
effective_date: 2007-03-18
expiry_date: 9999-12-31
*************************** 2. row ***************************
factory_sk: 2
factory_code: 2
factory_name: Second Factory
factory_street_address: 24242 Bunty La.
effective_date: 2007-03-18
expiry_date: 9999-12-31
*************************** 3. row ***************************
factory_sk: 3
factory_code: 3
factory_name: Third Factory
factory_street_address: 37373 Burbank Dr.
effective_date: 2007-03-18
expiry_date: 9999-12-31
*************************** 4. row ***************************
factory_sk: 4
factory_code: 4
factory_name: Fourth Factory
factory_street_address: 44444 Jenzen Blvd.
effective_date: 2007-03-18
expiry_date: 9999-12-31
*************************** 5. row ***************************
factory_sk: 5
factory_code: 5
factory_name: Fifth Factory
factory_street_address: 90909 McNicholds Blvd.
effective_date: 2007-03-27
expiry_date: 9999-12-31
5 rows in set (0.00 sec)Note
The fifth factory was correctly added.
Query the production_fact table to confirm that the three new daily productions are correctly loaded:
mysql> select product_sk psk, production_date_sk pdsk,
-> factory_sk fsk, factory_zip_code_sk fzsk, production_quantity
qty
-> from production_fact;You should see the following result on your console.
+------+------+------+------+------+ | psk | pdsk | fsk | fzsk | qty | +------+------+------+------+------+ | 1 | 748 | 4 | 6 | 100 | | 2 | 748 | 3 | 1 | 200 | | 4 | 748 | 2 | 6 | 300 | | 5 | 748 | 1 | 1 | 400 | | 1 | 748 | 1 | 1 | 400 | | 2 | 748 | 2 | 6 | 300 | | 4 | 748 | 3 | 1 | 200 | | 5 | 748 | 4 | 6 | 100 | | 6 | 757 | 3 | 1 | 400 | | 4 | 757 | 4 | 6 | 200 | | 7 | 757 | 5 | 6 | 100 | +------+------+------+------+------+ 11 rows in set (0.00 sec)