Creating Basic MDX Queries

You can create MDX queries to replicate the reports you create by using a browser such as the Office PivotTable list control.

Create a Minimal MDX Query

Start by creating the simplest possible query from the Sales cube.

1.	Replace the contents of the query pane with SELECT FROM Sales. This is the simplest possible MDX query. You can now run this query.
2.	Click the Run Query button on the toolbar. (If you don’t see the results pane, click the Split View button to the right of the Run Query button; the Split View button displays the results pane below the metadata pane.) A single number appears in the report. The number in the report—35,696.00—is the total Sales Dollars for the entire cube. Because the query does not specify a member from any dimension, the query uses the default member from each dimension. [View full size image] You can also press F5 to run a query. To add a comment to an MDX statement, you simply enter two adjacent slash characters (//), and MDX will ignore everything from that point to the end of the line.
3.	Move the insertion point before the word SELECT. Type //Simple Query, and press Enter. Then click the Run Query button to run the query again. You don’t see any difference—or an error message.
4.	Click the Save Query File button on the toolbar, type Queries as the name of the file, and click Save. You can add multiple queries to this single file, but you can’t save a file until you’ve entered something in the query pane.

This simple query retrieves a single value from the cube. You can create more interesting reports by adding sets of members to the column or row axis.

Add a Set to the Column Axis

To display more than one cell in the result grid, you must put a set of members on an axis. Before creating the new query, add a new query to the query file so that you can keep both queries intact.

1.	Select the contents of the query pane, and press Ctrl+C to copy it. Click the New Query button, and press Ctrl+V. Change the comment to //Set on Axis. To display more than one member on an axis, you use a set of members, precisely as in the browser. MDX has a number of functions that return sets. To the right of the member list, in the metadata pane, there’s a tree labeled Syntax Examples. This list is the same as the Functions tree in the Calculated Member Builder. To get a set of members that you can put on an axis, select a function from the Set group.
2.	Click immediately after the word SELECT, and type a space. In the Syntax Examples list, expand the Set group and double-click the Members - Level function. Then click after the Members function, and type a space and ON COLUMNS. [View full size image]
3.	Click the word «Level». In the Metadata tree, double-click the Category level node in the Product dimension. The level name appears in the query.
4.	Click the Run Query button to see the results of the query. The labels from the Category level appear as column headings in the report. The client application, however, can choose to put the “column” axis on either the columns or the rows. [View full size image]
5.	Click the Pivot Results button on the toolbar. The Category labels now appear as row headings, even though the query still says ON COLUMNS.
6.	Click the Pivot Results button again to return the labels to the column headings, and save the query file.

The tokens in the function templates help you know what kind of item to add to a specific location of the query. You must replace the «Level» token with the name of a level. In various contexts, you might also see tokens «Member», «Set», «Tuple», «Dimension», and «Hierarchy». «Set» and «Tuple» are MDX constructs that do not exist in the OLAP database, so you typically replace them with functions from the appropriate groups. «Cube», «Dimension», «Hierarchy», and «Level» all are OLAP objects, so you typically replace these tokens with items from the Metadata tree. A «Member» is the place where MDX and the cube meet. You can replace the «Member» token either with a constant member from the Metadata tree or with a function from the Member group.

Add a Set to a Second Axis

You can also add a set to the row axis. In MDX, if you create a query that has only one axis, it must be the column axis. If you create two axes, one must be the column axis and one must be the row axis, although it doesn’t matter in which order they appear within the query.

1.	Copy the current contents of the query pane, click the New Query button to add a new query to the current file, and paste the previous query. Change the comment to //Two axes.
2.	Click after the word COLUMNS, and type a comma and a space. You can now add another set.
3.	In the Syntax Examples list, look in the Set group and double-click the Children function. Then click after the Children function, and type ON ROWS. [View full size image] The token that precedes the Children function name is «Member». A member has children.
4.	Select the «Member» token, and in the Metadata tree, find and double-click the 1998 member. The formatted statement will look like this: //Two axes SELECT [Product].[Category].Members ON COLUMNS, [Time].[Calendar].[All Time].[1998].Children ON ROWS FROM Sales This function will now add a set of all the quarters of the year 1998 to the row axis.
5.	Click the Run Query button to run the query. On the column axis, the Members function displays only the members from the Category level. There are actually three versions of the Members function—one that returns the members of a level, one that returns the members from a dimension, and one that returns the members from a hierarchy. You can change the query to retrieve all the members of the Product dimension.
6.	Delete the Category level name and the period following it, but leave the dimension name. The function will now return all the members of the Product dimension.
7.	Click the Run Query button to see the results of the query. Click the Pivot Results button to move the products to the row axis.
8.	Save the query file, and click the Pivot Results button to return the orientation to normal. Both axes contain sets. Even though the products are not indented, all the members of the Product hierarchy are in the set.

The Syntax Examples list shows three functions named members—one for levels, one for hierarchies, and one for dimensions. When a dimension has a dual hierarchy—as with the Time.Fiscal dimension—you must use the hierarchy version, not the dimension version, even if only one of the hierarchies exists in the current cube (as in the Basic Sales virtual cube created in Chapter 4, “Advanced Dimensions and Cubes”).

Create a Set by Using Explicit Member Names

The only thing you can put on an axis is a set. A set function is one way to get a set to put on the axis. But sometimes, particularly with the Measures dimension, you want to be able to pick one or more individual members to put on an axis. You must be able to put the members into a set. To include members in a set, you must enclose the set in braces ({}). The braces aren’t necessary when you use set functions but are necessary when entering specific member names.

The terminology of an MDX query centers on sets, tuples, and members. A set includes one or more tuples, and a tuple includes one or more members. Sets appear on the axes. Tuples appear either in a set (on a column or row axis), or in the WHERE clause to create the filter axis. Aside from the cube name in the FROM clause, a member is the only object from a cube that appears directly in an MDX query.

Put Two Dimensions on One Axis

In “Use a PivotTable list to understand MDX terms” earlier in this chapter, you saw how the Office PivotTable list can put two dimensions onto the same axis, creating a set where each position in the set includes a tuple with more than one member. In an MDX query, to combine two sets from two dimensions into a single set that you can put onto a single axis, use the CrossJoin function.

1.	Click the New Query button. Type Select Country.Members on Columns, Category.Members On Rows From Sales. Click the Run Query button. This query puts the members of the country level (of the State dimension) on one axis, and the members of the category level (of the Product dimension) on the second axis. Each axis contains a set, and each set has a single member in each tuple. You can use the CrossJoin function to combine the two sets into one set, with two members in each tuple.
2.	Change the query definition to Select CrossJoin( Country.Members, Category.Members ) On Columns From Sales. Click Run Query to see the results. [View full size image] The column axis contains a set with nine positions. Each position is a tuple that includes one member from each of two dimensions. The nine positions correspond to all the possible combinations of the three categories and the three countries.
3.	After the Columns keyword, type a comma, and press Enter. Then type CrossJoin( [Calendar Year].Members, [Measures].Members) ON ROWS, and click Run Query to run the query. [View full size image] This query contains four dimensions from the Sales cube—two on each axis. Remember that you can put a dimension onto only one axis in a single query.
4.	Add a comment, and save the query file.

The CrossJoin function is a powerful way to create reports that compare multiple dimensions. Because the function is so powerful, it’s easy to create sets that are extremely large, particularly when combined with the Members function of multiple dimensions.