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
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.
.gif) 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.
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;
…