Testing the Zip Code Dimension Implementation

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

 mysql> select customer_sk sk, customer_zip_code czip,        shipping_zip_code szip from customer_dim;

The query result is presented here.

 +----+-------+-------+ | sk | czip  | szip  | +----+-------+-------+ |  1 | 17050 |  NULL | |  2 | 17055 |  NULL | |  3 | 17055 |  NULL | |  4 | 17050 |  NULL | |  5 | 17050 |  NULL | |  6 | 17055 |  NULL | |  7 | 17050 |  NULL | |  8 | 17055 |  NULL | |  9 | 17055 |  NULL | | 10 | 17050 | 17050 | | 11 | 17055 | 17055 | | 12 | 17055 | 17055 | | 13 | 17050 | 17050 | | 14 | 17050 | 17050 | | 15 | 17055 | 17055 | | 16 | 17050 | 17050 | | 17 | 17055 | 17055 | | 18 | 17055 | 17055 | | 19 | 44102 | 44102 | | 20 | 44102 | 44102 | | 21 | 44102 | 44102 | | 22 | 17050 | 17050 | | 23 | 44102 | 44102 | +----+-------+-------+ 23 rows in set (0.00 sec)

Now run the sales_order_fact_23.sql script in Listing 23.4.

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

You’ll see this response on your console.

 Database changed Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 62 rows affected (0.40 sec) Records: 62  Duplicates: 0  Warnings: 0 Query OK, 62 rows affected (0.09 sec) Rows matched: 62  Changed: 62  Warnings: 0 Query OK, 26 rows affected (0.05 sec) Rows matched: 26  Changed: 26  Warnings: 0 Query OK, 23 rows affected (0.33 sec) Records: 23  Duplicates: 0  Warnings: 0 Query OK, 19 rows affected (0.42 sec) Records: 19  Duplicates: 0  Warnings: 0

You can confirm that the splitting of the zip code has been successful by querying the customer_zip_code_dim, shipping_code_dim, and sales_order fact tables:

 mysql> select customer_zip_code_sk sk, customer_zip_code zip,           customer_city city, customer_state state     -> from customer_zip_code_dim;

The query result is as follows.

 +----+-------+----------------+-------+ | 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)

The shipping zip codes should be the same as the customer zip codes. Prove it using this query.

 mysql> select shipping_zip_code_sk sk, shipping_zip_code zip,        shipping_city city, shipping_state sta     -> from shipping_zip_code_dim;

The query result is should be the same as the following.

 +----+-------+----------------+-------+ | 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)

Now, query the sales orders:

 mysql> select order_date_sk odsk, customer_sk csk,     -> customer_zip_code_sk czsk, shipping_zip_code_sk szsk     -> from sales_order_fact order by order_date_sk;

You should get the following result.

 +------+------+------+------+ | odsk | csk  | czsk | szsk | +------+------+------+------+ |    1 |    3 |    6 | NULL | |   46 |    4 |    1 | NULL | |   81 |    5 |    1 | NULL | |  152 |    6 |    6 | NULL | |  185 |    7 |    1 | NULL | |  255 |    1 |    1 | NULL | |  311 |    2 |    6 | NULL | |  347 |    3 |    6 | NULL | |  380 |    4 |    1 | NULL | |  416 |    5 |    1 | NULL | |  456 |    6 |    6 | NULL | |  458 |    7 |    1 | NULL | |  502 |    1 |    1 | NULL | |  548 |    2 |    6 | NULL | |  554 |    3 |    6 | NULL | |  584 |    4 |    1 | NULL | |  681 |    5 |    1 | NULL | |  722 |    6 |    6 | NULL | |  727 |    6 |    6 | NULL | |  730 |    7 |    1 | NULL | |  731 |    4 |    1 | NULL | |  731 |    3 |    6 | NULL | |  731 |    5 |    1 | NULL | |  731 |    8 |    6 | NULL | |  731 |    7 |    1 | NULL | |  731 |    2 |    6 | NULL | |  731 |    9 |    6 | NULL | |  731 |    1 |    1 | NULL | |  731 |    9 |    6 | NULL | |  731 |    1 |    1 | NULL | |  731 |    3 |    6 | NULL | |  731 |    2 |    6 | NULL | |  731 |    4 |    1 | NULL | |  731 |    5 |    1 | NULL | |  731 |    8 |    6 | NULL | |  731 |    7 |    1 | NULL | |  732 |   18 |    6 |    6 | |  732 |   17 |    6 |    6 | |  732 |   15 |    6 |    6 | |  732 |   16 |    1 |    1 | |  732 |   14 |    1 |    1 | |  732 |   13 |    1 |    1 | |  732 |   12 |    6 |    6 | |  732 |   11 |    6 |    6 | |  732 |   10 |    1 |    1 | |  734 |   12 |    6 |    6 | |  734 |   11 |    6 |    6 | |  734 |   10 |    1 |    1 | |  735 |   10 |    1 |    1 | |  735 |   11 |    6 |    6 | |  741 |   10 |    1 |    1 | |  741 |   11 |    6 |    6 | |  746 |   23 |    7 |    7 | |  746 |   22 |    1 |    1 | |  746 |   21 |    7 |    7 | |  746 |   20 |    7 |    7 | |  746 |   13 |    1 |    1 | |  746 |   12 |    6 |    6 | |  746 |   11 |    6 |    6 | |  746 |   10 |    1 |    1 | |  756 |   21 |    7 |    7 | |  756 |   21 |    7 |    7 | +------+------+------+------+ 62 rows in set (0.00 sec) 

Note 

The early sales orders do not have shipping zip codes (NULL) because the customers didn’t have shipping zip codes at the time they placed the sales orders. You added the shipping zip codes in Chapter 10, “Adding Columns.”