Testing


Now that we’ve discussed all the tables in the third star, we're ready to do some testing.

First of all, you need some factories. The script in Listing 19.7 loads four factories into the factory_master table in the source database.

Listing 19.7: Factories in the factory_master source table

image from book
 /**********************************************************************/ /*                                                                    */ /* factory_master_source.sql                                          */ /*                                                                    */ /**********************************************************************/ USE source; INSERT INTO factory_master VALUES   ( 1, 'First Factory', '11111 Lichtman St.', 17050,        'Mechanicsburg', 'PA' ) , ( 2, 'Second Factory', '22222 Stobosky Ave.', 17055, 'Pittsburgh',        'PA' ) , ( 3, 'Third Factory', '33333 Fritze Rd.', 17050, 'Mechanicsburg',        'PA' ) , ( 4, 'Fourth Factory', '44444 Jenzen Blvd.', 17055, 'Pittsburgh',        'PA' ) ; /* end of script                                                      */
image from book

Run the script in Listing 19.7 using this command.

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

You’ll see the following on your console.

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

After you run the script in Listing 19.7, you need to set your MySQL date to any date later than the date you set in Chapter 18, “Junk Dimensions.” (In Chapter 18 you set your MySQL date to March 16, 2007) To follow the exercise in this chapter, however, you must set your MySQL date to March 18, 2007. After that, run the factory_ini.sql script in Listing 19.3 to load the four factories in the factory_master table to the factory_dim table. You can invoke the factory_ini.sql script using this command.

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

MySQL will indicate that 4 records are affected by the query.

 Database changed Query OK, 4 rows affected (0.04 sec) Records: 4  Duplicates: 0  Warnings: 0

Now query the factory_dim table to confirm correct population using this statement.

 mysql> select * from factory_dim \G

Here is the query result.

 *************************** 1. row ***************************             factory_sk: 1           factory_code: 1           factory_name: First Factory factory_street_address: 11111 Lichtman St.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA         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: 22222 Stobosky Ave.        factory_zip_code: 17055            factory_city: Pittsburgh           factory_state: PA          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: 33333 Fritze Rd.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA         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.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA         effective_date: 2007-03-18            expiry_date: 9999-12-31 4 rows in set (0.00 sec)

Next, prepare the factory.csv file below.

 FACTORY_CODE,NAME,STREET_ADDRESS,ZIP_CODE,CITY,STATE 2,Second Factory,24242 Bunty La.,17055,Pittsburgh,PA 3,Third Factory,37373 Burbank Dr.,17050,Mechanicsburg,PA

Note 

This CSV file contains the changes to factory codes 2 and 3 to test the SCD1 on the factory dimension.

You can use the script in Listing 19.8 to load data to the daily_production table in the source database.

Listing 19.8: Daily production data

image from book
 /****************************************************************/ /*                                                              */ /* daily_production_data.sql                                    */ /*                                                              */ /****************************************************************/ USE source; INSERT INTO daily_production VALUES   (1, CURRENT_DATE, 4, 100 ) , (2, CURRENT_DATE, 3, 200 ) , (3, CURRENT_DATE, 2, 300 ) , (4, CURRENT_DATE, 1, 400 ) , (1, CURRENT_DATE, 1, 400 ) , (2, CURRENT_DATE, 2, 300 ) , (3, CURRENT_DATE, 3, 200 ) , (4, CURRENT_DATE, 4, 100 ) ; /* end of script                                               */
image from book

Run the script in Listing 19.8 by using this command.

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

Here is what should be printed on your console.

 Database changed Query OK, 8 rows affected (0.05 sec) Records: 8  Duplicates: 0  Warnings: 0

Now you’re ready to test the production regular loading. Your MySQL date must have been set to March 18, 2005 before you run the production_regular.sql script. To run the script, use the following command.

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

Here is the response from MySQL.

 Database changed Query OK, 1 rows affected (0.07 sec) Query OK, 2 rows affected (0.03 sec) Records: 2  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 2 rows affected (0.00 sec) Rows matched: 2  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 8 rows affected (0.07 sec) Records: 8  Duplicates: 0  Warnings: 0

Using the following SQL statement, query the production_fact table to confirm a successful regular load of the daily production data.

 mysql> select * from production_fact;

Here is the query result.

 +-----------+-------------------+------------+---------------------+ | product_sk| production_date_sk| factory_sk | production_quantity | +-----------+-------------------+------------+---------------------+ |         1 |               748 |          4 |                 100 | |         2 |               748 |          3 |                 200 | |         4 |               748 |          2 |                 300 | |         5 |               748 |          1 |                 400 | |         1 |               748 |          1 |                 400 | |         2 |               748 |          2 |                 300 | |         4 |               748 |          3 |                 200 | |         5 |               748 |          4 |                 100 | +-----------+-------------------+------------+---------------------+ 8 rows in set (0.00 sec)

To confirm that SCD1 has been applied successfully on the factory dimension, query the factory_dim table using this statement.

 mysql> select * from factory_dim \G

You will see the following records as the result.

 *************************** 1. row ***************************             factory_sk: 1           factory_code: 1           factory_name: First Factory factory_street_address: 11111 Lichtman St.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA         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.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA         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.       factory_zip_code: 17050           factory_city: Mechanicsburg          factory_state: PA         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.       factory_zip_code: 17055           factory_city: Pittsburgh          factory_state: PA         effective_date: 2007-03-18            expiry_date: 9999-12-31 4 rows in set (0.00 sec)

Note 

The Second and Third factories have their addresses changed correctly.



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