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
/******************************************************************/
/* */
/* 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 */ |
Run the script in Listing 19.7 using this command.
mysql> . c:mysqlscriptsfactory_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:mysqlscriptsfactory_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
/******************************************************************/ /* */ /* 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 */ |
Run the script in Listing 19.8 by using this command.
mysql> . c:mysqlscriptsdaily_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:mysqlscriptsproduction_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.