Performance Improvements for MDX in SQL Server 2008 Analysis Services

For this version of Analysis Services, special emphasis was taken to improve the performance in the execution of Multidimensional Expressions (MDX) calculations. Several important changes have made to the engine architecture to achieve these performance gains. However, to take advantage of these performance improvements, it is necessary to optimize your MDX code.

This document will help you understand where issues can occur in your existing MDX code that will prevent you from experiencing the performance improvements, and provide advice on how to avoid these issues in your new MDX coding. This document also includes a list of the functions that benefit from the performance improvements.

Reviewing Your Code to Obtain Maximum Performance Gains in MDX

When reviewing your code, try to avoid the following set of scenarios or coding situations as they might prevent your MDX statements from achieving the performance gains expected in SQL Server 2008 Analysis Services (SSAS). However, if there is no practical way to change your code to avoid the listed situations, you can expect your MDX code to have the same level of performance as in SQL Server 2005 Analysis Services (SSAS).

Useful definitions

Space

The set of cells over which an expression is evaluated.

Arbitrary Shape

A space that cannot be expressed as the cross join of two or more sets. For example, the space {(Drink, USA), (Food, Canada)} represents an arbitrary shape because it is a subset of the cross join between {Drink, Food} * {USA, Canada} = {(Drink, USA), (Drink, Canada), (Food, USA), (Food, Canada)}.

Static expression

An expression is said to be static when it is invariant over the space on which is calculated.

For example, over the space of CrossJoin(Product.Members, Customer.Members) the following expressions are invariant.

• 1, a constant expression

• Product.Members.Count

Dynamic expression

An expression is said to be dynamic when it resolves into a different value for each cell over the space on which is calculated.

For example, over the space of CrossJoin(Product.Members, Customer.Members) the following expressions are dynamic.

• Sales, because Sales is a measure its value is different for each cell in the space.

Varying Attribute

A varying attribute drives the way the expression is evaluated and makes the expression dependant upon it. For example, the expression Customer.Geography.CurrentMember depends on the attributes in the geography hierarchy.

Usually, varying attributes reduce the space over which expressions are evaluated. Consider the following expression:

with member measures.x as Customers.Geography.currentmember.uniquename

Select Customers.Geography.City.members on 0,

Product.members on 1

From sales

Where measures.x

In this expression, Customers.Geography is a static expression. The currentmember function is a varying attribute because it introduces a dependency on the City attribute. Uniquename adds no varying attributes because it is bound to currentmember in a 1:1 relationship. Hence, uniquename will be evaluated only one time for each customer and not repeated for each Product. Therefore, the whole expression space was effectively reduced over the varying attribute.

Usage of Expressions in Non-Value properties of a cell

Any MDX expression that is used to assign the value of a non-value property of a cell will not benefit from the performance improvements. The performance will remain at the same level as SQL Server 2005 Analysis Services (SSAS).

Usage of Non-Listed Functions

The usage of any function not listed in this document in your MDX code will not benefit from the expected performance gain of this version of the product. See Functions with enhanced performance in this document.

Usage of Cell Security

Evaluating an MDX expression over a space that has defined cell security will prevent your code from gaining improved performance.

The relationship between cell security and performance is presented in the following table.

Cell Security	Expected Performance
None	Best
Read	Intermediate
Read Contingent	Lowest

See Using MDX Expressions to Set Cell Data Permissions and Granting Custom Access to Cell Data

Usage of Dynamic Dimensionality

The usage of dynamic dimensionality expressions in your MDX code will prevent your code from gaining improved performance. For example, expressions like Sum( IIF( Sales > 10000, h1.Members, h2.Members)) will not benefit because your code is changing the members to be summed up as the Sales expression is evaluated. Another example would be a scenario where you need to use either a member from the Calendar Year or a member from the Fiscal Year hierarchies that depend on an attribute that belongs to the current member of the Account attribute to make a comparison with the equivalent value of a parallel period. The MDX expression required for this scenario would be something similar to the following sample code.

