Performance Optimization - Professional SQL Server Analysis Services 2005 with MDX - page 701

[Measures].[Internet Sales Amount]*1.2,
[Measures].[Internet Sales Amount]*1.2)'
Select NON EMPTY [Customer].[Customer
Geography].[Customer].members*
Descendants ([Product].[Product
Categories].[Category].&[3],[Product].[Product
Categories].[Product]) on 1 ,
NON EMPTY {[Measures].[ForecastSales]} on 0
from [Adventure Works]
Even though the above query uses NON EMPTY, you will find the server still
takes around 12 seconds to execute it. This is because the optimized code
path cannot be applied on complex calculated members. In the preceding
query you have a calculated member that is multiplied by 1.2 and hence the
server needs to evaluate the expression to identify if the corresponding cells
are empty. Given the non-empty behavior for this measure, you can specify
NON_EMPTY_BEHAVIOR for this member, and tie the calculated measure to
a real fact measure. The server will use the optimized code path for the non-
empty determination. Execute the following modified query that contains the
NON_EMPTY_BEHAVIOR:
WITH member [Measures].[ForecastSales] as
'iif ([Measures].[Internet Sales Amount] >500 ,
[Measures].[Internet Sales Amount]*1.2,
[Measures].[Internet Sales Amount]*1.2)',
NON_EMPTY_BEHAVIOR = '[Measures].[Internet Sales
Amount]'
Select NON EMPTY [Customer].[Customer
Geography].[Customer].members*
Descendants ([Product].[Product
Categories].[Category].&[3],[Product].[Product
Categories].[Product]) on 1 ,
NON EMPTY {[Measures].[ForecastSales]} on 0
from [Adventure Works]
Did you notice the difference in how long this query took as compared to the
original query? This is why we recommend applying the
NON_EMPTY_BEHAVIOR for the calculated members whenever you are us-
ing complex calculations and you have a fact measure or a simple calculated
member that is helpful in evaluating if a cell is empty. We have just illustrated