# Chapter 3: Measure Additivity

This chapter covers fully-additive measures. Semi-additive measures are explained in Chapter 24, “Accumulated 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`

Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon