Regular Population
You need two scripts for the regular population. The first script is for January population and is presented in Listing 24.3. The second script is for the other months and is given in Listing 24.4.
The January regular population script is similar to the January initial population in that the script must reset the month end balance. However, while the January initial population is part of the whole initial population script, the January regular population is a separate script from the other months. You run the January regular population script at the end of January.
The regular population for the other months (February to December) is also similar to the initial population in that they both have three Insert statements. However, while the initial population must load historical data from January 2006 to March 2007 all at once, the regular population loads the current month data only. The other difference is that the regular population does not have a specific year condition. You run the regular population at the end of every month (February to December).
Listing 24.3. January month end balance regular population script
/******************************************************************/
/* */
/* jan_month_end_balance_regular.sql */
/* */
/******************************************************************/
USE dw;
INSERT INTO month_end_balance_fact
SELECT m.*
FROM
month_end_sales_order_fact m
, month_dim n
WHERE
month = 1
AND m.month_order_sk = n.month_sk
AND n.year = YEAR(CURRENT_DATE)
;
/* end of script */ |
Listing 24.4. Month end balance regular population for all months except January
/******************************************************************/
/* */
/* month_end_balance_regular.sql */
/* */
/******************************************************************/
USE dw;
INSERT INTO month_end_balance_fact
SELECT
n.month_order_sk
, n.product_sk
, (n.month_order_amount + m.month_end_amount_balance)
, (n.month_order_quantity + m.month_end_quantity_balance)
FROM
month_end_balance_fact m
, month_end_sales_order_fact n
, month_dim o
, month_dim p
WHERE
o.month = MONTH(CURRENT_DATE) - 1
AND p.month = MONTH(CURRENT_DATE)
AND m.month_sk = o.month_sk
AND n.month_order_sk = p.month_sk
AND o.year = p.year
AND m.product_sk = n.product_sk
;
INSERT INTO month_end_balance_fact
SELECT m.*
FROM
month_end_sales_order_fact m
, month_dim n
WHERE
n.month = MONTH(CURRENT_DATE)
AND m.month_order_sk = n.month_sk
AND m.product_sk NOT IN (
SELECT x.product_sk
FROM
month_end_balance_fact x
, month_dim y
WHERE
x.month_sk = y.month_sk
AND y.month = (MONTH(CURRENT_DATE)-1)
AND y.year = n.year )
;
INSERT INTO month_end_balance_fact
SELECT
o.month_sk
, m.product_sk
, m.month_end_amount_balance
, m.month_end_quantity_balance
FROM
month_end_balance_fact m
, month_dim n
, month_dim o
WHERE
n.month = MONTH(CURRENT_DATE)-1
AND m.month_sk = n.month_sk
AND o.month = MONTH(CURRENT_DATE)
AND n.year = o.year
AND m.product_sk NOT IN (
SELECT x.product_sk
FROM
month_end_sales_order_fact x
, month_dim y
WHERE
x.month_order_sk = y.month_sk
AND y.month = MONTH(CURRENT_DATE)
AND y.year = n.year)
;
/* end of script */ |
As the regular population scripts are similar to the initial population script, you don’t need to test the regular population.