Specifying the Contents of an Axis

The number of axes and the contents of each axis are specified after the SELECT keyword and immediately before the FROM clause in the expression template shown in “Parts of an MDX Statement.”

Each <axis_specification> value specifies one axis. The number of axes in the dataset is equal to the number of <axis_specification> values in the MDX statement.

To understand what constitutes an <axis_specification> value, we must take a look at the nature of an axis.

Contents of the x-Axis

In the dataset of the section “Cube and Dataset Example,” the x-axis of the dataset has two nested dimensions: Geography and SalesPerson. From the Geography dimension, four members are selected: Seattle, Boston, USA_South, Japan. Two members are selected from the SalesPerson dimension: Venkatrao and Netz. This yields a total of eight coordinates on this axis (8 = 4*2).

You can represent each coordinate as a tuple with two members — one from the SalesPerson dimension and another from the Geography dimension — as follows:

(Venkatrao, Seattle), (Venkatrao, Boston), (Venkatrao, USA_South), 
(Venkatrao, Japan), (Netz, Seattle), (Netz, Boston), (Netz, USA_South), 
(Netz, Japan)

You can consider an axis as being composed of a set of tuples. If there is only one dimension on an axis, then each tuple is just a member. If there are two dimensions, then two components compose each tuple with each component being a member of one of the dimensions. In general, if there are n dimensions nested on an axis, then n components compose each tuple where each component is a member of one of the nested dimensions.

Contents of the y-Axis

The y-axis of the dataset has the following set of tuples. (In this case, each tuple has just one component, making it the same as a member because there is only one dimension on this axis.)

Jan, Feb, Mar, Qtr2, Qtr3, Oct, Nov, Dec

Set, Tuple, and Member Specification

An axis contains a set of tuples. A tuple is a vector of members. To specify the coordinates of an axis:

Create an expression that generates members. Some expressions result in a single member while others result in a set of members.
Obtain the desired set of members for each dimension on the axis.
Take a cross product of these member sets to yield a set of tuples that represents an axis.

An application can use the simple expression syntax of OLE DB for OLAP to perform these operations. For the complete syntax of these expressions, see Appendix B, “MDX Grammar.” The following is a brief look at some of the important concepts.

There are many ways of choosing a set of members:

Enumeration of members. The easiest way to specify a set of members is simply to list the desired members:
```
{Venkatrao, Netz}
```
Set expressions. A set function generates a new set of members. For example, the function
```
USA.CHILDREN
```
generates the set
```
{USA_North, USA_South}
```
A comma-separated list of set expressions also results in a set. For example, the expression
```
{USA_North.CHILDREN, USA_South, Japan}
```
results in the set
```
{Seattle, Boston, USA_South, Japan}
```

Other ways of set specification make use of the natural order of members in a dimension. For example, the expression

Netz:Pizzo

on the SalesPerson dimension, whose members are naturally ordered by last name, yields the set

{Netz, Nakonieczny, Oks, Pace, Patel, Patterson, Pederson, Perin, Pizzo}

Note The expression is an inclusive range. That is, the members on both sides of the colon ( : ) specifier are included.

After an application generates sets that contain the members that it wants from each dimension on a given axis, it can use the CROSSJOIN function to get a cross product of these sets.

Putting all these concepts together, a <set_expression> value for the x-axis in the example dataset is

CROSSJOIN({Venkatrao, Netz},
{USA_North.CHILDREN, USA_South, Japan})

The expression for the y-axis in the example dataset is

{Qtr1.CHILDREN, Qtr2, Qtr3, Qtr4.CHILDREN}

Note The MDX syntax is much more extensive than what is shown in these few examples. Some functionality not covered here includes:

Set operations, such as UNION or INTERSECT.
Functions to order sets, such as ORDER or HIERARCHIZE.
Filter functions, such as TOPCOUNT, TOPPERCENT, BOTTOMCOUNT, BOTTOMPERCENT, or FILTER.
Tree navigation functions, such as DESCENDANTS.
Chapter 3, “Advanced MDX,” covers MDX in more detail. Appendix B, “MDX Grammar,” has the full syntax of MDX.

Projecting Tuple Sets On Axes

After an application has defined the set of tuples for each axis, it needs to specify which set is projected on which axis. Going back to the query template in the section “Parts of an MDX Statement,” there is an <axis_specification> value for each axis in the dataset.

The <axis_specification> syntax looks like the following:

<axis_specification> ::= <set> ON <axis_name>
<axis_name> ::= COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS
              | AXIS(index)

Each axis is associated with a number: 0 for the x-axis, 1 for the y-axis, 2 for the z-axis, and so on. To project a set on an axis, an application uses the syntax AXIS(<index>). The <index> value is the axis number. The first five axes — COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS — can be used in place of AXIS(0), AXIS(1), AXIS(2), AXIS(3), and AXIS(4), respectively.

It is an error for an MDX statement to skip axes, or to not start from axis 0. That is, every statement must start from the COLUMNS axis, and must not miss any intermediate axes, such as having COLUMNS and PAGES axes but no ROWS axis.

Note OLE DB for OLAP does not impose any restrictions on the number of axes supported in a dataset other than the minimum requirement of three for OLE DB for OLAP compliance. To find out the number of axes supported by the provider, the application can check the value of the MDPROP_AXES property in the DBPROPSET_DATASOURCEINFO property set.

The SELECT List

The SELECT list for the example dataset follows:

SELECT CROSSJOIN({Venkatrao, Netz},
{USA_North.CHILDREN, USA_South, Japan}) ON COLUMNS,
{Qtr1.CHILDREN, Qtr2, Qtr3, Qtr4.CHILDREN} ON ROWS

Discontiguous Axes in SELECT List

You should not specify a SELECT list where the axes are not contiguous. For instance, it is an error to have ROWS and PAGES, but not COLUMNS; or to have COLUMNS and PAGES, but not ROWS.

However, you may specify a SELECT list with no axes. It is not an error to have an empty SELECT list. In this case, all dimensions are slicer dimensions, and the MDX statement selects at most one cell.