Hi,

Sorry to ask so many questions around the same topic.

I have 2 MDX queries running through the sample app. Both use the same
selction criteria. One does aggregation and the other performs
selection using nonemptycrossjoins in the row axis.

The query performing aggregation takes 31 seconds to run and the one
which doesn't takes < 1 second to run. However the query performing
aggregation provides the exact answer I want. At this time I am using
very limited selection and having done some benchmarks the aggregation
query takes twice as long for every new axis or 1 or 2 new items on an
existing axis added. For my purposes this is far too slow (although
the result is excellent when it does arrive!). I am Creating Members
to perform the aggregation.

The sample query with aggregation is -

WITH
MEMBER [A].[MY_MEMBER] as 'SUM({[A].[All A].[3], [Ad].[All A].[10]},
[Measures].[PP])'
MEMBER [b].[MY_MEMBER] as 'SUM( {[b].[All B].[0], [b].[All B].[1]},
Measures.CURRENTMEMBER)'
MEMBER [FINDQTR].[PeriodAgg] as 'Aggregate({
[FINDQTR].[1994].[Quarter 1]:[FINDQTR].[1995].[Quarter 4]})'
SELECT non empty CROSSJOIN ({[ProfileQtr].Members}
, [Measures].[PP]}) on columns,
Non Empty CrossJoin ({[AnnouncementQtr].[All
AnnouncementQtr].Children}, {[b].[MY_MEMBER]}) on rows FROM Cube
WHERE ([FINDQTR].[PeriodAgg], [A].[MY_MEMBER] )

The question is ...

Is there a far more efficient way to perform the aggregation or is this
not something that MDX can do quickly (I have chosen the wrong tool for
the job).

Thanks,

Ben.

Hi, Ben

What about fiscal year hierarchy? If your fiscal year starts with april, you
can avoid
Aggregate({[FINDQTR].[1994].[Quarter 1]:[FINDQTR].[1995].[Quarter 4]}) and
use real dimension member.

Try instead of 'non empty crossjon()' the 'nonemptycrossjoin()'. It is much
more quick.
There are some tricks with nonemptycrossjoin. Take a look in msdn and news
groups.

Thanks,
Vladimir Chtepa

"Ben" <gringogordo (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:1136895822.986116.287330 (AT) g43g2000cwa (DOT) googlegroups.com...