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
/******************************************************************/ /* */ /* 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 */ |
Run the script in Listing 24.6 using this command.
mysql> . c:mysqlscriptsalance_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
/******************************************************************/ /* */ /* 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 */ |
Run the script in Listing 24.7 by using this command.
mysql> . c:mysqlscriptsalance_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.