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:
Create a factory_zip_code_dim view based on the zip_code_dim table.
Add the factory_zip_code_sk column to the production_fact table
Load the factory_zip_code_sk values from the existing factory zip codes.
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.
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
/****************************************************************/ /* */ /* 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 */