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

image from book
 /******************************************************************/ /*                                                                */ /* 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                                                  */
image from book

Make sure that your MySQL date is March 27, 2007 and run the script in Listing 23.10.

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

Next, run the production fact regular population script in Listing 23.9:

 mysql> \. c:\mysql\scripts\production_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) 



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

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

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