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

image from book
 /*****************************************************************/ /*                                                               */ /* across_all_dimensions.sql                                     */ /*                                                               */ /*****************************************************************/ USE dw; SELECT SUM (order_amount) sum_of_order_amount FROM sales order fact a ; /* end of script                                                 */
image from book

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

image from book
 /**********************************************************************/ /*                                                                    */ /* 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                                                      */
image from book

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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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.



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

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net