Populating the New Stars Tables

Populating the New Star’s Tables

In this section, I show you how to populate the tables of the third star.

Let’s assume that the factory_dim table stores information about factories and gets its data from a MySQL table called factory_master. You can create the factory_master table in the source database using the script in Listing 19.2 and populate it using the script in Listing 19.3.

Listing 19.2: Creating the factory_master table

 /****************************************************************/ /*                                                              */ /* factory_master.sql                                           */ /*                                                              */ /****************************************************************/ USE source; CREATE TABLE factory_master ( factory_code INT , factory_name CHAR (30) , factory_street_address CHAR (50) , factory_zip_code INT (5) , factory_city CHAR (30) , factory_state CHAR (2) ) ; /* end of script                                                */

Listing 19.3: Factory_dim initial population

 /****************************************************************/ /*                                                              */ /* factory_ini.sql                                              */ /*                                                              */ /****************************************************************/ USE dw; INSERT INTO factory_dim SELECT   NULL , factory_code , factory_name , factory_street_address , factory_zip_code , factory_city , factory_state , CURRENT_DATE , '9999-12-31' FROM source.factory_master ; /* end of script                                              */

Run the factory_master.sql script in Listing 19.2 using this command.

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

Run the factory_ini.sql script in Listing 19.3 using this command.

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

Note 

You do not need to change anything on the existing first and second stars.

Changes to factories are rare, so you can expect the users to provide any new information regarding the factories in a CSV file. Here are some sample factories in a factory.csv file.

 FACTORY_CODE,NAME,STREET_ADDRESS,ZIP_CODE,CITY,STATE 2,Second Factory,24242 Bunty La.,17055,Pittsburgh,PA 3,Third Factory,37373 Burbank Dr.,17050,Mechanicsburg,PA

Like other CSV source files, you need a staging table to load the factory.csv file. Use the script in Listing 19.4 to create the staging table.

Listing 19.4: Creating the factory staging table

 /****************************************************************/ /*                                                              */ /* factory_stg.sql                                              */ /*                                                              */ /****************************************************************/ USE dw; CREATE TABLE factory_stg ( factory_code INT , factory_name CHAR (30) , factory_street_address CHAR (50) , factory_zip_code INT (5) , factory_city CHAR (30) , factory_state CHAR (2) ) ; /* end of script                                                */

Run the factory_stg.sql script by using this command.

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

The first dimension table in the third star, the product_dim table, gets its data from a daily_production table in the source database. The script in Listing 19.5 creates this table.

Listing 19.5: Creating the daily production table

 /****************************************************************/ /*                                                              */ /* daily_production.sql                                         */ /*                                                              */ /****************************************************************/ USE source; CREATE TABLE daily_production ( product_code INT , production_date DATE , factory_code INT , production_quantity INT ) ; /* end of script                                                */

Run the script in Listing 19.5 using this command.

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

Assuming the users agree that the fact table will start its population on the date you implement it, you can use the script in Listing 19.6 to regularly populate the factory dimension and fact tables.

Note 

You apply SCD1 to all columns in the factory_dim table. You run this script daily to load today's production data.

Listing 19.6: Production regular population

 /****************************************************************/ /*                                                              */ /* production_regular.sql                                       */ /*                                                              */ /****************************************************************/ USE dw; TRUNCATE factory_stg; LOAD DATA INFILE 'factory.csv' 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 , a.factory_zip_code = b.factory_zip_code , a.factory_city = b.factory_city , a.factory_state = b.factory_state WHERE a.factory_code = b.factory_code ; /* add new factory                                              */ INSERT INTO factory_dim SELECT   NULL , factory_code , factory_name , factory_street_address , factory_zip_code , factory_city , factory_state , 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 , production_quantity FROM   source.daily_production a , product_dim b , date_dim c , factory_dim d 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.production_date = c.date AND a.factory_code = d.factory_code ; /* end of script                                                */