Revising the Campaign Population Script


In the overview I explained how you could simplify the campaign data in Chapter 16. In this section I explain how you can update the campaign population script for the non-straight data source.

First of all, you need a different campaign staging table. Use the script in Listing 20.1 to create one.

Listing 20.1: Creating a new staging table for the non-straight campaign population

image from book
 /*****************************************************************/ /*                                                               */ /* campaign_stg_20.sql                                           */ /*                                                               */ /*****************************************************************/ USE dw; CREATE TABLE non_straight_campaign_stg  ( campaign_session CHAR (30) , start_month CHAR (9) , start_year INT (4) , end_month CHAR (9) , end_year INT (4) ) ; /* end of script                                                 */
image from book

Run the script in Listing 20.1 using this command.

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

You’ll see the following message on your console.

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

Note that the new staging table has both the start month/year and end month/year columns.

The revised campaign population script is given in Listing 20.2. It updates the month dimension twice. The first update is for the start and the end month of every campaign. The second update is for the months between the start and end months of each campaign.

Listing 20.2: Revised campaign population script

image from book
 /****************************************************************/ /*                                                              */ /* campaign_session_20.sql                                      */ /*                                                              */ /****************************************************************/ TRUNCATE non_straight_campaign_stg; LOAD DATA INFILE 'non_straight_campaign.csv' INTO TABLE non_straight_campaign_stg FIELDS TERMINATED BY ', ' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (   campaign_session , start_month , start_year , end_month , end_year ) ; /* for the start and end months                                  */ UPDATE month_dim p, ( SELECT   a.month , a.year , b.campaign_session  FROM  month_dim a LEFT OUTER JOIN  ( SELECT      campaign_session    , month    , year    FROM    (SELECT       campaign_session     , start_month month     , start_year year     FROM non_straight_campaign_stg     UNION ALL    SELECT      campaign_session    , end_month month    , end_year year    FROM non_straight_campaign_stg) x    ORDER BY year, month) b ON     a.year = b.year    AND a.month = b.month ) q SET p.campaign_session = q.campaign_session WHERE     q.campaign_session IS NOT NULL AND p.month = q.month AND p.year = q.year ; /* end of the start and end months                               */ /* for the in-between months                                     */ UPDATE month_dim p , month_dim q, (SELECT   MIN (a.month) minmo , MIN (a.year) minyear , a.campaign_session campaign_session , MAX (b.month) maxmo , MAX (b.year) maxyear FROM   month_dim a , month_dim b WHERE a.campaign_session IS NOT NULL AND b.campaign_session IS NOT NULL AND a.month = b.month AND a.year = b.year GROUP BY   a.campaign_session , b.campaign_session ) r SET p.campaign_session = r.campaign_session WHERE    p.month > r.minmo AND p.year = r.minyear AND q.month < r.maxmo AND q.year = r.maxyear AND p.month = q.month AND p.year = q.year ; /* end of the in-between months                                  */ /* end of script                                                 */
image from book

Now let’s test the new script. The campaign data can be found in the non_straight_campaign.csv file (printed below).

 CAMPAIGN_SESSION, START_MONTH, START_YEAR, END_MONTH, END_YEAR 2006 First Campaign, 1, 2006, 4, 2006 2006 Second Campaign, 5, 2006, 7, 2006 2006 Third Campaign, 8, 2006, 8, 2006 2006 Last Campaign, 9, 2006, 12, 2006

Before you run the revised campaign population script, however, you need to remove the campaign sessions you loaded in Chapter 16 using the script in Listing 20.3.

Listing 20.3: Removing campaign

image from book
 /*****************************************************************/ /*                                                               */ /* remove_campaign.sql                                           */ /*                                                               */ /*****************************************************************/ USE dw; UPDATE month_dim SET campaign_session = NULL ; /* end of script                                                 */
image from book

Run the script in Listing 20.3 by using this command.

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

You’ll be notified that there are 12 rows affected.

 Database changed Query OK, 12 rows affected (0.06 sec) Rows matched: 96  Changed: 12  Warnings: 0

Now run the campaign_session_20.sql script in Listing 20.2.

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

You’ll see these messages on the console.

 Query OK, 1 row affected (0.10 sec) Query OK, 4 rows affected (0.05 sec) Records: 4  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 7 rows affected (0.06 sec) Rows matched: 7  Changed: 7  Warnings: 0 Query OK, 5 rows affected (0.05 sec) Rows matched: 5  Changed: 5  Warnings: 0

Now query the month_dim table to confirm it was correctly populated.

 mysql> select month_sk m_sk, month_name, month m, campaign_session,     -> quarter q, year     -> from month_dim where year = 2006;

The result is given below.

 +------+------------+------+----------------------+------+------+ | m_sk | month_name | m    | campaign_session     | q    | year | +------+------------+------+----------------------+------+------+ |   11 | January    |    1 | 2006 First Campaign  |    1 | 2006 |  |   12 | February   |    2 | 2006 First Campaign  |    1 | 2006 | |   13 | March      |    3 | 2006 First Campaign  |    1 | 2006 | |   14 | April      |    4 | 2006 First Campaign  |    2 | 2006 | |   15 | May        |    5 | 2006 Second Campaign |    2 | 2006 | |   16 | June       |    6 | 2006 Second Campaign |    2 | 2006 | |   17 | July       |    7 | 2006 Second Campaign |    3 | 2006 | |   18 | August     |    8 | 2006 Third Campaign  |    3 | 2006 | |   19 | September  |    9 | 2006 Last Campaign   |    3 | 2006 | |   20 | October    |   10 | 2006 Last Campaign   |    4 | 2006 | |   21 | November   |   11 | 2006 Last Campaign   |    4 | 2006 | |   22 | December   |   12 | 2006 Last Campaign   |    4 | 2006 | +------+------------+------+----------------------+------+------+ 12 rows in set (0.00 sec)

The two hierarchical queries from Chapter 16, quarter_path.sql and campaign_session_path.sql, do not need to change. If you run them again you'll get the same results as before.



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