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:mysqlscriptsproduction_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)
3.143.0.85