MDX Essentials: Structure of the MDX Data Model

Monday Dec 16th 2002 by William Pearson
Share:

Join Author Bill Pearson in an exploration of the MDX Data Model. In this article, we expose the syntax for the basic building blocks of the MDX query, tuples, sets, and axes, and set the stage for exploring more advanced components and syntax in subsequent articles.


Join Author Bill Pearson in an exploration of the MDX Data Model. In this article, we expose the syntax for the basic building blocks of the MDX query, tuples, sets, and axes, and set the stage for more advanced components and syntax in subsequent articles.


About the Series...

This is the second article of the series, MDX Essentials. The primary focus of this series will be an introduction to the MDX language. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and system requirements needed for getting the most out of the series, please see the first article, MDX at First Glance: Introduction to MDX Essentials.


What We Accomplished in our Last Article

In the first article of the series, we introduced the MDX query in its simplest form. We took a look at some of the basic keywords and then began a discussion of the element(s) of the MDX language that will carry forward as we progress through the series. We performed practice activities, as we will do throughout the entire MDX Essentials series, to reinforce the concepts we introduced.

We explored the rudiments of MDX queries from a syntax perspective and introduced the respective semantics as they arose. We provided a brief introduction to MDX, and then discussed several basic keywords commonly used within its syntax. We examined a breakdown of a simple MDX query into its component parts, comparing and contrasting MDX to SQL where useful. Finally, we created and executed basic MDX queries that served to both demonstrate and reinforce our discussions surrounding keywords and components.


Introduction to the Structure of the MDX Data Model

In this article, we will introduce the MDX data model, together with numerous of its most basic and most common components. These components will include tuples, axes, and sets. We will focus on the composition and uses of, and provide hands-on exposure to, these syntactical building blocks. Rules of syntax will be emphasized and will provide a basis for more complex query building as we progress through the series. Finally, we will work practice exercises to demonstrate tangible results, and to reinforce our discussions with examples.

This lesson will include:

  • A brief discussion of tuples and sets, the building blocks of MDX queries;
  • An examination of axes and their role in the data model;
  • A examination of general MDX query results;
  • Illustrations and examples of the concepts presented throughout the session.

Let's begin by discussing the MDX data model and the components that underpin it, with an eye toward gaining a grasp of how the respective elements work and interact to provide us with the robust functionality that is the MDX language.


Page 2: The Building Blocks: Tuples and Sets



The Building Blocks: Tuples and Sets

Many of us have, by now, come across the argument surrounding the pronunciation of the word "tuple" that rages within the OLAP and associated arenas. Few disagree, however, as to the importance of the role that the tuple plays in MDX syntax in general, in set construction specifically, and in overall expression building. Axes, too, play an important role in locating values along the dimension members they represent. In addition, sets comprise the next level of construction, and share importance as a significant building block within MDX.

We will take a look at these three basic elements of multidimensional structures first, as we prepare for subsequent lessons covering more advanced components and concepts within the MDX language. We will perform straightforward practice exercises at appropriate junctures to reinforce our understanding of the concepts as we introduce them.

Tuples

Discussion

A tuple is a collection of members, each of which is selected from a different dimension. To illustrate, (Sales, 1997) conceptually represents a tuple that is composed of members that might exist in an OLAP cube from two dimensions: measures (recall that measures are yet another dimension from a syntactical perspective) and time. The tuple (Non-Consumable, Sales, 1997) is composed of members of three different dimensions: product, measures, and time. The tuple is the basic unit for forming an axis, which we will discuss below.

An important thing to remember when considering tuples is that, while they can be composed of one or more members from a number of dimensions, only one member from each dimension can exist in a given tuple. A tuple can be composed of any and all dimensions, including, as we have already noted, the measures dimension.

Syntax

The syntax for a tuple follows a few basic rules, the chief of which are detailed in Table 1 below:


 

 

 

 

 

 

 

 

 

SPECIFICATION

 

GENERAL RULE

 

SYNTAX            

 

NOTES

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Single Member / Single Dimension

 

List dimension alone

 

[Dimension].[Member]

 

(1)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Multiple Members / Multiple Dimensions

 

Surround tuple membership in parentheses

 

([Dim1].[Member], [Dim2].[Member], [Dim3].[Member])

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NOTES:

 

 

 

 

 

 

 

(1) Single members can be enclosed within parentheses, but are not required to be.

 

 

 

 

 

 

 

 

Table 1: Syntax Table: Tuples (General)


