Chapter 4: Dimensional Queries


A dimensional query is a query in a dimensional data warehouse that joins the fact table and the dimension tables on one or more surrogate keys. This chapter teaches you the dimensional query pattern and how to apply it to the three most common query types: aggregation, specific, and inside out.

Aggregate queries aggregate individual facts, for example, by adding up measure values. In a specific query you query the facts of a specific dimension value. While most queries specify one or more dimensions as the selection criteria (constraints), an inside out query's criteria are measure values. Understanding these three most common query types allows you to apply dimensional queries to other query types.

Applying Dimensional Queries

In this section I explain how you can apply dimensional queries on the three most common query types: aggregate, specific, and inside-out queries.

To apply dimensional queries, you first need to add data to your data warehouse by running the script in Listing 4.1. You need this additional data to test the dimensional queries in Listings 4.2 to 4.7.

Listing 4.1: Script for that adds data for testing dimensional queries

image from book
 /*****************************************************************/ /*                                                               */ /* dimensional_query_data.sql                                    */ /*                                                               */ /*****************************************************************/ USE dw; INSERT INTO order dim VALUES   (NULL, 11, CURRENT_DATE, '9999-12-31') , (NULL, 12, CURRENT_DATE, '9999-12-31') , (NULL, 13, CURRENT_DATE, '9999-12-31') , (NULL, 14, CURRENT_DATE, '9999-12-31') , (NULL, 15, CURRENT_DATE, '9999-12-31') , (NULL, 16, CURRENT_DATE, '9999-12-31') ; INSERT INTO date_dim VALUES   (NULL, '20075-0211-016', 'FebruaryNovember', 112, 41, 20057,       CURRENT_DATE, '9999-12-31') ; INSERT INTO sales_order_fact VALUES   (11, 1, 2, 2, 20000) , (12, 2, 3, 2, 25000) , (13, 3, 4, 2, 30000) , (14, 4, 2, 2, 35000) , (15, 5, 3, 2, 40000) , (16, 1, 4, 2, 45000) ; /* end of script                                                 */
image from book

Before you start, change your MySQL date to February 6, 2007; and run the script in Listing 4.1 to insert six orders into the order_dim table, one date into the date_dim table, and six sales orders into the sales_order_fact table.

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

You’ll see this on your MySQL console.

 Database changed Query OK, 6 rows affected (0.05 sec) Records: 6  Duplicates: 0  Warnings: 0 Query OK, 1 row affected (0.06 sec) Query OK, 6 rows affected (0.06 sec) Records: 6  Duplicates: 0  Warnings: 0

Now that you have the necessary data, you’re ready to apply dimensional queries to the three types of queries mentioned earlier.



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