==> Profiler Query duration 950 ms
Added a calculation member - code see below
and include the member in the query the response time
==> Profiler Query duration 5500 ms
increaes about 500%!
The special point "Effective area in m2" uses the "FirstNonEmpty" Aggregate function - while we switch for testing to "SUM" the performance comes back to normal!
WHAT could we do - ist there an improvement in SP2 (we use already SP1)
Thanks for your help
==============
case WHEN [Measures].[Effective area in m=B2]=0 Then null ELSE
( [Real estate].[Top state].[Top status group].&[rent],
[Measures].[Effective area in m2] )
/
(
[Measures].[Effective area in m2] )
END
( [Real estate].[Top state].[Top status group].&[rent],
[Measures].[Effective area in m2] )
/
(
[Measures].[Effective area in m2] )
END
Performance with simple calc on FirstNonEmpty
snehthakkar
I have found out the reason - if we query without a time the default (Allmember) is used ant the time dimension is really long (03.02.1900 on day level - means the LastNonEmpty as to scan over 100 Years on day level for the first value)
If i use a dedicated year the query is fast.
Joe Ingle
What is the correct format for NON_EMPTY_BEHAVIOR Or are all these OK The documentation is sketchy.
I have seen
NON_EMPTY_BEHAVIOR = [Measures].[Dollar Sales]
NON_EMPTY_BEHAVIOR = '[Measures].[Dollar Sales]'
NON_EMPTY_BEHAVIOR = "[Measures].[Dollar Sales]"
NON_EMPTY_BEHAVIOR = {[Measures].[Dollar Sales]}
Little Green Frog
Hi there,
I have a suggestion that might help if your comparison to 0 is equivalent to a comparison to null. Try setting the non_empty_behavior of this calculated member to measures.[effective area in m=B2] and eliminating the condition
( [Real estate].[Top state].[Top status group].&[rent],
[Measures].[Effective area in m2] )
/
(
[Measures].[Effective area in m2] )
, non_empty_behavior=Measures].[Effective area in m=B2]
Now this is a tad dangerous (actually, a lot) and I'm reluctant to suggest this. Because when mdx compares null to 0, the condition will return as true. So you might be returning and error when you don't want to. But try it out - I'd like to see how it works out.
Richard