Chapter 11: Pulling It Out and Building It UpMDX Queries

I've never been convinced that experience is linear, circular, or even random. It just is. I try to put it in some kind of order to extract meaning from it, to bring meaning to it.
—Toni Cade Bambara,
U.S. fiction writer

Overview

We now turn our attention from building and maintaining OLAP cubes to extracting the information resting within them. After all, this is the whole reason these cubes exist. To do this extraction, we utilize MDX queries. As a reminder, the acronym MDX stands for Multidimensional Expression language.

We have been looking at MDX expressions since Chapter 8. In this chapter, we look at MDX statements. An MDX statement, specifically the SELECT statement, provides the mechanism for querying information. We also look at other MDX statements in addition to the SELECT statement. These statements enable us to manipulate the data in an OLAP cube. They also let us build a cube from scratch.

Many visual tools are available for extracting information from a cube. We worked with one of these when we used the Browser tab in the Business Intelligence Development Studio in Chapter 10. This tool enables us to drag-and-drop dimensions and measures to pull data from the cube. Of course, the Business Intelligence Development Studio is one big visual tool for defining and managing OLAP cubes.

Why, when we have such capable tools for defining cubes and extracting their information, do we need to learn anything about MDX statements? The answer is this: it creates character, and helps build strong bones and muscles. Well, perhaps not. The real answer is, It gives us a deeper understanding of how these visual tools operate. After all, the Business Intelligence Development Tool creates MDX statements behind the scenes to perform the information extraction and cube management activities we ask of it.

In addition, at times, it is more convenient and, perhaps, even faster to use an MDX statement to accomplish a goal, rather than using a visual interface. When we are first creating or deploying a cube we need to verify that everything is set up correctly. At these times, we want to look at the raw information and not have a visual tool adding layers between us and the cube.

With that in mind, let's begin with the MDX SELECT statement.