The Revised Schema

The first you need to do is set up a new fact table that stores the accumulated month-end balance of sales order amounts. Call this table month_end_balance_fact. Figure 24.1 shows a sample of an accumulated balance. The sample starts the accumulation in January 2006. The January order for Floppy Drive is 1000, so its accumulated month-end balance is 1000. There is sales order for Floppy Drive in February 2006, so its February accumulated month-end balance does not change. Its March order is 500, so its accumulated balance is 1500, and so on. The balances are reset at the beginning of each year.

Figure 24.1. The month_end_balance_fact, product_dim, and month_dim tables create a new star

[View full size image]

The month_end_balance_fact table creates an additional star in your schema. The new star consists of this new fact table and two existing dimension tables from another star, product_dim and month_dim. Figure 24.1 shows the new schema. Note that only related tables are shown.

The script in Listing 24.1 can be used to create the month_end_balance_fact table.

Listing 24.1. Creating the month_end_balance_fact table

/******************************************************************/
/*                                                                */
/* month_end_balance_fact.sql                                     */
/*                                                                */
/******************************************************************/

USE dw;

CREATE TABLE month_end_balance_fact
(month_sk INT
, product_sk INT
, month_end_amount_balance DEC(10,2)
, month_end_quantity_balance INT )
;

/* end of script                                                  */

Run the script in Listing 24.1 to create the new fact table.

mysql> . c:mysqlscriptsmonth_end_balance_fact.sql