MDX Part-3(MDX Operators:curled braces "{}", commas "," and colons ":")

Introduction to Basic Operators: 

curled braces "{}", commas "," and colons ":"

Curled Braces:

The way that MDX distinguishes sets: it surrounds members or tuples with curly braces (the { and } characters.) We are required to surround tuples with braces anytime the tuples are explicitly listed.

MDX uses the brace characters to "package" a set of components from a single dimension or a set of dimensions. The following example query makes use of the curly braces to specify the results we wish to see:

SELECT 
    {[Measures].[Units Shipped]} ON COLUMNS, 
    {[Store].[Store State].[CA], 

[Store].[Store State].[OR],

[Store].[Store State].[WA]} ON ROWS
FROM [Warehouse]

The expression above requests the total units shipped for the stores, as summarized specifically for the states of California, Oregon and Washington. It does this by selecting three single members of the Store dimension, the states in which they reside, and returns them on the rows of a result set that might be represented as in Table 1 below.

 Type the following simple query into the Query pane:

-- MDX03-1:  Tutorial Query No. 1
SELECT
{([Store].[All Stores].[USA],[Product].[All Products])} ON COLUMNS,
{([Time].[1997])} ON ROWS
FROM Warehouse
WHERE ([Measures].[Units Shipped])

Commas:

The comma operator separates tuples forming a set, particularly when we find it difficult to practically define a set using a range of component members (more on ranges later).

 MDX uses the comma operator to separate tuples, which define a slice of data from a cube. (Tuples are composed of an ordered collection of one member from each of one or more dimensions)

Example:Let's say that we have been asked to generate several measures for the California warehouse group by management for purposes of evaluating aggregate performance of the warehouses over the 1997 / 1998 measurement periods. These seven measures, used as critical success factors for the California warehouse group manager, are as follows:

• Store Invoice
• Supply Time
• Warehouse Cost
• Warehouse Sales
• Units Shipped
• Units Ordered
• Warehouse Profit

SELECT
  {[Measures].[Store Invoice],[Measures].[Supply Time],
      [Measures].[Warehouse Cost],[Measures].[Warehouse Sales], 
      [Measures].[Units Shipped],[Measures].[Units Ordered], 
      [Measures].[Warehouse Profit]} ON COLUMNS,
  { [Time].[1997], [Time].[1998] } ON ROWS 
FROM Warehouse
WHERE
([Warehouse].[All Warehouses].[USA].[CA])

Colons:
The colon operator provides us a means of leveraging the natural order of members to create a set. Order is important, because the levels within a dimension house their respective members either in member name or member key order. We can take advantage of the order of the members, and define sets based upon ranges within the order, by using the colon operator.

Example:
We can illustrate the syntax within which a colon operator is used with the following example, excerpted from our practice exercise in the immediately preceding section. The set of members specified in the set:
{[Measures].[Store Invoice],[Measures].[Supply Time],  [Measures].[Warehouse Cost],
[Measures].[Warehouse Sales],[Measures].[Units Shipped],
 [Measures].[Units Ordered],[Measures].[Warehouse Profit]}
can be retrieved with the following syntax:
{ [Measures].[Store Invoice]: [Measures].[Warehouse Profit]}
provided that the range specified by the colon takes into account the natural order of the members. Let's verify this point by putting it into action in the following exercise.