MDX Essentials : MDX Operators: The Basics - Page 2

Curled Braces

Discussion:

While the syntax format of the MDX SELECT statement resembles SQL syntax in numerous ways, one of the more pronounced differences is 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. We will see in later lessons that there are other ways to return sets; suffice it, for now, to say that when an expression uses MDX functions and operators, there is no requirement for the expression concerned to be enclosed within braces, as long as there is not a simultaneous effort to combine the set involved with other tuples. To make what is a new subject to many of us a little easier, I will strive to consistently use curled braces with any set expressions we encounter throughout the series.

Syntax

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.

Practice

We can see, through a quick exercise, some of the finer points in the use of curly braces. Let's fire up the Sample Application that we introduced in our last tutorial, and jump right in.

1.             Start the MDX Sample Application.

2.             Clear the top area (the Query pane) of any queries or remnants that might appear.

3.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.             Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown below, complete with the information from the Warehouse cube displaying in the Metadata tree (in the left section of the Metadata pane, between the Query pane at the top of the application window, and the Results pane, at the bottom.).

We will begin with a simple illustration that involves our current topic: Let's say that an information consumer from management of the Warehouse Division wants to know the total number of all product items shipped for all USA stores in 1997.

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

6.             Click Query on the top menu, and then select Run.

We see the results below, which appear in the Query pane as soon as Analysis Services fills the cells that it determines to be specified by the query.

Note: For an explanation of the comments line, together with a general discussion regarding attributes of the basic MDX query structure, see the first article of the series.

The query delivers the results that we requested.

7.             Save the query by selecting File -> Save As, and call the file MDX03-1.

Now, let's make the following alteration to demonstrate the important nature of the curly braces:

8.             Change the following lines in the query:

-- MDX03-1:  Tutorial Query No. 1

and

{([Store].[All Stores].[USA],[Product].[All Products])} ON COLUMNS,

to the following (simply changing the comment line and removing the braces in the first line under the SELECT clause):

-- MDX03-2:  Tutorial Query No.21

and

([Store].[All Stores].[USA],[Product].[All Products]) ON COLUMNS,

Leave the rest of the query in its original form. The Query pane should resemble that shown in Illustration 3 below:

9.             Click Query on the top menu, and then select Run.

An error dialog appears, indicating that the cellset cannot be opened, as shown in the following illustration:

The reason for the error is that, when we run the query after removing the braces, we are presenting an invalid request. The tuple in the modified line needs to be enclosed in braces to be properly recognized.

10.         If it is desirable, save the query as MDX03-2.