We use ProClarity as the front end for our cubes and one particular
query a user was trying to run was taking about an hour to come back
although it comprised only 16 rows; I took a look at the MDX that was
generated & tried it in the Sample Application (the result took the
same time to return).
I don't understand why it appears to be such a huge query - the MDX is:
SELECT
NON EMPTY
{ [Measures].[Our Share EPI Conv],
[Measures].[USM Prem Conv],
[Measures].[USM Claim Conv],
[Measures].[SCM OS Conv],
[Measures].[Incurred Claim CONV],
[Measures].[GrPdIncLR%],
[Measures].[Count] }
ON COLUMNS ,
NON EMPTY
{ { { [AccYr].[default].[Acc Yr].&[2002],
[AccYr].[default].[Acc Yr].&[2003],
[AccYr].[default].[Acc Yr].&[2004],
[AccYr].[default].[Acc Yr].&[2005],
[AccYr].[default].[Acc Yr].&[2006] } *
{ DESCENDANTS( [Policy].[NewCOB].[All Policy],
[Policy].[NewCOB].[Polid] ) } } }
ON ROWS
FROM [vPolicy]
WHERE
( [Broker].[default].[Bkr Grp Cd].&[LIL] )


which is just a few calculated measures for the 16 leaf level members
of the Policy.NewCOB dimension where the broker is LIL.

Now the PolId level DOES have a lot of members (170,000 ), but surely
the WHERE clause filters the result set down to the 16 required?

I tried running it in the Sample App without the NON EMPTY keyword but
got the error -
"Unable to display opened cellset. Unable to allocate memory for
Flexgrid"
which also sounds as though it is dealing with a very large result set.

Can anyone explain this to me?

Thanks

Rachel

Hi Rachel,

If there are any calculated measures in the query, ensure that their
"Non Empty Behavior" property is set appropriately:

http://support.microsoft.com/default...b;en-us;304137
Keyword
...
SUMMARY
In some cases, a query slows down considerably when you use both the NON
EMPTY keyword on an axis of a Multidimensional Expression together with
a calculated member.

This article describes how you can optimize a query, by using the Non
Empty Behavior property for the calculated member.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak, that is something I wasn't aware of; unfortunately I
don't think it will help me here because nearly all of the measures are
actually calculated CELLS populating base measures on the fly.

Also, most of our calculated members are actually aggregations of base
measures, i.e. 2 or more added together, and so - please correct me if
I am wrong - if I specified one measure for the Non Empty Behaviour
then this might lead to incorrect exclusions.

I suppose I am just confused as to why the MDX apparently has to trawl
through ALL the policy leaf members when there is a filter on the where
clause..

Rachel

Rachel,

The problem is that the where clause constraint on policies is indirect
- it only specifies a broker directly.
But there should be some possibilities, depending on your data
relationships, cube design, and on whether you're using AS 2005:

- In AS 2005, multiple measures can be specified for Non Empty Behavior.
Even in AS 2000, a single measure should suffice for a single physical
cube - but not if there are multiple cubes combined in a virtual cube.

- Is the relationship between broker and policy truly determined by the
fact data or calculations - if it can be modelled in the policy
dimension, identifying the relevant policies would be facilitated. In
any case, there should be ways to filter down the policies in MDX.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***