With square brackets "[ ]" serving as delimiting characters, MDX uses tuples to identify cell "addresses." Any cell value can be mapped using a tuple made up of one distinct member from each of a cube's dimensions. We can project dimensions to columns, rows, pages and other axes (see the Axes section below). An important concept to carry away from this section is that every intersection of multiple tuples results in yet another tuple. The ultimate outcome off combining all the tuples in a query is, after all, a cell with a value that is defined by the coordinates provided within the "grand tuple." In usage, a tuple refers to either a combination of members (the "grand tuple") -- and the cell that the combination defines (an "address", as it were) -- or to the value or values in the cell or cells that is specified by the tuple.

We will revisit this concept many times over the series, so as to examine it and understand it from multiple perspectives. Seeing concepts in action is often the best way to learn, and there will be ample opportunity to do so as we progress.


Page 3: Axes



Axes

Discussion

An axis is a group, or collection, of members from one or more dimensions, organized as tuples. Principle uses of the axis include placing or filtering specific cube values along the dimension members with which they are associated.

An illustration might be a query that returns head count, by pay type and month, for a given department. In this case, three axes could be used in presenting the result of the query, with an axis representing each of the three dimensions (Pay Type, Time and Department).

We will later explore options for reducing the number of axes in our illustration, for example, by nesting the pay type and department results in an axis with tuples. In this manner, we would combine the two dimensions in a tuple, which we would then designate as the axis, thus projecting a member from each dimension onto the axis. The Time dimension would project onto the other axis. This axis could then contain tuples to represent time values, such as April 1997, April 1998, and so forth.

Syntax

The syntax for an axis consists of axis specification, and occurs within a query just after the SELECT keyword. To illustrate, in the following excerpt from a query, the ON COLUMNS and ON ROWS statements indicate the designated axes for the sets they follow.

([Time].[1998],[Department].[All Department].[HQ Finance and Accounting])} ON COLUMNS,
{[Pay Type].[Pay Type].Members} ON ROWS

The example is excerpted from a query with only two axes ("x" and "y," as it were), that uses sets, which we will discuss next (to display more than one cell in a result grid, we have to place a set of members on an axis).

A point we need to understand is that the ON COLUMNS and ON ROWS terms are aliases for axis names. We can see, using the aliases, why a single-axis report must include a COLUMNS axis, but not a ROWS axis. According to the documentation (Books Online, which is installed by default with MSSQL Server 2000/Analysis Services in a Typical installation, as well as other sources), a query can specify up to 63 axes, for which alias names exist only for the first few. Suffice it to say, however, that it is rare to use more than a couple of heading axes in a standard reporting scenario.


Sets

Discussion

The set is an important component of MDX syntax. Sets are typically enclosed by curled "{ }" braces and often appear in the SELECT part of a query, acting to specify the cells of a cube that are to be returned by the query. MDX is similar to any other programming language in that it uses various operators to identify sets. These operators include the colon and comma (used as separators between members within sets), among others (we will visit these individually, beginning with our next lesson).

A set typically consists of a group of one or more tuples. The order of the tuples is significant, and the same tuple can be repeated in an MDX set. Similar to the way that the term "tuple" can refer to a combination of members, or to the value(s) in the cell(s) that is (are) specified by the tuple, a set can reference the set of member combinations that it contains or the value(s) contained in the cell(s) specified by its tuples.

Syntax

As we have noted, sets are typically enclosed by curled "{ }" braces and often appear in the SELECT part of a query. Indeed, the curled braces specify, to the program involved, that the enclosed members make up a set. Sets can be specified other ways, as we shall see later in the series. Braces are often required to make the syntax work correctly, but can sometimes be optional. We will "default" to a consistent style and use braces whenever appropriate (optional or mandatory) throughout the MDX Essentials series.


Page 4: Practical Examples



Practical Examples

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

  1. Click OK.

The MDX Sample Application window appears.

  1. Clear the top area (the Query pane) of any queries or remnants that might appear.
  2. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.
  3. 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


  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.



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.

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


Page 5: Practical Example (Continued)



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



Next in Our Series ...

In this article, we introduced the MDX data model and its most basic components, namely tuples, axes, and sets. We focused on the composition and uses of tuples, axes and sets, and provided hands-on exposure to these building blocks. After discussing each of the three components, we emphasized rules of syntax that related to each, providing a basis to build upon as we progress through the series. Finally, we worked practice exercises to demonstrate tangible results and to reinforce our discussions with examples.

In our next lesson, MDX Operators and Functions: The Basics, we will introduce additional ways to construct tuples and sets, taking up first the most basic of the components involved. The operators we explore in this lesson will include curled braces "{ }", commas "," and colons ";", and members. With each or the operators, and especially with the members operator, we will illustrate the uses and options that are available to us in constructing queries that are basic in nature.


See All Articles by Columnist William E. Pearson, III


Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved