Creating Simple MDX Expressions

MDX is a pseudoacronym for multidimensional expressions. MDX was created expressly for use with multidimensional OLAP data sources. MDX is used in two different ways within Microsoft SQL Server 2000 Analysis Services. First, it is a query language—the tool for retrieving reports from an OLAP cube. In other words, MDX is the tool used by client applications (such as the Microsoft Excel 2000 PivotTable report) to retrieve values. Second, MDX is an expression language—the tool used to calculate single values. For example, MDX is what you use to create calculated members in a cube or to create a dynamic default member. In this chapter, you’ll learn how to create MDX expressions based on single values. In Chapter 7, “MDX Sets,” you’ll learn how to create MDX queries and also how to create expressions based on multiple values. In Chapter 8, “Storage Optimization,” you’ll learn how to apply MDX expressions in a variety of contexts within a cube, including calculating default members, calculating custom rollup formulas, and creating custom actions. If you understand MDX, you can create sophisticated expressions that put the A (for analytical) into OLAP.

MDX is not exclusive to Analysis Services. It’s part of a specification called OLE DB for OLAP, which Microsoft has sponsored to industry standards boards as a tool for querying a multidimensional data source. MDX is a standardized language that will soon be supported by several OLAP providers, just as structured query language (SQL) is a standardized language that is supported by many relational database providers. Of course, just as relational database providers make modifications to the SQL standard, so OLAP providers will make minor customizations to the MDX standard. In fact, the MDX implemented in Analysis Services does vary somewhat from the OLE DB for OLAP specification. This book will refer only to the Analysis Services flavor of MDX.

Create a Calculated Member Using Constant Values

One important use of MDX expressions is in creating calculated members. Calculated members add the calculating ability of a spreadsheet to an OLAP database. In a spreadsheet, each cell gets its own value. That value can be a constant that you type into a cell or it can be calculated as a formula. To understand how calculated members work, start by creating calculated members that use constant values.

1.	In the Chapter 6 database, expand the Cubes folder, right-click the Sales cube, and click Edit. Click the Data tab to activate the Preview pane, and drag the Product dimension to the rows area, replacing the Employee dimension. [View full size image]
2.	Click the Insert Calculated Member toolbar button. In the Calculated Member Builder dialog box, type Sample as the name of the new member. In the Value Expression box, type 500. [View full size image]
3.	Click OK to create the member. [View full size image] A new column labeled Sample appears, filled with the value 500. If you think of the grid as a spreadsheet, you can think of a calculated member as a value or formula that automatically fills all the cells of the column. You can change the expression for a calculated member simply by changing the Value property in the Properties pane.
4.	In the cube tree, select Sample in the Calculated Members folder. Select the Value property, type “Hello”, and press Enter. [View full size image] The values in the Sample column change to the word Hello. You can use a text string as a constant value in a calculated member as long as you enclose the string in double quotation marks.
5.	Select the Value property of the Sample member, type Null, and press Enter. [View full size image] The cells in the Sample column appear empty. You can’t leave the Value property empty; to create an empty cell, use the keyword Null.
6.	Select the Value property of the Sample member, type 50+17, and press Enter. [View full size image] The number 67—the result of the expression—appears in all the cells of the Sample column. Again, think of the grid as a spreadsheet where the value or formula you type is automatically copied into all the relevant cells. It’s as if there were four different copies of the formula, each one calculating a value. In an MDX expression, you can use any standard arithmetic operator: plus (+) for addition, minus (-) for subtraction, asterisk (*) for multiplication, slash (/) for division, and caret (^) for exponentiation.
7.	Select the Value property of the Sample member, type “Hello” + “,” + “World”, and press Enter. [View full size image] The string Hello, World appears. An MDX expression can combine text strings. Unlike an Excel formula, in which you use an ampersand (&) to combine text strings, in an MDX expression, you must use a plus sign (+). Also unlike an Excel formula, an MDX expression does not automatically convert a number into a text string, so you cannot use an expression such as “The value is” + 45. (Later in this chapter, you’ll learn how to combine text with a number.) See “Use an external function to convert a string to a number” later in this chapter to learn how to combine text with a number.

Display the Name of the Current Member

An MDX expression can return either a number or a text string. You can use MDX expressions to display member names. Doing so can help you see how MDX expressions and dimension members interact.

