Best SQL Server 2005 MDX Tips and Tricks - Part 1
Overview
SQL Server’s 2005 Analysis Services has introduced several changes to the MDX queries syntax that can lead to better performance than the equivalent AS 2000 queries.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Here is a list of tips and tricks as well as best practices on how to get better performance from your MDX queries in AS2005.
Details
Filter a set and then use it in the Crossjoin. Filter function materializes the set and iterates through the set to check condition to build new set.
Avoid:
filter(NECJ({set1},{set2}),..)
Use:
NECJ(filter({set1},...),{set2})"
Use Rank() over Intersect() function to check if a member exists in a set
The disadvantage of using Intersect() to determine if a member exists in a set is because it treats the member as a set and can not use a better plan in the evaluator.
Avoid:
iif(intersect({ACTUALS_DAYS_SET},{[TIME DIM].[Time Main].currentmember}).count)>0
Use:
iif(rank([TIME DIM].[Time Main].currentmember, {ACTUALS_DAYS_SET})>0
Use Curly Braces in crossjoin with single member
When doing Crossjoins always use sets (add curly braces around single member if used in crossjoin).
Avoid:
Sum( [FINANCIAL VERSION DIM].[Financial Version].[Financial Version Type].&[WSLT]
*{[GROUP STATUS DIM].[Group Status].[Group Status Name].[cancel]
,[GROUP STATUS DIM].[Group Status].[Group Status Name].[turn down]},measure)
Use:
Sum(
{[FINANCIAL VERSION DIM].[Financial Version].[Financial Version Type].&[WSLT]}
*{[GROUP STATUS DIM].[Group Status].[Group Status Name].[cancel]
,[GROUP STATUS DIM].[Group Status].[Group Status Name].[turn down]},Measure)
Avoid unnecessary .CurrentMember in calculations.
It is not a good practice to use .CurrentMember when it is not required in the calculations. The formula engine can generate a better query plan if MDX does not use “.CurrentMember” to select the current member of a dimension. CurrentMember is implied and does not need to be explicitly included in the syntax.
No Need to use [TIME DIM].[Time Main].[Year].currentmember in the following MDX.
WITH
MEMBER [Measures].[M] as
'([TIME DIM].[Time Main].[Year].currentmember
,[FINANCIAL VERSION DIM].[Financial Version].[Financial Category].&[ACTL])'
select
{[TIME DIM].[Time Main].[Year].&[2005].members}
*{
descendants([GROUP EVENT DIM].[Group Event].[Hotel].&[12]
,[GROUP EVENT DIM].[Group Event].[Group Event])}
Use Exists Function
Exists function should be used where ever possible instead of filter on member properties.
Avoid Lookup
Avoid using lookup function. Try to find out if the cube structure can be modified to have the measure available in the same cube.
Use Minus over Filter for a single member
When filtering out a single member from the set use minus over filter function
Avoid:
filter({set},.Currentmember <> "UNKN")
Use :
( {set} minus {&[UNKN] member})
Do not use calculated members that are constants
Although there may not be any difference for a simple example, when combined with other calculations, it can cause a more complicated execution plan inside the server. Note that when using parallelperiod function, often the function evaluates to a constant, so if it is known in advance that is preferred because the engine does not check for certain patterns that are known to be constant. It can be faster for a UI tool to send a first query to resolve parallelperiod (without other calculations), then substitute into the original query, rather than sending one more complicated query.
Avoid:
with
member [a].[NiceName] as '[a].[123]'
member [Time].[YearBefore] as 'parallelperiod( [Time].[year], 1, [Time].[2006].[jan] )'
select { [a].[NiceName] } on 0,
{ [Time].[2006].[jan], [Time].[YearBefore] } on 1
from [MyCube]
Use:
select { [a].[123] } on 0,
{ [Time].[2006].[jan], [Time].[2005].[jan] } on 1
from [MyCube]
How to check if the cell is empty?
Usually empty cell is checked for avoiding division by zero or for checking if value is missing (NON EMPTY analysis).
- Check for non empty to avoid division by zero
For a/b calculations
IIF(b=0,NULL,a/b)
Empty cells are treated as zero in arithmetic operations.
- For checking empty cells (Non Empty Analysis)
Filter([dimension].[hierarchy].member.members, isEmpty(dim.member))
This invokes MDX function IsEmpty, which, as the name suggests, checks whether cell value (in this case at coordinate b) is empty or not. Note, that if the cell value is empty, it is treated as number zero in arithmetic operations, however, it is possible, that b had value zero, which is not empty! Therefore check for IsEmpty is appropriate when the user wants to differentiate empty or missing values from the existing values (for example in NON EMPTY like analysis), but is not appropriate for checks in division by zero.
Note: Dot not ever use IS operator (ie: IIF(b IS NULL, NULL, a/b)) to check if the cell value is empty. The IS operator checks if the member b exists or not.
Miscellaneous design tips
- Avoid assigning values like 0, Null, “N/A”, “-“ to cells that would remain empty otherwise. Use Format_String instead, for custom UI formatting of values.
- Avoid redundant Sum/Aggregate calculations in situations where default/normal cell value aggregation would do.
- Try to avoid IIF. If have to use IIF, see if it’s possible to write it in such a way that one of the branches (then/else) is written “null”.
- Prefer using static literal hierarchy and member references (e.g. Measures.Sales instead of Dimensions(0).Sales, Product.[All] instead of Product.Members.Item(0) etc.).
- Avoid using LinkMember, StrToSet, StrToMember, StrToValue.
- Prefer using the new calculation Non_Empty_Behavior optimization hint instead of writing calculation expressions of the form Aggregate(NonEmptyCrossjoin(Descendants(…, Leaves) …).
- If wanting to get the value of the current cell, consider using an explicit measure name instead of Measures.CurrentMember.
- When writing calculation expressions like “expr1 * expr2”, make sure the expression sweeping the largest area/volume in the cube space is on the left side. For instance, write “Sales * ExchangeRate” instead of “ExchangeRate * Sales”, and “Sales * 1.15” instead of “1.15 * Sales”.
- Consider replacing simple “Measure1 + Measure2” calculations with computed columns (additional measures) in the DSV or in the SQL data source.
- Instead of writing expressions like Sum(Customer.City.Members, Customer.Population.MemberValue), consider defining a separate measure group on the City table, with a Sum measure on the Population column.
Author: Nicholas Dritsas