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.
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:mysqlscriptsshipping_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_order_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:mysqlscriptsorder_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)
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.