Chapter 3: Measure Additivity

A measure always has numeric values. One of the most important characteristics of a fact table pertaining to measures is measure additivity. With regard to additivity, a measure may be fully-additive or semi-additive. If you can sum the values of a measure in all situations, the measure is fully-additive. If you can only add up its values in some situations, it is semi-additive. Understanding measure additivity is key to selecting a data item as a measure in a fact table. The order_amount measure in the sales_order_fact table, for example, is fully-additive because you can correctly add up this measure across any one and all of its dimensions. This means, you can correctly sum the order_amount measure in your sales_order_fact table any time, for any product, any customer, and any order.

This chapter covers fully-additive measures. Semi-additive measures are explained in Chapter 24, “Accumulated Measures.”

Fully-Additive Measures

A measure is fully additive if the total of its individual values across any one dimension is the same as the total across any other dimension and across any combination of some or all dimensions.

This section explains fully additive measures by using the order_amount measure in the sales_order_fact table. I show that this measure is fullyadditive by querying the sales_order_fact table across all dimensions and the combinations of some dimensions. When you design a fact table and want to make sure its measures are fully-additive, you should test every measure using this type of query.

However, before you start, you need to add data to the order_dim, date_dim, and sales_order_fact tables because they are empty. You already have three products in the product_dim table and seven customers in the customer_dim table, so you don't need to add data to these tables.

You can use the script in Listing 3.1 to add data. This script inserts ten rows into the order_dim table, one row into the date_dim table, and ten rows into the sales_order_fact table.

Listing 3.1: Inserting data to demonstrate fully-additive measures

 /*****************************************************************/ /*                                                               */ /* additive_data.sql                                             */ /*                                                               */ /*****************************************************************/ USE dw; INSERT INTO order_dim VALUES   (NULL, 1, CURRENT_DATE, '9999-12-31') , (NULL, 2, CURRENT_DATE, '9999-12-31') , (NULL, 3, CURRENT_DATE, '9999-12-31') , (NULL, 4, CURRENT_DATE, '9999-12-31') , (NULL, 5, CURRENT_DATE, '9999-12-31') , (NULL, 6, CURRENT_DATE, '9999-12-31') , (NULL, 7, CURRENT_DATE, '9999-12-31') , (NULL, 8, CURRENT_DATE, '9999-12-31') , (NULL, 9, CURRENT_DATE, '9999-12-31') , (NULL, 10, CURRENT_DATE, '9999-12-31') INSERT INTO date_dim VALUES   (NULL, '2005-10-31', 'October', 10, 4, 2005, CURRENT_DATE,   '9999-12-31') ;   INSERT INTO sales_order_fact VALUES   (1, 1, 2, 1, 1000) , (2, 2, 3, 1, 1000) , (3, 3, 4, 1, 4000) , (4, 4, 2, 1, 4000) , (5, 5, 3, 1, 6000) , (6, 1, 4, 1, 6000) , (7, 2, 2, 1, 8000) , (8, 3, 3, 1, 8000) , (9, 4, 4, 1, 10000) , (10, 5, 2, 1, 10000) ; /* end of script                                               */

You run the script in the MySQL monitor by entering the script name with its full path as follows:

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

Here is what you should see on the console after you press Enter.

 Database changed Query OK, 10 rows affected (0.26 sec) Records: 10  Duplicates: 0  Warnings: 0 Query OK, 1 row affected (0.09 sec) Query OK, 10 rows affected (0.11 sec) Records: 10  Duplicates: 0  Warnings: 0