1.	Select the Value property of the Sample member, and click the ellipsis (...) button. Clear the contents of the Value Expression box. MDX includes a large number of functions. MDX functions are all listed in the Functions tree of the Calculated Member Builder, grouped based on what the function returns. There is a Name function that returns a name. A name is a text string, so the Name function appears in the String group.
2.	In the Functions tree, expand String and double-click the Name - Dimension function. [View full size image] The function appears in the Value Expression box, preceded by the token «Dimension». When you insert a function from the Functions tree, it shows you the syntax for the function, using placeholder tokens enclosed in double pointed brackets. Tokens are easy to replace: simply click anywhere within the token to select it, and then double-click an appropriate item from the Data or Functions tree.
3.	Click the «Dimension» token, and then double-click the Product dimension in the Data tree to change the expression to [Product].Name. Then click OK. [View full size image] The Sample column shows the word Product in all the rows. The expression is displaying the name of the dimension, which doesn’t change from member to member. To display the name of a specific member, you must use a different version of the Name function.
4.	Select the Value property, and click the ellipsis button. Clear the Value Expression box, expand String in the Functions tree, double-click Name - Member, and click the «Member» token. [View full size image] You need a function that will return a member, so look in the Member group of the Functions tree.
5.	Expand the Member group, and double-click the CurrentMember function. [View full size image]
6.	Click the «Dimension» token that precedes the function. Double-click Product in the Data tree to change the expression to [Product].CurrentMember.Name. Then click OK. [View full size image] The Sample column displays the name of each member.
7.	Drag the State dimension down to the rows axis, replacing the Product dimension (that is, the Category heading). [View full size image] The value in the Sample column changes to All Product for all the rows. That’s because the All Product member is selected in the filter list above the grid. The value in the filter list is the current member for that dimension.
8.	In the Product filter list, select Bread. [View full size image] The value in the Sample column changes to Bread because Bread is now the current member of the Product dimension. Each cell in the grid has a current member for each dimension. If a dimension is represented on the row or column axis, the current member is the member that appears on the current row or column. If a dimension appears in the filter area, the current member is the member that appears in the filter box.
9.	Drag the Product dimension back to the row axis, replacing the State dimension, and double-click the Category heading so that both Category and Subcategory levels are visible. Scroll as needed to see the Sample member.

The current member of a dimension can be at any level of the hierarchy.

Display the Ancestor of a Current Member

CurrentMember is an MDX function. It is a function that returns a member. Once you have a member, you can display that member’s name. There are other functions that return a member, often using the current member of a dimension as a starting point. For example, you can find the ancestor of the current member at any level of the hierarchy.

1.	Click the Value property of the Sample member, click the ellipsis button to display the Calculated Member Builder dialog box, and clear the contents of the Value Expression box.
2.	In the Functions tree, expand String and double-click the Name - Member function.
3.	Click the «Member» token, and expand Member in the Functions tree. [View full size image] Like CurrentMember, Ancestor is a function in the Member group. There are two versions of the Ancestor function.
4.	Double-click Ancestor - Level in the Functions tree, and look at the description at the bottom of the dialog box. This function returns the ancestor of a member at a given level. The CurrentMember function returns a member, so you can use it to replace the «Member» token.
5.	Click the «Member» token. Then double-click CurrentMember in the Functions tree. Click the «Dimension» token. Then double-click Product in the Data tree.
6.	Click the «Level» token. Then expand the Product dimension, and double-click the Category level. Click OK to accept the new expression. [View full size image] Note Putting the insertion point after one item in a pair of punctuation marks (such as parentheses, brackets, or quotation marks) turns both items bold. Omitting one of a pair of punctuation marks turns the remaining item red. These signals help you properly match punctuation that must come in pairs.
7.	Double-click the Subcategory level, and browse the Sample column to see how it always displays the appropriate category ancestor of the current product. [View full size image] The parent of a member is just a special kind of ancestor—the ancestor that is only one level away. A second variation of the Ancestor function allows you to specify the distance from the original member to the desired member. For a parent, the distance is 1 level.
8.	Select the Value property of the Sample member, and change the expression to Ancestor([Product].CurrentMember,1).Name. Then press Enter to display the name of the parent for each member. [View full size image]

By using the Ancestor function—coupled with the CurrentMember function—you can display the ancestor of the current member, either at a relative position in the hierarchy or at a specific level of the hierarchy.

Test a Member Name against a String

MDX allows you to make comparison tests in an expression. For example, you can test whether a product belongs in a specific category by testing the name of the product’s ancestor.

