Performance Improvements for MDX in SQL Server 2008 Analysis Services

Useful definitions

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).

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.

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.

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

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.

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.

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

Plan Hints in configuration properties

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

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.

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.

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;

…

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.

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

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

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

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

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