MDX Part-2(Tuples,Axes, and Sets)

Tuples, Axes, and Set are the  most basic and most common components in the MDX.


Tuples:A tuple is a collection of members, each of which is selected from a different dimension.
For example: (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.


A tuple can be composed of any and all dimensions, including,measures dimension.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.


[Dimension].[Member]------->or {[Dimension].[Member]}--->Single Member / Single Dimension


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


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 . Every intersection of multiple tuples results in yet another tuple.


Axes:
An axis is a group, or collection, of members from one or more dimensions, organized as tuples. 

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

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

The ON COLUMNS and ON ROWS statements indicate the designated axes for the sets they follow.

A point we need to understand is that the ON COLUMNS and ON ROWS terms are aliases for axis names. why a single-axis report must include a COLUMNSaxis, but not a ROWS axis.

According to the documentation  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.

SET:

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.

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. 

Braces are often required to make the syntax work correctly, but can sometimes be optional.

In above MDX we specified only one axis. 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

Example: Suppose wants to 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 row.

SELECT {([Time].[1997]), ([Time].[1998])}ON COLUMNS, {[Pay Type].[Pay Type].Members} ON ROWS FROM HR WHERE ([Measures].[Count])