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

image from book
 /*****************************************************************/ /*                                                               */ /* 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 '\r\n' 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                                                 */
image from book

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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

Run the script in Listing 11.3 using this command.

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

You’ll see the following as the response.

 Database changed Query OK, 0 rows affected (0.20 sec)



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net