Grouping and Drilling Queries


You can do grouping and drilling queries on hierarchies. A grouping query groups the facts on one or more levels of a dimension. The script in Listing 15.1 is an example of a grouping query. It is a query that retrieves the sales amount grouped by products (product_category) and the three hierarchy levels (columns) of the date dimension (year, quarter, and month_name).

Listing 15.1: A grouping query

image from book
 /*****************************************************************/ /*                                                               */ /* grouping.sql                                                  */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   product_category / year , quarter , month_name , SUM (order_amount) FROM   sales_order_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_category , year , quarter , month ORDER BY   product_category , year , quarter , month ; /* end of script                                                 */
image from book

You can run the grouping.sql script in Listing 15.1 by using this command.

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

If you’ve been following the instructions in Chapter 1 to Chapter 14, you’ll get the following output.

 Database changed +----------------+------+----------+--------------+--------------------+ |product_category| year | quarter  | month_name   | SUM (order_amount) | +----------------+------+----------+--------------+--------------------+ | Monitor        | 2005 |       1  | March        |            4000.00 |  | Monitor        | 2005 |       3  | July         |            6000.00 | | Monitor        | 2006 |       1  | January      |            1000.00 | | Monitor        | 2006 |       2  | April        |            2500.00 | | Monitor        | 2006 |       3  | July         |            4000.00 | | Monitor        | 2006 |       4  |  October     |            1000.00 | | Monitor        | 2007 |       1  | February     |            4000.00 | | Monitor        | 2007 |       1  | March        |            32000.00| | Peripheral     | 2007 |       1  | March        |            25000.00| | Storage        | 2005 |       2  | April        |            4000.00 | | Storage        | 2005 |       2  | May          |            6000.00 | | Storage        | 2005 |       3  | September    |            8000.00 | | Storage        | 2005 |       4  | November     |            8000.00 | | Storage        | 2006 |       1  | February     |            1000.00 | | Storage        | 2006 |       1  | March        |            2000.00 | | Storage        | 2006 |       2  | May          |            3000.00 | | Storage        | 2006 |       2  | June         |            3500.00 | | Storage        | 2006 |       3  | August       |            4500.00 | | Storage        | 2006 |       3  | September    |            1000.00 | | Storage        | 2007 |       1  | January      |            4000.00 | | Storage        | 2007 |       1  | February     |            4000.00 | | Storage        | 2007 |       1  | March        |            46000.00|  +----------------+------+----------+--------------+--------------------+ 22 rows in set (0.42 sec)

The grouping query output shows the measure (sales order amount) grouped along the year-quarter-month hierarchy on each row.

Like a grouping query, a drilling query also groups its facts on one or more levels of a dimension. However, unlike a grouping query that shows the grouped facts (e.g. the sum of order amounts) of only the dimension’s lowest level (e.g. the month level), a drilling query shows the grouped facts of each level of the dimension. The drilling query in Listing 15.2 shows the sum of order amounts at each of the date dimension levels (year, quarter, and month levels).

Listing 15.2: A drilling query

image from book
 /*****************************************************************/ /*                                                               */ /* drilling.sql                                                  */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   product_category , time , order_amount FROM ( ( SELECT   product_category , date , year time , 1 sequence , SUM (order_amount) order_amount FROM   sales_order_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_category , year ORDER BY date ) UNION ALL ( SELECT   product_category , date , quarter time , 2 sequence , SUM (order_amount) FROM   sales_order_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_category, year, quarter ORDER BY date ) UNION ALL ( SELECT   product_category , date , month_name time , 3 sequence , SUM (order_amount) FROM   sales_order_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_category , year , quarter , month_name ORDER BY date ) ) x ORDER BY   product_category , date , sequence , time ; /* end of script                                                 */
image from book

You can run the drilling.sql script by using this command.

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

Here is the output of the drilling query.

 Database changed +------------------+----------+-------------+ | product_category | time     | order_amount| +------------------+----------+-------------+ | Monitor          | 2005     |     10000.00| | Monitor          | 1        |      4000.00| | Monitor          | March    |      4000.00| | Monitor          | 3        |      6000.00| | Monitor          | July     |      6000.00| | Monitor          | 2006     |      8500.00| | Monitor          | 1        |      1000.00| | Monitor          | January  |      1000.00| | Monitor          | 2        |      2500.00| | Monitor          | April    |      2500.00| | Monitor          | 3        |      4000.00| | Monitor          | July     |      4000.00| | Monitor          | 4        |      1000.00| | Monitor          | October  |      1000.00| | Monitor          | 2007     |     36000.00| | Monitor          | 1        |     36000.00| | Monitor          | February |      4000.00| | Monitor          | March    |     32000.00| | Peripheral       | 2007     |     25000.00| | Peripheral       | 1        |     25000.00| | Peripheral       | March    |     25000.00| | Storage          | 2005     |     26000.00| | Storage          | 2        |     10000.00| | Storage          | April    |      4000.00| | Storage          | May      |      6000.00| | Storage          | 3        |      8000.00| | Storage          | September|      8000.00| | Storage          | 4        |      8000.00| | Storage          | November |      8000.00| | Storage          | 2006     |     15000.00| | Storage          | 1        |      3000.00| | Storage          | February |      1000.00| | Storage          | March    |      2000.00| | Storage          | 2        |      6500.00| | Storage          | May      |      3000.00| | Storage          | June     |      3500.00| | Storage          | 3        |      5500.00| | Storage          | August   |      4500.00| | Storage          | September|      1000.00| | Storage          | 2007     |     54000.00| | Storage          | 1        |     54000.00| | Storage          | January  |      4000.00| | Storage          | February |      4000.00| | Storage          | March    |     46000.00| +------------------+----------+-------------+ 44 rows in set (0.03 sec)

Note 

Drilling queries use the UNION set operator. Each of the three unions in the drilling query in Listing 15.2 gives you the rows of each of the three levels. The sequence column helps order the monthly sales orders from the year to the quarter to the month.



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

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