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:mysqlscriptsfactory_master.sql
Run the factory_ini.sql script in Listing 19.3 using this command.
mysql> . c:mysqlscriptsfactory_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:mysqlscriptsfactory_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:mysqlscriptsdaily_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 '
'
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 */ |