Revising the Production Regular Population Script


Similar to what you've done to the regular data warehouse population script, you need to remove all zip code related columns from the factory dimension population and apply the factory zip code surrogate key in the production fact population.

Note that you are not going to truncate the factory_stg table as you only get incremental factory sources in the factory.csv file. You must maintain the complete existing factory data (including the zip code, city, state) in the staging. Therefore, use the REPLACE option in the LOAD DATA INFILE command (shown in bold in the script in Listing 23.9)

Listing 23.9: The revised production regular population script

image from book
 /**********************************************************************/ /*                                                                    */ /* production_regular_23.sql                                          */ /*                                                                    */ /**********************************************************************/ USE dw; LOAD DATA INFILE 'factory.csv' REPLACE INTO TABLE factory_stg FIELDS TERMINATED BY ' , ' OPTIONALLY ENCLOSED BY "" LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( factory_code , factory_name , factory_street_address , factory_zip_code , factory_city , factory_state ) ; /* SCD1                                                               */ UPDATE factory_dim a, factory_stg b SET   a.factory_name = b.factory_name , a.factory_street_address = b.factory_street_address WHERE a.factory_code = b.factory_code ; /* add new factory                                                    */ INSERT INTO factory_dim SELECT   NULL , factory_code , factory_name , factory_street_address , CURRENT_DATE , '9999-12-31' FROM factory_stg WHERE factory_code NOT IN ( SELECT y.factory_code FROM factory_dim x, factory_stg y WHERE x.factory_code = y.factory_code ) ; INSERT INTO production_fact SELECT   b.product_sk , c.date_sk , d.factory_sk , e.factory_zip_code_sk , production_quantity FROM   source.daily_production a , product_dim b , date_dim c , factory_dim d , factory_zip_code_dim e , factory_stg f WHERE     production_date = CURRENT_DATE AND a.product_code = b.product_code AND a.production_date >= b.effective_date AND a.production_date <= b.expiry_date AND a.factory_code = f.factory_code AND f.factory_zip_code = e.factory_zip_code AND a.production_date >= e.effective_date AND a.production_date <= e.expiry_date AND a.production_date = c.date AND a.factory_code = d.factory_code ; /* 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