Inside-Out Queries


While the preceding queries have dimensional constraints (the selection of facts is based on dimensions), an inside out dimensional query selects the facts based on one or more measure values. In other words, your query starts from the fact (the centre of the star schema) towards the dimensions, hence the name inside-out query. The following two examples are inside-out dimensional queries.

Product Performer

The dimensional query in Listing 4.6 gives you the sales orders of products that have a monthly sales amount of 75,000 or more.

Listing 4.6: Inside-out - Monthly Product Performer

image from book
 /*****************************************************************/ /*                                                               */ /* monthly_product_performer.sql                                 */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   month_name , year , product_name , SUM (order_amount) , COUNT(*) 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   month_name , year , product_name HAVING SUM (order_amount) >= 75000 ORDER BY   month , year , product_name ; /* end of script                                                 */
image from book

Run the script using this command.

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

Here is the result of the query:

 Database changed +------------+------+------------+-------------------+----------+ | month_name | year |product_name| SUM (order_amount)| COUNT(*) | +------------+------+------------+-------------------+----------+ | November   | 2007 | LCD Panel  |           75000.00|        2 | +------------+------+------------+-------------------+----------+ 1 row in set (0.00 sec)

The query output shows the total order amounts (sum) and the number of orders (count) for LCD Panel, the only product that has a total order amount that is equal to or greater than 75000.

Loyal Customer

The query in Listing 4.7 is a more complex inside out query than the one in Listing 4.6. If your users would like to know which customer(s) placed more than five orders annually in the past eighteen months, you can use the query in Listing 4.7. This query shows that even for such a complex query, you can still use a dimensional query.

Listing 4.7: Inside-out (loyal customer)

image from book
 /*****************************************************************/ /*                                                               */ /* loyal_customer.sql                                            */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   customer_number , year , COUNT(*) 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_number , year HAVING     COUNT(*) > 3 AND (12 - MONTH (MAX (date))) < 7 ; /* end of script                                                 */
image from book

Run the script using this command.

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

Here is the output of the query:

 Database changed +-----------------+------+----------+ | customer_number | year | COUNT(*) | +-----------------+------+----------+ |               1 | 2007 |        4 | +-----------------+------+----------+ 1 row in set (0.02 sec)

The query result shows the number of orders (count) from customer_number 1, the only customer that meets the above selection criteria.



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