MDX Axis Specifications

<axis_specification> :: = 
  [NON EMPTY] <set> ON COLUMNS|ROWS|PAGES|CHAPTERS|SECTIONS|AXIS(<unsigned_integer>)

Understanding the following concepts will help you construct axis specifications for many SELECT queries to Essbase databases.

Ordering of Axes

If providing multiple axes, you cannot skip axes. For example, you can specify a Row axis only if you have a Column axis. You can specify a Pages axis only if you also have Column and Row axes.

You can also use ordinals to represent the axes. For example, you can specify <set> ON AXIS(0), <set> ON AXIS(1), etc.

You can specify up to 64 axes (though it is common to use just two). The first five ordinal axes have keyword aliases:

Table 4-4 Axis Keywords and Corresponding Ordinal Notation

Axis Keyword	Axis Ordinal
`COLUMNS`	`AXIS(0)` (default if nothing specified)
`ROWS`	`AXIS(1)`
`PAGES`	`AXIS(2)`
`CHAPTERS`	`AXIS(3)`
`SECTIONS`	`AXIS(4)`

For example:

SELECT set1 ON COLUMNS,
set2 ON ROWS
FROM Sample.Basic

is the same as:

SELECT set1 ON AXIS(0),
set2 ON AXIS(1)
FROM Sample.Basic

Both return a hypothetical data cube (or subset) of the following format:

Table 4-5 Hypothetical Subset of Data

(axis)	Member names in set1
Member names in set2	Data at intersections of set1 and set2 members

The examples above are hypothetical because they will not return a cube until values are provided for the sets. In the following example, we replace set1 and set2 with real sets:

SELECT
{[100-10], [100-20]} ON COLUMNS,
{[Qtr1], [Qtr2], [Qtr3], [Qtr4]} ON ROWS
FROM Sample.Basic

which returns the following results:

Table 4-6 Output Grid from MDX Example

(axis)	100-10	100-20
Qtr1	5096	1359
Qtr2	5892	1534
Qtr3	6583	1528
Qtr4	5206	1287

Specifying the Set

You can represent the sets in each axis in many ways.

SELECT
{ }
ON COLUMNS
from sample.basic

illustrates that you can choose nothing for a set. However, no cell values will be returned. The following rules apply:

When any of the axes contains an empty set, no cell values are returned. The axes whose sets have at least one tuple will have their tuples returned.
If there are no axes at all, then exactly one cell is returned using the default member of each dimension. The slicer tuple, if present, overrides the default member for the respective dimensions.

SELECT
{ ( [Year].[Qtr2] ) }
ON COLUMNS
from sample.basic

illustrates using a set that contains a single tuple.

For more information about sets, see MDX Set Specification.

NON EMPTY

The axis specification syntax including NON EMPTY is shown below:

<axis_specification> ::= 
        [NON EMPTY] <set> ON 
        COLUMNS | ROWS | PAGES | CHAPTERS | 
        SECTIONS | AXIS (<unsigned_integer>)

Including the optional keywords NON EMPTY before the set specification in an axis causes suppression of slices in that axis that would contain entirely #MISSING values.

For any given tuple on an axis (such as (Qtr1, Actual)), a slice consists of the cells arising from combining this tuple with all tuples of all other axes. If all of these cell values are #MISSING, the NON EMPTY keyword causes the tuple to be eliminated.

For example, if even one value in a row is not empty, the entire row is returned. Including NON EMPTY at the beginning of the row axis specification would eliminate the following row slice from the set returned by a query:

Table 4-7 Output Grid from MDX Example

Qtr1
Actual	#Missing	#Missing	#Missing	#Missing	#Missing

For another example, see the Tail function.

Note:

NON EMPTY syntax is not supported in an MDX sub select axis specification.

To provide the best data export performance in MDX, any NON EMPTY specification on an axis is ignored for MDX Export.

Dimension Properties

A property, in MDX grammar, refers to the Essbase concepts of attributes and UDAs.

The axis specification syntax including the properties specification is shown below:

<axis_specification> ::= 
        [NON EMPTY] <set> [<dim_props>] ON 
        COLUMNS | ROWS | PAGES | CHAPTERS | 
        SECTIONS | AXIS (<unsigned_integer>)

As shown in the above syntax, a properties specification can follow the set specification in an axis.

For more information about properties, see About MDX Properties.