ParallelPeriod(Iif( Account.CurrentMember.Properties("UsesFiscalCalendar")="Y", FiscalTime, CalendarTime).CurrentMember)

Again, the dimensions change dynamically as the current member of the Account dimension changes.

Usage of Dynamic Parameters

The usage of dynamic parameters in your MDX code will prevent your code from gaining improved performance. For example, an expression like KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) varies over the cells upon which it is being calculated, In contrast, the expression KpiGoal("Sales_" & Cstr(Year(Now))) is invariant.

Important

It might be the case that the expression KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) evaluates to the same value over the space that it is calculated in. However, that would not be enough for the engine to provide the expected performance gains.

Dynamic Member References

The usage of any dynamic member reference in your MDX code will prevent your code from gaining improved performance. For example, in the following expression

(IIF( e, mbr1, mbr2), Sales)

there is no way to know the resulting tuple until the IIF() expression is evaluated at execution time. However, in the following equivalent expression

IIF( e, (mbr1, Sales), (mbr1, Sales))

either of the resulting tuples are known before evaluating the expression e.

Plan Hints

Plan hints are an extension to the MDX language to indicate to the engine how to evaluate expressions. In this version of Analysis Services, plan hints are introduced over the IIF(,,) function only.

Plan hints can be indicated in the MDX expression or configured globally at the server configuration properties.

Plan Hints in IIF() functions

In IIF(,,), expressions hints are indicated using the following syntax:

IIF(<cond>, <expr>, <expr>) [HINT <hints>]

<expr> ::= <expr> [HINT <hints>]

<hints> ::= <hint> [<hints>]

<hint> ::= EAGER | STRICT | LAZY

• EAGER causes the expression to be evaluated over the entire IIF subspace.

• STRICT causes the expression to be evaluated only in the resulting subspace according to the results of the condition expression.

• LAZY causes the expression to be evaluated in a cell-by-cell mode.

• EAGER and STRICT are mutually exclusive in the hint; they can be used in the same IIF(,,) over different expressions.

A syntax example:

IIF([Measures].[Internet Sales Amount]=0

, {([Date].[Calendar Year].CURRENTMEMBER, [Customer].[Country].[All Customers])} HINT EAGER

, {{[Date].[Calendar Year].CURRENTMEMBER} * [Customer].[Country].[Country].MEMBERS} STRICT LAZY

)

Plan Hints in configuration properties

The following configuration properties are introduced to support Plan Hints, under OLAP\Query path:

Property Name	Acceptable Values	Explanation
IIFThenMode	0 | 1 | 2	0, No Hint (default) 1, EAGER 2, STRICT
IIFElseMode	0 | 1 | 2	0, No Hint (default) 1, EAGER 2, STRICT
LazyEnabled	0 | 1	0, Disabled (default) 1, Enabled

User Defined Stored Procedures (COM or .NET)

The usage of user-defined stored procedures in your MDX code will prevent your code from gaining improved performance.

Note

SQL Server 2008 Analysis Services (SSAS) provides stored procedures that are optimized for the performance improvements.

Usage of Named Sets or Set Alias in parameters

Any time that a named set or a set alias is used as the first parameter in the functions Sum, Min, Max, Avg, or Aggregate in your MDX code, your code will not benefit from the performance improvements.

For example, the following MDX expression counts how many members have more than one child.

Sum(h.members as S, Iif(S.Current.Children.Count > 1, 1, 0))

Because h.members is being aliased as S and later Current function value is taken from the aliased set, it prevents the expected improvement in performance from being obtained.

Another common example of this situation is illustrated in the following code.

WITH

SET [Core Products] AS '{[Product].[Category].[Bikes]}'

MEMBER [Measures].[Core Products Sales] AS SUM([Core Products], [Measures].[Internet Average Unit Price] * [Measures].[Internet Order Quantity])

Select [Measures].[Core Products Sales] on 0

From [Adventure Works]

The SUM function in the member definition does not obtain the expected performance improvement because it is based on a named set.

Usage of Late Bindings in Custom Rollup Expressions

