dcsimg

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

December 16, 2002


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









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers