MDX Essentials: Structure of the MDX Data Model - Page 5

December 16, 2002

Now let's expand the query beyond its current selection. Suppose the information consumer asks that we add a couple of features to the query result. He asks that we again provide total headcount for the years 1997 and 1998 for the entire company, but this time he wants us to break out the current pay types to show composition of the workers in our totals. Again, we are to provide the information in a two-dimensional grid, with the years as column headings. He states that he wants to see the pay types in the rows.

  1. Select File -> New from the top menu.
  2. Type the following query into the Query pane:
--MDX02-2: Tutorial Query No. 2 SELECT {([Time].[1997]), ([Time].[1998])}ON COLUMNS, {[Pay Type].[Pay Type].Members} ON ROWS FROM HR WHERE ([Measures].[Count])

The diagram below labels the various parts of the query:

Illustration 5: Labeled Parts of a Basic MDX Query

  1. Click Query on the top menu, and then select Run.

We see the results below, which appear in the Query pane as soon as Analysis Services fills the cells that it determines to be specified by the query. As is apparent, the two pay types within the organization are hourly and salaried.

Illustration 6: The Initial Query Results

The query delivers the results that were requested by the information consumer. A quick review indicates that the values associated with the two pay types total to the values we received in the first query (MDX02-01) above. (We will discuss the .members components in the next lesson).

  1. Save the query as MDX02-2.
  2. Exit the Sample Application.

As we move into the stages of query building, from basic to advanced, we will constantly reinforce the concept that an MDX query result consists of another cube. All queries return cells, and the end consumer may be interested in either the data in the respective cells, the members that are associated with the data, properties of those members, or a combination of these. We will look at examples of each of these from various perspectives as we progress through the series.

Page 6: Next in Our Series