Chapter 10: Adding Columns

This chapter teaches you the most commonly encountered extension of a starter data warehouse: adding columns to an existing dimension and the fact table. This chapter starts by discussing what should happen to the schema if you need to add a new column or two. It then proceeds by demonstrating how you can add a new column to the customer dimension and the sales order fact and apply SCD2 to the new column.

Enhancing the Schema

In this section I show you how to revise the data warehouse schema. Figure 10.1 shows the enhanced schema with new columns in the customer_dim table and the sales_order_fact table. The new columns in customer_dim are shipping_address, shipping_zip_code, shipping_city, and shipping_state. The sales_order_fact table has one new column: order_quantity.

The shipping_address.sql script in Listing 10.1 adds the new columns in the customer_dim and customer_stg tables. Before you run the script, set your MySQL date to March 1, 2007.

Figure 10.1: New columns in customer_dim and sales_order_fact

Listing 10.1: Adding new columns to the customer dimension

 /*****************************************************************/ /*                                                               */ /* shipping_address.sql                                          */ /*                                                               */ /*****************************************************************/ USE dw; ALTER TABLE customer_dim   ADD shipping_address CHAR (50) AFTER customer_state , ADD shipping_zip_code INT (5) AFTER shipping_address , ADD shipping_city CHAR (30) AFTER shipping_zip_code , ADD shipping_state CHAR (2) AFTER shipping_city ; ALTER TABLE customer_stg   ADD shipping_address CHAR (50) AFTER customer_state , ADD shipping_zip_code INT (5) AFTER shipping_address , ADD shipping_city CHAR (30) AFTER shipping_zip_code , ADD shipping_state CHAR (2) AFTER shipping_city ; /* end of script                                                 */

You run the above script by using this command.

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

This message will be printed on the MySQL console.

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

To confirm that the new columns were added, query the customer_dim table.

 mysql> select * from customer_dim \G

Here is the query result.

 *************************** 1. row ***************************             customer_sk: 1         customer_number: 1           customer_name: Really Large Customers customer_street_address: 7500 Louise Dr.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 2. row ***************************             customer_sk: 2         customer_number: 2           customer_name: Small Stores customer_street_address: 2500 Woodland St.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 3. row ***************************             customer_sk: 3         customer_number: 3           customer_name: Medium Retailers customer_street_address: 1111 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 4. row ***************************             customer_sk: 4         customer_number: 4           customer_name: Good Companies customer_street_address: 9500 Scott St.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective date: 2005–03–01             expiry_date: 9999–12–31 *************************** 5. row ***************************             customer_sk: 5         customer_number: 5           customer_name: Wonderful Shops customer_street_address: 3333 Rossmoyne Rd.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 6. row ***************************             customer_sk: 6         customer_number: 6           customer_name: Extremely Loyal Clients customer_street_address: 7070 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective date: 2005–03–01             expiry_date: 2007–02–28 *************************** 7. row ***************************             customer_sk: 7         customer_number: 7           customer_name: Distinguished Agencies customer_street_address: 9999 Scott St.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective date: 2005–03–01             expiry_date: 9999–12–31 *************************** 8. row ***************************             customer_sk: 8         customer_number: 6           customer_name: Extremely Loyal Clients customer_street_address: 7777 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective_date: 2007–03–01             expiry_date: 9999–12–31 *************************** 9. row ***************************             customer_sk: 9         customer_number: 8           customer_name: Subsidiaries customer_street_address: 10000 Wetline Blvd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA        shipping_address: NULL       shipping_zip_code: NULL           shipping_city: NULL          shipping_state: NULL          effective_date: 2007–03–01             expiry_date: 9999–12–31 9 rows in set (0.00 sec)

Note 

The new columns are not populated yet, so their values are NULL.

The order_quantity.sql script in Listing 10.2 adds the order_quantity column to the sales_prder_fact table.

Listing 10.2: Adding the order_quantity column

 /*****************************************************************/ /*                                                               */ /* order_quantity.sql                                            */ /*                                                               */ /*****************************************************************/ USE dw; ALTER TABLE sales_order_fact ADD order_quantity INT AFTER order_amount ; /* end of script                                                 */ 

You run the above script by using this command.

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

Here is what you see on the console after you press Enter.

 Database changed Query OK, 35 rows affected (0.56 sec) Records: 35  Duplicates: 0  Warnings: 0 

To confirm that the new column was added, query the sales_order_fact table.

 mysql> select order_sk osk, customer_sk csk, product_sk psk,        order_date_sk odsk,     -> order_amount amt, order_quantity qty     -> from sales_order_fact;

The result should be as follows.

 +-----+-----+-----+------+---------+------+ | osk | csk | psk | odsk | amt     | qty  | +-----+-----+-----+------+---------+------+ |   1 |   3 |   3 |    1 | 4000.00 | NULL | |   2 |   4 |   1 |   46 | 4000.00 | NULL | |   3 |   5 |   2 |   81 | 6000.00 | NULL | |   4 |   6 |   3 |  152 | 6000.00 | NULL | |   5 |   7 |   1 |  185 | 8000.00 | NULL | |   6 |   1 |   2 |  255 | 8000.00 | NULL | |   7 |   2 |   3 |  311 | 1000.00 | NULL | |   8 |   3 |   1 |  347 | 1000.00 | NULL | |   9 |   4 |   2 |  380 | 2000.00 | NULL | |  10 |   5 |   3 |  416 | 2500.00 | NULL | |  11 |   6 |   1 |  456 | 3000.00 | NULL | |  12 |   7 |   2 |  458 | 3500.00 | NULL | |  13 |   1 |   3 |  502 | 4000.00 | NULL | |  14 |   2 |   1 |  548 | 4500.00 | NULL | |  15 |   3 |   2 |  554 | 1000.00 | NULL | |  16 |   4 |   3 |  584 | 1000.00 | NULL | |  17 |   5 |   1 |  681 | 4000.00 | NULL | |  18 |   6 |   2 |  722 | 4000.00 | NULL | |  19 |   7 |   3 |  730 | 4000.00 | NULL | |  20 |   1 |   1 |  731 | 1000.00 | NULL | |  21 |   2 |   2 |  731 | 2000.00 | NULL | |  22 |   3 |   4 |  731 | 3000.00 | NULL | |  23 |   4 |   5 |  731 | 4000.00 | NULL | |  24 |   5 |   2 |  731 | 1000.00 | NULL | |  25 |   8 |   2 |  731 | 3000.00 | NULL | |  26 |   7 |   4 |  731 | 5000.00 | NULL | |  27 |   9 |   5 |  731 | 7000.00 | NULL | |  28 |   1 |   1 |  731 | 1000.00 | NULL | |  29 |   2 |   2 |  731 | 2000.00 | NULL | |  30 |   3 |   4 |  731 | 4000.00 | NULL | |  31 |   4 |   5 |  731 | 6000.00 | NULL | |  32 |   5 |   1 |  731 | 2500.00 | NULL | |  33 |   8 |   2 |  731 | 5000.00 | NULL | |  34 |   7 |   4 |  731 | 7500.00 | NULL | |  35 |   9 |   5 |  731 | 1000.00 | NULL | +-----+-----+-----+------+---------+------+ 35 rows in set (0.00 sec)