MDX Expressions

MDX expressions are partial MDX statements that evaluate to a value. They are typically used in calculations or in defining values for objects such as default member and default measure, or used for defining security expressions to allow or deny access. MDX expressions typically take a member, a tuple, or a set as a parameter and return a value. If the result of the MDX expression evaluation is no value, a Null value is returned. Following are some examples of MDX expressions.

Example 1

     Customer.[Customer Geography].DEFAULTMEMBER 

This example returns the default member specified for the hierarchy Customer Geography of the Customer dimension.

Example 2

     (Customer.[Customer Geography].CURRENTMEMBER, Measures.[Sales Amount]) -     (Customer.[Customer Geography].Australia, Measures.[Sales Amount) 

This MDX expression is used to compare the sales between customers of different countries with sales of customers in Australia.

Such an expression is typically used in a calculation called the calculated measure. Complex MDX expressions can include various operators in the MDX language along with the combination of the functions available in MDX. One such example is shown in Example 3.

Example 3

     COUNT (INTERSECT ( DESCENDANTS ( IIF ( HIERARCHIZE (EXISTS[Employee].[Employee].MEMBERS,     STRTOMEMBER ("[Employee].[login].[login].&["+USERNAME"]")),     POST).ITEM (0).ITEM (0).PARENT.DATAMEMBER is     HIERARCHIZE (EXISTS ([Employee].[Employee].MEMBERS,     STRTOMEMBER ("[Employee].[login].[login].&["+USERNAME+"]")), POST).ITEM (0).ITEM (0),     HIERARCHIZE (EXISTS ([Employee].[Employee].MEMBERS,     STRTOMEMBER ("[Employee].[login].[login].&["+username+"]")),     POST).ITEM (0).ITEM (0).PARENT,     HIERARCHIZE (EXISTS ([Employee].[Employee].MEMBERS,     STRTOMEMBER ("[Employee].[login].[login].&["+USERNAME+"]")), POST).ITEM (0).ITEM (0))     ).ITEM (0) , Employee.Employee.CURRENTMEMBER)) > 0 

The above example is a cell security MDX expression to allow employees to see Sales information made by them or by the employees reporting to them and not to other employees. This MDX expression uses several MDX functions (you learn some of these in the next section). You can see that this is not a simple MDX expression. The above MDX expression returns a value "True" or "False" based on the employee that is logged in, and Analysis Services provides appropriate cells to be accessed by the employee based on the evaluation. This example is analyzed in more detail in Chapter 18.

MDX has progressed extensively since its birth and you can pretty quickly end up with a complex MDX query or MDX expression like the one shown above in Example 3. There can be multiple people working on implementing a solution and hence it is better to have some kind of documentation for the queries or expressions. Similar to programming languages where you can embed comments, MDX supports commenting within queries and MDX expressions. At this time there are three different ways to comment your MDX. They are:

     // (two forward slashes) comment goes here     -- (two hyphens) comment goes here     /* comment goes here */ (slash-asterisk pairs) 

We highly recommend that you add comments to your MDX expressions and queries so that you can look back at a later point in time and interpret or understand what you were implementing with a specific MDX expression or query.