MDX Essentials: Structure of the MDX Data Model

Because we have covered enough of the basics to begin practicing some of the concepts, let's build a simple query and reinforce our understanding. We will return to the MDX Sample Application (installed with Analysis Services, and discussed further in Article One, MDX at First Glance: Introduction to MDX Essentials), and take the following steps, to practice interacting with Analysis Services in a hands-on manner.

1. Go to the Start button on the PC, and then navigate to Microsoft SQL Server -> Analysis Services.
2. Click MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Illustration 1 below.

Illustration 1: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of one of my servers, RAPHAEL, and properly indicates that we will be connecting via the MSOLAP provider (the default).

3. Click OK.

The MDX Sample Application window appears.

4. Clear the top area (the Query pane) of any queries or remnants that might appear.
5. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.
6. Select the HR cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown below, complete with the information from the HR cube displaying in the Metadata tree (in the left section of the Metadata pane, between the Query pane at the top of the application window and the Results pane, at the bottom.).

Illustration 2: The MDX Sample Application Window (Compressed)

We will begin with an illustration: We are asked by an information consumer in Human Relations to provide the total headcount for the years 1997 and 1998 individually for the entire organization.

1. Type the following query into the Query pane:

-- MDX02-1:  Tutorial Query No. 1

SELECT

{([Time].[1997]), ([Time].[1998])}ON COLUMNS

FROM HR

WHERE
([Measures].[Count])

The diagram below labels the various parts of the query:

Illustration 3: Labeled Parts of a Basic MDX Query

2. 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.

Illustration 4: The Initial Query Results

Note: For an explanation of the comment line, together with other general discussion regarding attributes of the basic MDX query structure, see the first article of the series.

The query delivers the results that were requested by the information consumer. The results display columns, but no rows, because we specified only one axis. We will add specification of the Row axis in the next example.

Anytime we use single tuples, such as we do in specifying the "x" axis above (our only axis), we enclose the tuples in curled braces to signify that they are tuples contained within a set, as shown.

3. Save the query by selecting File -> Save As, and call the file MDX02-1.

Page 5: Practical Example (Continued)