Any time that a custom rollup expression references a calculated member, or any other MDX expression that is evaluated at execution time, the custom rollup expression prevents improved performance.

Usage of Forward References in Scripts

Any time that you create forward definition references, in separate statements, in your MDX code, your code will not benefit from the performance improvements. For example, in the following MDX script snippet a forward reference is created upon Y during X definition.

Create Member X as Y * 2;

…

Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);

To correct this situation, put Y definition before X definition, as in the following snippet.

Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);

Create Member X as Y * 2;

…

Functions with enhanced performance

Scalar Functions

The following list, of scalar functions, includes the functions over which you should expect to see improved performance. The first column in the list contains the scalar operators.

-	OR	KEY
*	XOR	LEVELS.COUNT
/	CALCULATIONPASSVALUE	MEMBERTOSTR
+	CASE	MEMBERVALUE
<	COALESCEEMPTY	NAME
<=	HIERARCHIES.COUNT	ORDINAL
<>	ID	PROPERTIES
=	IIF	UNIQUENAME
>	IS	USERNAME
>=	ISANCESTOR	VALIDMEASURE
unary minus	ISEMPTY	VALUE
NOT	ISLEAF
AND	ISSIBLING

Note

Any user-defined stored procedure, in either COM or managed code, will not see any improvement in performance when it is compared to SQL Server 2005 Analysis Services (SSAS). For more information, see User Defined Stored Procedures (COM or .NET) earlier in this document. Constant expressions, either literal or numeric, will benefit from the performance improvements.

Member Functions

The following list of member functions includes the functions over which you should expect to see improved performance.

.CurrentMember	.FirstSibling	.LastSibling
.DataMember	.Item	.Lead
.DefaultMember	.Lag	.Parent
.FirstChild	.LastChild	.UnknownMember

Ancestor	KPIStatus	NextMember
Ancestors	KPITrend	OpeningPeriod
Ascendants	KPIValue	ParallelPeriod
ClosingPeriod	KPIWeight	PrevMember
Cousin	LastPeriods	StrToMember(<String Expression>, CONSTRAINED)
KPIGoal	LinkMember

Note

StrToMember(<String Expression>, CONSTRAINED) obtains the best performance possible when <String Expression> is a static expression.

Set Functions

The following list of set functions includes the functions over which you should expect to see improved performance.

Aggregate	Max	Sum
Avg	Min

However, when you use any of the functions listed, the first parameter must be an expression that uses any combination of the following functions.

- (except operator)	.Children	MTD
(<set expression>,(<set expression>, …,(<set expression>) (cross join operator)	.Members	PeriodsToDate
* (cross join operator)	.Siblings	QTD
: (range operator)	AddCalculatedMembers	StrToSet(<String Expression>, CONSTRAINED)
+ (union operator)	Crossjoin(<set expression>,(<set expression>, …,(<set expression>)	Tail
	Descendants	Union
	Distinct	Unorder
	Except	WTD
	Hierarchize	YTD
	Intersect

Note

Static sets, including the empty set, will also benefit from the expected performance gains.

VBA Functions

The following list of VBA functions includes the functions over which you can expect to see improved performance.

Abs	CLng	Len
CDate	CStr	Now
CDbl	Int	Right
CInt	Left	Round

For the following VBA functions, you can expect to see improved performance if the function is evaluated over varying attributes.

Asc	Format	Sgn
AscW	FV	Sin
Atn	Hex	SLN
Cbool	Hour	Space
Cbyte	Ipmt	Sqr
Ccur	Lcase	Str
Cdec	Log	StrComp
Chr	Ltrim	StrConv
ChrW	Minute	String
Cos	Month	SYD
CSng	Nper	Tan
Cvar	Oct	Timer
Date	Partition	TimeSerial
DateAdd	Pmt	TimeValue
DateDiff	PPmt	Trim
DatePart	PV	TypeName
DateSerial	QBColor	Ucase
DateValue	Rate	Val
Day	RBG	Weekday
DDB	Rnd	Year
Exp	Rtrim
Fix	Second