Example Queries


In this section I use two queries (given in Listings 24.6 and 24.7) to show that the month end balance measure (which is an accumulated measure) must be used with caution as it is not fully-additive. A measure is not fully-additive across some of its dimensions, usually across the time dimension. A measure that is not fully-additive is semi-additive.

Additive Across Products

You can correctly add the accumulated balance (month end amount balance) across products as demonstrated by the query in Listing 24.6.

Listing 24.6: Across products

image from book
 /*****************************************************************/ /*                                                    */ /* balance_across_products.sql                        */ /*                                                    */ /*****************************************************************/ USE dw; SELECT   Year , month , SUM (month_end_amount_balance) FROM   month_end_balance_fact a , month_dim b WHERE a.month_sk = b.month_sk GROUP BY year, month ORDER BY year, month ; /* end of script                                      */
image from book

Run the script in Listing 24.6 using this command.

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

You should see the following response.

 Database changed +------+-------+--------------------------------+ | year | month | SUM (month_end_amount_balance) | +------+-------+--------------------------------+ | 2006 |     1 |                        1000.00 | | 2006 |     2 |                        2000.00 | | 2006 |     3 |                        4000.00 | | 2006 |     4 |                        6500.00 | | 2006 |     5 |                        9500.00 | | 2006 |     6 |                       13000.00 | | 2006 |     7 |                       17000.00 | | 2006 |     8 |                       21500.00 | | 2006 |     9 |                       22500.00 | | 2006 |    10 |                       23500.00 | | 2006 |    11 |                       23500.00 | | 2006 |    12 |                       23500.00 | | 2007 |     2 |                        9000.00 | | 2007 |     3 |                      158500.00 | +------+-------+--------------------------------+ 14 rows in set (0.02 sec)

You can verify this result (the sum of amount balances of all products every month) against the previous initial population's query output.

Non-Additive Across Months

The query in Listing 24.7 adds the month end balances across months.

Listing 24.7: Across months

image from book
 /*****************************************************************/ /*                                                    */ /* balance_across_months.sql                          */ /*                                                    */ /*****************************************************************/ USE dw ; SELECT   product_name , SUM (month_end_amount_balance) FROM   month_end_balance_fact a , product_dim b WHERE a.product_sk = b.product_sk GROUP BY product_code ORDER BY product_code ; /* end of script                                      */
image from book

Run the script in Listing 24.7 by using this command.

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

Here is the response.

 Database changed +--------------------------+--------------------------------+ | product_name             | SUM (month_end_amount_balance) | +--------------------------+--------------------------------+ | Hard Disk Drive          |                      104000.00 | | Floppy Drive             |                       83500.00 | | LCD Panel                |                      116500.00 | | Keyboard                 |                       27000.00 | | High End Hard Disk Drive |                        4000.00 | +--------------------------+--------------------------------+ 5 rows in set (0.00 sec)

The query output is incorrect. The correct output should be the same as the result of the following query, which is a query against the source data (the month_end_sales_order_fact table).

 mysql> SELECT product_name, sum (month_order_amount)     -> FROM month_end_sales_order_fact a, product_dim b     -> WHERE a.product_sk = b.product_sk     -> group by product_code;

 +--------------------------+--------------------------+ | product_name             | sum (month_order_amount) | +--------------------------+--------------------------+ | Hard Disk Drive          |                55000. 00 | | Floppy Drive             |                36500. 00 | | LCD Panel                |                59500. 00 | | Keyboard                 |                27000. 00 | | High End Hard Disk Drive |                 4000. 00 | +--------------------------+--------------------------+ 5 rows in set (0.00 sec)

In other words, the month_end_balance measure is additive across products, but not across months.



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