Chapter 24: Accumulated Measures


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 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.

image from book
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.

Table 24.1: Month end balance prepared data
Open table as spreadsheet

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

image from book
 /*****************************************************************/ /*                                                          */ /* 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  */
image from book

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

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



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