Set, Tuple, and Member Specification

An axis contains a set of tuples. A tuple is an ordered collection of members from different dimensions or a single member from one dimension. One way to specify the set of tuples on an axis with two or more dimensions is to:

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

You can use the MDX syntax and Crossjoin function to perform these operations. For the complete MDX syntax, see the OLE DB documentation. A brief description of some of the important concepts follows.

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:

  {Smith, Jones}

• Set functions. 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 functions 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, use the inclusive colon operation in the expression

Jones:Pizzo

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

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

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

After you generate sets that contain the members that you want from each dimension on a given axis, you can use the Crossjoin function to get a cross product of these sets.

Putting all these concepts together, the <set> value for the x-axis of the example dataset shown in Cube and Dataset Example is

CROSSJOIN({Smith, Jones},

{USA_North.CHILDREN, USA_South, Japan})

The <set> value for the y-axis in the example dataset is

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

The complete MDX syntax is much more extensive than that shown in these few examples. Some functionality not covered in this topic includes the following subjects:

• Set functions, 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

For more information about MDX and MDX syntax, see the OLE DB documentation. For more information about the MDX functions provided with Microsoft® SQL Server™ OLAP Services, see Calculated Member Builder.