Hierarchical Queries

In this section I present two example queries that use the two hierarchical paths of the month dimension. The first query, shown in Listing 16.4, drills on the year-quarter-month path. This query is similar to the drilling query in Chapter 15, “Dimension Hierarchies” except that this one queries the month_end_sales_order_fact table, whereas the one in Chapter 15 queried the sales_order_fact table.

Listing 16.4: Quarter path drilling query

 /*****************************************************************/ /*                                                               */ /* quarter_path.sql                                              */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   product_category , time , order_amount , order_quantity FROM ( ( SELECT   product_category , year , 1 month , year time , 1 sequence , SUM (month_order_amount) order_amount , SUM (month_order_quantity) order_quantity FROM   month_end_sales_order_fact a , product_dim b , month_dim c WHERE     a.product_sk = b.product_sk AND a.month_order_sk = c.month_sk AND year = 2006 GROUP BY   product_category , year ) UNION ALL ( SELECT   product_category , year , month , quarter time , 2 sequence , SUM (month_order_amount) order_amount , SUM (month_order_quantity) order_quantity FROM   month_end_sales_order_fact a , product_dim b , month_dim c WHERE     a.product_sk = b.product_sk AND a.month_order_sk = c.month_sk AND year = 2006 GROUP BY product_category, year, quarter ) UNION ALL ( SELECT   product_category , year , month , month_name time , 3 sequence , SUM (month_order_amount) order_amount , SUM (month_order_quantity) order_quantity FROM   month_end_sales_order_fact a , product_dim b , month_dim c WHERE     a.product_sk = b.product_sk AND a.month_order_sk = c.month_sk AND year = 2006 GROUP BY   product_category , year , quarter , month ) ) x ORDER BY   product_category , year , month , sequence ; /* end of script                                                 */

Run the script in Listing 16.4 using this command.

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

Here is the query result.

 Database changed +------------------+----------+--------------+----------------+ | product_category | time     | order_amount | order_quantity |  +------------------+----------+--------------+----------------+ | Monitor          | 2006     |      8500.00 |           NULL | | Monitor          | 1        |      1000.00 |           NULL | | Monitor          | January  |      1000.00 |           NULL | | Monitor          | 2        |      2500.00 |           NULL | | Monitor          | April    |      2500.00 |           NULL | | Monitor          | 3        |      4000.00 |           NULL | | Monitor          | July     |      4000.00 |           NULL | | Monitor          | 4        |      1000.00 |           NULL | | Monitor          | October  |      1000.00 |           NULL | | Storage          | 2006     |     15000.00 |           NULL | | Storage          | 1        |      3000.00 |           NULL | | Storage          | February |      1000.00 |           NULL | | Storage          | March    |      2000.00 |           NULL | | Storage          | 2        |      6500.00 |           NULL | | Storage          | May      |      3000.00 |           NULL | | Storage          | June     |      3500.00 |           NULL | | Storage          | 3        |      5500.00 |           NULL | | Storage          | August   |      4500.00 |           NULL | | Storage          | September|      1000.00 |           NULL |  +------------------+----------+--------------+----------------+ 19 rows in set (0.02 sec)

The second query, presented in Listing 16.5, drills the campaign session year-campaign-month hierarchy. This query has the same structure as the first one, except that it groups by campaign and not by quarter.

Listing 16.5: Drilling the campaign session path

 /*****************************************************************/ /*                                                               */ /* campaign_session_path.sql                                     */ /*                                                               */ /*****************************************************************/ SELECT   product_category pc , time , order_amount amt , order_quantity qty FROM ( ( SELECT   product_category , year , 1 month , year time , 1 sequence , SUM (month_order_amount) order_amount , SUM (month_order_quantity) order_quantity FROM   month_end_sales_order_fact a  , product_dim b , month_dim c WHERE     a.product_sk = b.product_sk AND a.month_order_sk = c.month_sk AND year - 2006 GROUP BY   product_category , year ) UNION ALL ( SELECT   product_category , year , month , campaign_session time , 2 sequence , SUM (month_order_amount) order_amount , SUM (month_order_quantity) order_quantity FROM   month_end_sales_order_fact a , product_dim b , month_dim c WHERE     a.product_sk = b.product_sk AND a.month_order_sk = c.month_sk AND year = 2006 GROUP BY   product_category , year , campaign_session ) UNION ALL ( SELECT   product_category , year , month , month_name time , 3 sequence , SUM (month_order_amount) order_amount , SUM (month_order_quantity) order_quantity FROM   month_end_sales_order_fact a , product_dim b , month_dim c WHERE     a.product_sk = b.product_sk AND a.month_order_sk = c.month_sk AND year = 2006 GROUP BY   product_category , year , campaign_session , month_name ) ) x ORDER BY   product_category , year , month , sequence ; /* end of script                                                 */

Run the query in Listing 16.5 using this command.

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

Here is the query result.

 +------------+----------------------+----------+------+ | pc         | time                 | amt      | qty  | +------------+----------------------+----------+------+ | Monitor    | 2006                 |  8500.00 | NULL | | Monitor    | 2006 First Campaign  |  3500.00 | NULL | | Monitor    | January              |  1000.00 | NULL | | Monitor    | April                |  2500.00 | NULL | | Monitor    | 2006 Second Campaign |  4000.00 | NULL | | Monitor    | July                 |  4000.00 | NULL | | Monitor    | 2006 Last Campaign   |  1000.00 | NULL | | Monitor    | October              |  1000.00 | NULL | | Storage    | 2006                 | 15000.00 | NULL | | Storage    | 2006  First Campaign |  3000.00 | NULL | | Storage    | February             |  1000.00 | NULL | | Storage    | March                |  2000.00 | NULL | | Storage    | 2006 Second Campaign |  6500.00 | NULL | | Storage    | May                  |  3000.00 | NULL | | Storage    | June                 |  3500.00 | NULL | | Storage    | 2006 Third Campaign  |  4500.00 | NULL | | Storage    | August               |  4500.00 | NULL | | Storage    | 2006 Last Campaign   |  1000.00 | NULL | | Storage    | September            |  1000.00 | NULL |  +------------+----------------------+----------+------+ 19 rows in set (0.00 sec)