1.	Select the Value property of the Sample calculated member, and click the ellipsis button. Change the value expression to Ancestor([Product].CurrentMember, [Product].[Category]).Name = “Bread”.
2.	Click OK to accept the revised expression. In the browser, display the Product Category and Subcategory levels and look at the value in the Sample column. [View full size image] The value of True as 1 and False as 0 is similar to the usage in Excel but differs from Microsoft Visual Basic in which True is -1 and False is 0. The Sample column shows the value 1 for all products that are within the Bread category and 0 for all products that are not in the Bread category. The value 1 is equivalent to True, and 0 is equivalent to False. Note MDX uses standard comparison operators: = (equals), <> (not equal), > (greater than), < (less than), >= (greater than or equal), and <= (less than or equal). You might want to display something other than 1 or 0 in the cells. Suppose, for example, that you wanted to flag all the products that would be OK to bring to a bread-tasting social. The IIF function allows you to return different values depending on the result of a conditional test. The MDX IIF function is similar to the IF function in Excel or the IIF function in Visual Basic.
3.	Click the Value property ellipsis button to redisplay the Calculated Member Builder dialog box. In the Functions tree, expand String and select the IIF - String function. Look at the description of the function at the bottom of the dialog box. [View full size image] The first argument of the IIF function is a conditional test—an expression that returns True or False. The second argument is the value that will be returned if the conditional test is True; it can be either a string or a number. The third argument is the value that will be returned if the conditional test returns False; it must be the same type of value as that of the second argument. If the second argument is a string, the third argument must also be a string. Likewise, if the second argument is a number, the third argument must also be a number. This is different from the IF function in Excel or the IIF function in Microsoft Visual Basic for Applications (VBA), where the arguments can have different data types.
4.	Change the contents of the Value Expression box to IIF(Ancestor ([Product].CurrentMember, [Product].Category ).Name =“Bread”, “Bring”, “Don’t Bring”). (Specifically, enter IIF and an opening parenthesis at the beginning of the expression. Type a comma, “Bring”, another comma, “Don’t Bring”, and a closing parenthesis at the end of the expression.)
5.	Click OK to accept the definition, and browse the values in the Sample column. [View full size image] A conditional test, such as the one used in the IIF function, can be composed of more than one sub test. For example, if your bread party changes to a bread and cheese party, you could expand the conditional test.
6.	Select the Value property, and click the ellipsis button. Change the expression to IIF(Ancestor([Product].CurrentMember, [Product]. Category ).Name = “Bread” OR Ancestor([Product].CurrentMember, [Product].Subcategory ).Name = “Cheese”, “Bring”, “Don’t Bring” ).
7.	Click OK, and browse the results in the Sample column [View full size image] Note In addition to OR (which returns True if either of two conditional tests returns True), MDX also allows you to combine two conditional tests using AND (which returns True only if both values are True) and XOR (which returns True if one and only one value is True). You can also precede a conditional test with NOT (which reverses the True and False results of the test).

Display a Member Property for a Member

In addition to names, members can have member properties as well. For example, in the Product Dimension, the Subcategory level has a Category Manager member property assigned. You can display the name of that manager.

1.	Select the Value property of the Sample calculated member, and click the ellipsis button. Change the expression to Ancestor( [Product].CurrentMember, [Product].[SubCategory] ).Name. This expression would display the name of the current product’s subcategory.
2.	Delete the Name function from the end of the expression, and add Properties(“Category Manager”). [View full size image] The name of the member property must be enclosed in double quotation marks. If the member property name does not exist, the expression will return an error.
3.	Click OK, and then expand the levels to show the Product Name level and see the manager change with the subcategory. [View full size image]

Use an External Function to Convert a String to a Number

A member property is always returned as a string, even if the member property stores a number. If you want to use the member property as a number in an expression, you will have to use an external function to convert it.

For example, the Product Name level of the Product dimension includes a Price member property that contains the list price for each product. If you try to use the Price member property in a numeric expression, you’ll get a syntax error. To use the member property in an expression, you must convert it to a number. MDX does not have any functions that will convert a string to a number, but MDX does allow you to access functions from other sources. In fact, MDX automatically includes functions from VBA and from Excel. VBA includes a function named CDbl (for “convert to double”) that will convert a string into a number.

As a simple example, try to display what the list price would be after a 10 percent across-the-board price hike. The first step is to display the Price member property.

1.	Replace the Value property of the Sample member with [Product]. CurrentMember.Properties(“Price”), and press Enter. [View full size image] The prices appear for the individual products, but an error value occurs for each higher-level member. You can use an IIF function to display a value only for the lowest level.
2.	Change the Value property of the Sample member to IIF(Product. CurrentMember.Level.Name = “Product Name”,Product. CurrentMember.Properties(“Price”),“”), and press Enter. [View full size image] You might want to open the Calculated Member Builder dialog box to edit the expression. The error values are now gone. With the IIF function, if the second argument is a string, the third argument must also be a string. Even though the Price member property is really a number, member properties are always returned as a string. The third argument must, therefore, be an empty string. To multiply the Price by a value, you must convert it to a number.
3.	Change the Value property of the Sample member to IIF(Product. CurrentMember.Level.Name = “Product Name”,CDbl(Product. CurrentMember.Properties(“Price”)),Null), and press Enter. To convert a number to a string, use the CStr function or the Format function from VBA. The values in the browser look the same, but now the prices are numbers, not strings. The revised expression required two changes: adding the CDbl function and its parentheses around the member property value, and replacing the empty string with Null. The CDbl function is not an MDX function. It does not appear in the Number group of the Functions tree—in fact, it doesn’t appear anywhere in the Functions tree. The function is a VBA function. You don’t have to do anything special to include it in an MDX expression. All that remains is to multiply the value by 1.1 to get a 10 percent increase in price.
4.	Append * 1.1 to the end of the Value expression for the Sample member. The resulting expression will be IIF(Product.CurrentMember.Level.Name =“Product Name”,CDbl(Product.CurrentMember.Properties(“Price”)),Null)*1.1. Then press Enter to see the updated values. [View full size image]

If you attempt to use a member property value in an arithmetic expression, the result is an error unless you first convert the value to a number.