Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 16, 2002

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

By William Pearson



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.


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.



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.


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM