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
/******************************************************************/ /* */ /* 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 */ |
Run the script in Listing 20.1 using this command.
mysql> . c:mysqlscriptscampaign_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
/******************************************************************/
/* */
/* 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 '
'
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 */ |
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
/******************************************************************/ /* */ /* remove_campaign.sql */ /* */ /******************************************************************/ USE dw; UPDATE month_dim SET campaign_session = NULL ; /* end of script */ |
Run the script in Listing 20.3 by using this command.
mysql> . c:mysqlscripts emove_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:mysqlscriptscampaign_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.