Specific Queries


A specific query selects and aggregates the facts on a specific dimension value. The two examples show the application of dimensional queries in specific queries.

Monthly Storage Product Sales

The monthly_storage.sql script in Listing 4.4 aggregates sales amounts and the number of orders every month.

Listing 4.4: Specific query (monthly storage product sales)

image from book
 /*****************************************************************/ /*                                                               */ /* monthly_storage.sql                                           */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   product_name , month_name , year , SUM (order_amount) , COUNT(*) FROM   sales_prder_fact a , product_dim b , date_dim c WHERE     a.product_sk = b.product_sk AND a.order_date_sk = c.date_sk GROUP BY   product_name , product_category , month_name , year HAVING product_category = 'Storage' ORDER BY   year , month name ; /* end of script                                                 */
image from book

Run the script using this command.

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

Here is the output of the query:

 Database changed +-----------------+------------+------+------------------+---------+ | product_name    | month_name | year | SUM(order_amount)| COUNT(*)| +-----------------+------------+------+------------------+---------+ | Hard Disk Drive | February   | 2007 |         65000.00 |       2 | | Floppy Drive    | February   | 2007 |         55000.00 |       2 | | Hard Disk Drive | February   | 2007 |         15000.00 |       3 | | Floppy Drive    | February   | 2007 |         23000.00 |       4 | +-----------------+------------+------+------------------+---------+ 4 rows in set (0.00 sec)

The query result shows the monthly total order amounts (sum) and the number of orders (count), grouped by the individual storage products.

Quarterly Sales in Mechanisburg

The query in Listing 4.5 is another specific query. It produces the quarterly aggregation of the order amounts in Mechanicsburg.

Listing 4.5: Specific query (quarterly sales in Mechanicsburg)

image from book
 /*****************************************************************/ /*                                                               */ /* quarterly_mechanicsburg.sql                                   */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   customer_city , quarter , year , SUM (order_amount) , COUNT (order_sk) FROM   sales_order_fact a , customer_dim b , date_dim c WHERE     a.customer_sk = b.customer_sk AND a.order_date_sk = c.date_sk GROUP BY   customer_city , quarter , year HAVING customer_city = 'Mechanicsburg' ORDER BY   year , quarter; /* end of script                                                 */
image from book

Run the script using this command.

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

Here is the query result.

 Database changed +---------------+---------+------+-----------------+---------------+ | customer_city | quarter | year |SUM(order_amount)|COUNT(order_sk)| +---------------+---------+------+-----------------+---------------+ | Mechanicsburg |       4 | 2007 |       177000.00 |            10 | +---------------+---------+------+-----------------+---------------+ 1 row in set (0.00 sec)

The query result shows the quarterly total order amounts (sum) and the number of orders (count) for Mechanicsburg.



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