Revising the production_fact Table


Similar to what you have done to the customer and shipping zip codes, you need to do the following five steps for the factory zip code:

  1. Create a factory_zip_code_dim view based on the zip_code_dim table.

  2. Add the factory_zip_code_sk column to the production_fact table

  3. Load the factory_zip_code_sk values from the existing factory zip codes.

  4. Define factory_code as the primary key of the factory_stg table and populate the factory_stg with the factories from the factory_dim table. You need the complete data (including the zip code, city, and state) of all factories in the staging for the production regular population. The primary key is required to maintain the factory data in the factory_stg table.

  5. Remove the factory zip code, and their cities and states from the factory_dim table

The script in Listing 23.5 implements the above five steps.

Listing 23.5: production_fact with zip_code_dim intial loading

image from book
 /****************************************************************/ /*                                                              */ /* production_fact_23.sql                                       */ /*                                                              */ /****************************************************************/ USE dw; CREATE VIEW factory_zip_code_dim ( factory_zip_code_sk , factory_zip_code , factory_city , factory_state , effective_date , expiry_date ) AS SELECT   zip_code_sk , zip_code , city , state , effective_date , expiry_date FROM zip_code_dim ; ALTER TABLE production_fact   ADD factory_zip_code_sk INT AFTER factory_sk ; UPDATE   production_fact a , factory_dim b , factory_zip_code_dim c SET   a.factory_zip_code_sk = c.factory_zip_code_sk WHERE   a.factory_sk = b.factory_sk AND b.factory_zip_code = c.factory_zip_code ; TRUNCATE factory_stg; ALTER TABLE factory_stg ADD PRIMARY KEY (factory_code) ; INSERT INTO factory_stg SELECT   factory_code , factory_name , factory_street_address , factory_zip_code , factory_city , factory_state FROM factory_dim ; ALTER TABLE factory_dim   DROP factory_zip_code , DROP factory_city , DROP factory_state ; /* end of script                                                */
image from book



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net