Dimensional Data Warehousing with MySQL: A Tutorial

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:\mysql\scripts\across_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:\mysql\scripts\across_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:\mysql\scripts\across_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:\mysql\scripts\across_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.