Ways of Improving MDX Performance and Improvements with MDX in Katmai (SQL 2008)
1. Use calculated members with Scope assignments instead of using IIF
The reason this option is faster is because the scope subcube definitions enable the Query Execution Engine to know ahead of time the calculation space for each business rule. Using this information, the Query Execution Engine can select an optimized execution path to execute the calculation on the specified range of cells. As a general rule, it is a best practice to always try to simplify calculation expressions by moving the complex parts into multiple Scope definitions whenever possible.
No-one writing MDX ever wants to show the 1.#INF value in a cell is because it’s looks ugly and typically end user tends to see it as errors in the data. However, it’s not an error it only look like that. If you don’t check for divide by zero errors, you get either positive or negative infinity represented as 1.#INF.
On the other hand, empty cells are typically easier to interpret as no input data. To have this check, lot of time is spent checking division by zero conditions. If it is SQL AS 2005, take a call...you want to perform expensive calculations and deliver clean output OR fast calculations with such ugly looking output (1.#INF).
If it is SQL Server 2008 then things are pretty much taken care, if the numerator is empty, the formula returns empty (regardless of the denominator), but if only the denominator is empty, you get an error. Multiplication by empty results in empty, but with addition and subtraction, empty = 0. The idea seems to be that if the numerator is empty, then an empty denominator is OK, but if the numerator is not empty and the denominator is, something is wrong and an error should appear. Think of examples like price = dollars/units or ratio = current/parent. (This comes from a recent training I had on SQL BI Performance Tuning)
Following are the different ways of checking the denominator. Give it a try and see which one works best for you...
IIF(b = 0, NULL, a/b)
IIF(IsEmpty(b), NULL, a/b)
IIF(b IS NULL, NULL, a/b)
Using IIF statement, calculation space is evaluated Cell-by-Cell. Because of this Query Execution engine selects less optimized path and so its query response time is high. Another computation method is 'Block Computation'
2. Remove empty tuples from MDX result.
This is always a good practice, because empty tuples can not only increase the number of rows and/or columns in your result set, but they can also increase query response time.
Empty tuples can be removed by using NON EMPTY keyword, NON EMPTY function or the NON EMPTY BEHAVIOR (NEB) hint
You can also evaluate empty by using Exists function on a measure group
Use should use Non_Empty_Behavior where ever possible to enable the query execution engine to use bulk evaluation mode. Beware, if you use Non_Empty_Behavior incorrectly, you will return incorrect results. Let's see what Mosha says on this... a case study on different ways of controlling non-empty-behavior here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx
3. Other techniques of Summarizing data
Summarizing data through MDX can potentially result in slow performance in large-scale cubes or cubes with many nested MDX calculations. If you experience performance issues summarizing data in MDX then you may need to consider other approaches for improving performance like Creating Named Calculations in DSV, Using Measure expressions, Use semiadditive measures and unary operators
And now, I am sure you would be keen to know what are performance improvements done with MDX in SQL Server 2008 (Katmai)....
Performance Improvements for MDX in SQL Server 2008 Analysis Services: http://msdn.microsoft.com/en-us/library/bb934106(S...
MDX in Katmai: Dynamic named sets: http://sqljunkies.com/WebLog/mosha/archive/2007/08...
If you are new to MDX:
MDX Syntax Elements (MDX): http://msdn.microsoft.com/en-us/library/ms146020(S...
MDX Language Reference (MDX): http://msdn.microsoft.com/en-us/library/ms145595(S...
I will be writing about 'Block computation in Katmai and its Impact on Performance' in my next post...stay tuned... :)