

Up to this point in the chapter, we have looked at the ins and outs of the MDX SELECT statement. This statement is extremely important to cube analysis. For the remainder of this chapter, we touch on some additional MDX that is useful in both the MDX SELECT statement and in MDX syntax scripts.
First, we take a look at the operators available in MDX.
Item  Description 

/*…*/  Multiline comment 
  Singleline comment 
//  Singleline comment 
If you save your MDX queries for use by others or for your own reuse later, documenting what the query is for and exactly what it returns is important. With some of the more intricate MDX statements, it is not always easy to decipher the statement's purpose. A comment or two can be a lifesaver in these situations.
Multiline comments can span several lines and are not terminated until a */ is encountered. Singleline comments are terminated at the end of the line. Any of the comment types can begin at the beginning or in the middle of a line. Comments are completely ignored during MDX processing.
Here is an example of all three types of comments.
/* This is an MDX SELECT statement from the "Delivering Business Intelligence with SQL Server 2005" book. */ SELECT [Product Type].Members ON COLUMNS,  the COLUMNS dimension [Year].Members ON ROWS // the ROWS dimension FROM [Max Min Manufacturing DM] WHERE [Measures].[Total Products] /* The slicer dimension */
item  Description 

+  Positive 
+  Addition 
  Negative 
  Subtraction 
*  Multiplication 
/  Division 
The numeric operators function as we would expect.
Item  Description 

+  Concatenation 
String concatenation is performed with the plus (+) sign as in TSQL.
Item  Description 

<  Less Than 
<=  Less Than or Equal To 
<>  Not Equal To 
=  Equal 
>  Greater Than 
>=  Greater Than or Equal To 
AND  Logical and 
IS  Tuple Equality 
NOT  Logical Not 
OR  Logical Or 
XOR  Logical Exclusive Or 
The logical operators perform as expected.
Item  Description 

  Except 
*  Crossjoin 
+  Union 
The except () and union (+) operators only work with sets from the same dimension. The crossjoin (*) operator requires two sets from different dimensions. The Range operator was covered in the section "Range Operator" in Chapter 10. The crossjoin operator functions in the same manner as the CROSSJOIN function covered in the section "CROSSJOIN Function" in this chapter.
If Set 1 is
{[Bear and Cub], [Bear with Hive], [Bull Moose]}
and Set 2 is
{[Bull Moose], [Eagle Pair], [Howling Wolf]
then
[Set 1]  [Set 2]
is
{[Bear and Cub], [Bear with Hive]}
In other words, it is the members in Set 1 that are not in Set 2.
[Set 1] + [Set 2]
is
{[Bear and Cub], [Bear with Hive], [Bull Moose], [Eagle Pair], [Howling Wolf]}
Items found in both sets appear only once in the resulting concatenated set.
We look at some additional MDX functions that may be of use.
These functions return a dimension, a hierarchy, or a level.
Dimension Returns the dimension to which the given object belongs.
[Bear and Cub].Dimension
returns
[Dim Product]
Hierarchy Returns the hierarchy to which the given object belongs.
[Bear and Cub].Hierarchy
returns
[Product Hierarchy]
Level Returns the level to which the given object belongs.
[Bear and Cub].Level
returns
[Product]
These functions return either true or false.
IsAncestor(member1, member2) Returns true if member1 is an ancestor of member2.
IsAncestor([Woodland Creatures], [Bear and Cub])
returns true.
IsEmpty(MDXexpression) Returns true if MDXexpression results in an empty cell.
IsEmpty(([Warriors Of Yore],[Maximum MiniaturesMumbai]))
returns true because the cell represented by this tuple has no data.
IsGeneration(member1, numeric expression) Returns true if member1 is a member of the generation represented by numeric expression. The lowest level or leaf level of a hierarchy is level 0.
IsGeneration([Black Bears],1)
returns true.
IsLeaf(member1) Returns true if member1 is at the lowest level of its hierarchy.
IsLeaf([Bear and Cub])
returns true.
IsSibling(member1, member2) Returns true if member1 is a sibling of member2.
IsSibling([Bear and Cub], [Bear with Hive])
returns true.
IIF(logical expression, expression1, expression2) The IIF function may or may not return true or false. The reason the IIF function is here is because it requires a logical expression as its first parameter. Therefore, the IIF function is a likely place to find one of these other logical expressions.
If the logical expression evaluates to true, expression1, the second parameter, is evaluated and the result returned by the IIF function. If the logical expression evaluates to false, expression2, the third parameter, is evaluated and the result returned. For example:
IIF(IsLeaf([Product Hierarchy].CurrentMember), [Product Hierarchy].CurrentMember, [Product Hierarchy].CurrentMember.FirstChild)
In this example, if the current member of the product hierarchy is a leaf member, the current member of the product hierarchy is returned. This is the content of the second parameter passed to the IIF function. If the current member of the product hierarchy is not a leaf member, the first child of the current member of the product hierarchy is returned. This is the content of the third parameter passed to the IIF function.
These functions return a member.
closingPeriod(level, member) Returns the member at the level of level that represents the last item in the period occupied by member.
ClosingPeriod([Month], [2005Q3])
returns [200509], the last month member in Q3, 2005.
Item(n) Returns the nth member of the tuple.
([2004], [Bear and Cub], [Clay]).Item(1)
returns [Bear and Cub].
OpeningPeriod(level, member) Returns the member at the level of level that represents the first item in the period occupied by member.
OpeningPeriod([Month], [2005Q3])
returns [200507], the first month member in Q3, 2005.
When we used the SUM function to aggregate values to calculate the average production per month, we forced an aggregation on the rollup. All of the items were added together to create the aggregate. In some cases, this may not have been appropriate.
If we were working with sales figures, we could have inadvertently added in return amounts that should have been subtracted. If we were working with financial data, we could have added in credits that should have been subtracted. (In these days of SarbanesOxley that can get a company in real trouble, real fast!)
Instead of forcing an aggregation by using a function like the SUM function, we can use the AGGREGATE function. The AGGREGATE function works just like the SUM function with one major exception. The AGGREGATE function uses the default aggregation operator for a measure, rather than forcing one on it. This helps insure the date resulting from an aggregation is correct.
MDX provides a number of numerical functions to assist in the analysis of OLAP cube data. We do not have space here to treat each in detail. We'll provide a list of these functions here to make you aware of their existence. Then, if you or one of your users has need of a particular type of data or statistical analysis, you can track down the details for the function or functions that fit the bill.
Function  Description 

Covariance  Returns the population covariance of two series evaluated over a set, using the biased population formula. 
CovarianceN  Returns the sample covariance of two series evaluated over a set, using the unbiased population formula. 
LinRegIntercept  Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b. 
LinRegPoint  Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b. 
LinRegR2  Calculates the linear regression of a set and returns R2 (the coefficient of determination). 
LinRegSlope  Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b. 
LinRegVariance  Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b. 
LookupCube  Returns the value of an MDX expression evaluated over another specified cube in the same database. 
Max  Returns the maximum value of a numeric expression evaluated over a set. 
Median  Returns the median value of a numeric expression evaluated over a set. 
Min  Returns the minimum value of a numeric expression evaluated over a set. 
Stddev, Stdev  Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula. 
StddevP, StdevP  Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula. 
Var, Variance  Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula. 
VarP, VarianceP  Returns the population variance of a numeric expression evaluated over a set, using the biased population formula. 

