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

 /*****************************************************************/ /*                                                               */ /* 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                                                 */

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.