|
|
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 |
-- | Single-line comment |
// | Single-line 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. Single-line 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 T-SQL.
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 Miniatures-Mumbai]))
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 Sarbanes-Oxley 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. |
|
|