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