This chapter teaches you how to accumulate measures in a fact table, something you have to do if, say, your data warehouse users want to track month after month measures. In this chapter you also learn that accumulated measures are semi-additive and that its initial population is much more complex than any of those you’ve done in the previous chapters.
Note | The opposite of semi-additive measures are fully-additive measures, which were covered in Chapter 3, “Measure Additivity.” |
In this chapter I show you how to implement an accumulated month-end balance and how to change our data warehouse schema and initial and regular population scripts.
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
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.
Month | Product Name | Order in the Month | Month-End Balance (Accumulated Sales Order Amount) |
---|---|---|---|
January 2006 | Floppy Drive | 1000 | 1000 |
January 2006 | Hard Drive | 9000 | 9000 |
February 2006 | Floppy Drive | 0 | 1000 |
February 2006 | Hard Drive | 12000 | 21000 |
March 2006 | Floppy Drive | 500 | 1500 |
March 2006 | Hard Drive | 15000 | 36000 |
… | … | … | … |
December 2006 | … | … | … |
January 2007 | Hard Drive | 9000 | 9000 |
January 2007 | Floppy Drive | 0 | 0 |
February 2007 | Hard Drive | 15000 | 24000 |
February 2007 | Floppy Drive | 0 | 0 |
March 2007 | … | … | … |
… | … | … | … |
December 2007 | … | … | … |
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:\mysql\scripts\month_end_balance_fact.sql