Testing the Factory Zip Codes

Before you run the product_fact_23.sql script in Listing 23.5 to modify the data warehouse structure, I’d like you to query the factory_dim table using this statement.

 mysql> select factory_sk, factory_zip_code from factory_dim;

Here is the query result.

 +------------+----------------------+ | factory_sk | customer_zip_code_sk | +------------+----------------------+ |          1 |                17050 | |          2 |                17055 | |          3 |                17050 | |          4 |                17055 | +------------+----------------------+

Now run the production_fact_23.sql script in Listing 23.5.

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

You will see something similar to this on your console.

 Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 8 rows affected (0.46 sec) Records: 8  Duplicates: 0  Warnings: 0 Query OK, 8 rows affected (0.09 sec) Rows matched: 8  Changed: 8  Warnings: 0 Query OK, 2 rows affected (0.13 sec) Query OK, 0 rows affected (0.32 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 4 rows affected (0.06 sec) Records: 4  Duplicates: 0  Warnings: 0 Query OK, 4 rows affected (0.35 sec) Records: 4  Duplicates: 0  Warnings: 0

If you query the factory_zip_code_dim table, it will return the seven zip codes in the factory_zip_code_dim table.

 mysql> select factory_zip_code_sk sk, factory_zip_code zip,        factory_city city, factory_state state     -> from factory_zip_code_dim; +----+-------+----------------+-------+ | sk | zip   | city           | state | +----+-------+----------------+-------+ |  1 | 17050 | PITTSBURGH     | PA    | |  2 | 17051 | MC VEYTOWN     | PA    | |  3 | 17052 | MAPLETON DEPOT | PA    | |  4 | 17053 | MARYSVILLE     | PA    | |  5 | 17054 | MATTAWANA      | PA    | |  6 | 17055 | MECHANICSBURG  | PA    | |  7 | 44102 | CLEVELAND      | OH    | +----+-------+----------------+-------+ 7 rows in set (0.00 sec)

To confirm that the factory_zip_code_sk column in the prodction_fact table has been populated correctly, send this statement to MySQL.

 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 the console.

 +------+------+------+------+------+ | psk  | pdsk | fsk  | fzsk | qty  | +------+------+------+------+------+ |    1 | 711  |    4 |    6 | 100  | |    2 | 711  |    3 |    1 | 200  | |    4 | 711  |    2 |    6 | 300  | |    5 | 711  |    1 |    1 | 400  | |    1 | 711  |    1 |    1 | 400  | |    2 | 711  |    2 |    6 | 300  | |    4 | 711  |    3 |    1 | 200  | |    5 | 711  |    4 |    6 | 100  | +------+------+------+------+------+ 8 rows in set (0.00 sec)