Additional MDX Syntax


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.

Operators

First, we take a look at the operators available in MDX.

Comment

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 */ 

Numeric

item

Description

+

Positive

+

Addition

-

Negative

-

Subtraction

*

Multiplication

/

Division

The numeric operators function as we would expect.

String

Item

Description

+

Concatenation

String concatenation is performed with the plus (+) sign as in T-SQL.

Logical

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.

Set

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.

Functions

We look at some additional MDX functions that may be of use.

Dimensional

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] 

Logical

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.

Member

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.

The AGGREGATE Function

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.

Data Analysis

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.




Delivering Business Intelligence with Microsoft SQL Server 2005
Delivering Business Intelligence with Microsoft SQL Server 2005: Utilize Microsofts Data Warehousing, Mining & Reporting Tools to Provide Critical Intelligence to A
ISBN: 0072260904
EAN: 2147483647
Year: 2007
Pages: 112
Authors: Brian Larson

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net