MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 26: Mapping MDX to SQL Statements

Tables and Sets

When mapping a statement in MDX, a set maps to a table, which reflects the dimensionality of the set.

Dimensionality of a Set

Many MDX functions take a set or sets as input and return a set as output. A set consists of tuples. A tuple is a vector of members. Let n be the number of members in any given tuple. The value n is the dimensionality of the set. The following rules apply:

• Each member of the tuple must be from a different dimension.
  
  
• The dimension of the pth member of a tuple is the same across all tuples. (The value of p ranges from 1 to n.)

Table Associated with a Set

The set, specifically the set of axis, maps to a table. This table has n+1 columns, where n is the dimensionality of the set. There is one column for each dimension, and there is a RANK column that has the rank of a tuple within the set. This column is needed because sets in MDX are ordered, while a table in SQL is not.

Any intermediate table in the expansion of an MDX statement also has a RANK column. The expansions below use this implicit column in an ORDER BY clause to preserve the order. Note that the notion of rank is somewhat similar to that of an identity column.

Use of ORDER BY

Many sections in this document deal with how to expand MDX set value expressions into equivalent SQL statements. For set value expressions that return another set (such as ORDER and CROSSJOIN), the final result of the expansion is a SELECT statement (which returns a virtual table, also known as a set). This SELECT statement always contains an ORDER BY on the RANK column. This is appropriate for a section that deals only with mapping a set value expression. However, it is inefficient to do this when evaluating a full-fledged MDX statement that has multiple such functions along an axis. In that case, the ORDER BY is done at the end after evaluating the entire MDX statement.