Enhancing the Schema

In this section I explain on-demand population using a sales promotion scenario. Regular population is not appropriate here as the data loading cannot be scheduled. The following is the content of a CSV flat file that needs to be loaded.

PROMOTION CODE,PROMOTION NAME,START DATE,LAST DATE
SO,Special Offer,2007-04-01,2007-04-10
DP,Disk Promotion,2007-05-05,2007-05-20
MS,Month Special,2007-06-01,2007-06-30
MS,Monitor Promotion,2007-07-10,2007-07-15
BS,Back to School,2007-08-10,2007-08-30

Note that the source data provides promotion periods, not individual promotion dates. Assuming your user wants to load only new promotions in the future, they don’t need promotion history in the data warehouse.

The first thing you need to do is add a new column, promo_ind (for promotion indicator) to the date_dim table. The new schema after the addition is shown in Figure 11.1.

Figure 11.1. The new promo_ind column in date_dim

[View full size image]

You use the promotion_indicator.sql script in Listing 11.1 to add the promo_ind column in the date_dim table.

Listing 11.1. Promotion indicator

/******************************************************************/
/*                                                                */
/* promotion_indicator.sql                                        */
/*                                                                */
/******************************************************************/

USE dw;

ALTER TABLE date_dim
ADD promo_ind CHAR(1) AFTER year
;

/* end of script                                                  */

You run the script using this command.

mysql> . c:mysqlscriptspromotion_indicator.sql

After you run the script, you’ll see this on the console.

Database changed
Query OK, 2132 rows affected (0.45 sec)
Records: 2132  Duplicates: 0  Warnings: 0

Using the following SQL statement, query the date dimension to confirm there’s no data in the new promo_ind column.

mysql> select * from date_dim where promo_ind IS NOT NULL;

All values of the column must be NULL, as shown here.

Empty set (0.01 sec)