Testing Fully-Additivity
The order_amount measure is fully-additive if all query results are the same. To prove that order_amount is fully-additive, we use the queries in Listings 3.2, 3.3, 3.4, and 3.5. We will prove that all the four queries produce a total order of 58,000.
The first query, the across_all_dimensions.sql script in Listing 3.2, sums the order_amounts across all dimensions (adding up the order_amount values by selecting all dimensions).
Listing 3.2. Querying across all dimensions
/******************************************************************/ /* */ /* across_all_dimensions.sql */ /* */ /******************************************************************/ USE dw; SELECT SUM(order_amount) sum_of_order_amount FROM sales_order_fact a ; /* end of script */ |
Run the script using this command.
mysql> . c:mysqlscriptsacross_all_dimensions.sql
You’ll get:
Database changed +---------------------+ | sum_of_order_amount | +---------------------+ | 58000.00 | +---------------------+ 1 row in set (0.04 sec)
The second query, the across_date_product_order.sql script in Listing 3.3, sums the order_amount values across the date, product, and order dimensions (adding up the order_amount values by selecting customers only).
Listing 3.3. Querying across the date, product, and order
/******************************************************************/
/* */
/* across_date_product_order.sql */
/* */
/******************************************************************/
USE dw;
SELECT
customer_number
, SUM(order_amount) sum_of_order_amount
FROM
sales_order_fact a
, customer_dim b
WHERE
a.customer_sk = b.customer_sk
GROUP BY
customer_number
;
/* end of script */ |
You run the script in Listing 3.3 using this command.
mysql> . c:mysqlscriptsacross_date_product.sql
The result is as follows.
Database changed +-----------------+---------------------+ | customer_number |sum_of_order_amount | +-----------------+---------------------+ | 1 | 7000.00 | | 2 | 9000.00 | | 3 | 12000.00 | | 4 | 14000.00 | | 5 | 16000.00 | +-----------------+---------------------+ 5 rows in set (0.10 sec)
The total of the sum of order amounts is 7,000 + 9,000 + 12,000 + 14,000 + 16,000 = 58,000.
The third query, the across_date_customer_order.sql script in Listing 3.4, sums the order amounts across the date, customer, and order dimensions.
Listing 3.4. Querying across the date, customer, and order
/******************************************************************/
/* */
/* across_date_customer_order.sql */
/* */
/******************************************************************/
USE dw;
SELECT
product_code
, SUM(order_amount) sum_of_order_amount
FROM
sales_order_fact a
, product_dim b
WHERE
a.product_sk = b.product_sk
GROUP BY
product_code
;
/* end of script */ |
You can run the script in Listing 3.4 using this command.
mysql> . c:mysqlscriptsacross_date_customer.sql
The result is this.
Database changed +--------------+---------------------+ | product_code | sum_of_order_amount | +--------------+---------------------+ | 1 | 15000.00 | | 2 | 23000.00 | | 3 | 20000.00 | +--------------+---------------------+ 3 rows in set (0.09 sec)
Again, the query produces a total order amount of 58,000 (15,000 + 23,000 + 20,000).
The fourth query, the across_date_order.sql script in Listing 3.4, sums the order amounts across the date and order dimensions.
Listing 3.5. Querying across the date and order
/******************************************************************/
/* */
/* across_date_order.sql */
/* */
/******************************************************************/
USE dw;
SELECT
customer_number
, product_code
, SUM(order_amount) sum_of_order_amount
FROM
sales_order_fact a
, customer_dim b
, product_dim c
WHERE
a.customer_sk = b.customer_sk
AND a.product_sk = c.product_sk
GROUP BY
customer_number
, product_code
;
/* end of script */ |
Run the script in Listing 3.5 using this command.
mysql> . c:mysqlscriptsacross_date.sql
You should see the following on your console.
Database changed +-----------------+--------------+---------------------+ | customer_number | product_code | sum_of_order_amount | +-----------------+--------------+---------------------+ | 1 | 2 | 1000.00 | | 1 | 3 | 6000.00 | | 2 | 1 | 1000.00 | | 2 | 2 | 8000.00 | | 3 | 1 | 8000.00 | | 3 | 3 | 4000.00 | | 4 | 2 | 4000.00 | | 4 | 3 | 10000.00 | | 5 | 1 | 6000.00 | | 5 | 2 | 10000.00 | +-----------------+--------------+---------------------+ 10 rows in set (0.03 sec)
The total is again 58,000 (1,000 + 6,000 + 1,000 + 8,000 + 8,000 + 4,000 + 4,000 + 10,000 + 6,000 + 10,000).
All the four queries produce the same total (58,000), which confirms that this measure is fully-additive.