Performing On-Demand Population
In this section, I show you how to perform on-demand population using a sales promotion as an example. Essentially, what the population does is set the promo_ind column in the date_dim table to ‘Y’ if there is a promotion scheduled on a date.
The on-demand population script to be used in this example is presented in Listing 11.2. You run the script after the dates are loaded. In other words, all the dates from the start date to the last date of all promotion schedules must be available in the date dimension.
Listing 11.2. Populating the promotion indicator
/******************************************************************/
/* */
/* on_demand.sql
/* */
/******************************************************************/
USE dw;
TRUNCATE promo_schedule_stg;
LOAD DATA INFILE 'promo_schedule.csv'
INTO TABLE promo_schedule_stg
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES
( promo_code
, promo_name
, promo_start_date
, promo_last_date )
;
UPDATE
date_dim a
, promo_schedule_stg b
SET a.promo_ind = 'Y'
WHERE
a.date >= b.promo_start_date
AND a.date <= b.promo_last_date
;
/* end of script */ |
The script in Listing 11.2 loads the content of the promo_schedule.csv file to a promo_schedule_stg table. Therefore, before you can run the script in Listing 11.2, you need to create the promo_schedule_stg table. The script in Listing 11.3 can be used to create the table.
Note
The promo_schedule.csv file is discussed in the next section.
Listing 11.3. Creating the promotion staging table
/******************************************************************/ /* */ /* create_promo_schedule_stg.sql */ /* */ /******************************************************************/ USE dw; CREATE TABLE promo_schedule_stg ( promo_code CHAR(2) , promo_name CHAR(30) , promo_start_date DATE , promo_last_date DATE ) ; /* end of script */ |
Run the script in Listing 11.3 using this command.
mysql> . c:mysqlscriptscreate_promo_schedule_stg.sql
You’ll see the following as the response.
Database changed Query OK, 0 rows affected (0.20 sec)