I have got two MDX queries that look very similar.
Each queries a cube on 13 axes.
The members along all axes apart from the first are identical.
In serving the data for one, my SSAS 2005 instance takes a few seconds and
consumes hardly any server resources.
In serving the data for the other the same SSAS 2005 instance chews up all
the RAM on the server and keels over!
The queries are generated programmatically and as such have extremely long
strings with lists of member names.

If anyone can give me some clues as to why I should get such dramatically
different performance from these two queries I would love to hear from you.
Afraid that I cannot field questions about the design of the cubes - I
didn't design them - I just need to get data out of them!

I tried to files with the queries to this post but they are both 211KB in
size so was unable to.

I am probably looking for links to more general resources that will explain
performance
characteristics of processing MDX queries so I can understand the difference
that I am seeing.

Ultimately my objective is to have the query that brings the server down
process as efficiently as the one that doesn't.

TIA

look similar but are not similar... so this little difference can explain
some results.

do you access a calculated measure in 1 query and not in the other? (or
different calculated measures with different formulas)
have you try to optimize your aggregation design?


"Andy" <arb70sok (AT) hotmail (DOT) com> wrote

Jéjé wrote:


Regards
Amish Shah

Like I said - I know nothing about the design of these cubes they have just
been 'deliver' to me and now I need to get data out.
If I change the query so that it has only a single member from the first
dimension it runs OK.

So ... I guess that I will use binary division on my 8000+ members to figure
out precisely which member(s) are causing the problem and at that point I
might be able to answer your questions.

Andy


"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote

well...

sometimes the measure you try to use are not optimize for a large access.
I mean accessing the measure by displaying a large number of members.

Imagine if the measure is something like "number of distinct products" which
is a calculated measure.
then you query this measure to display this by customer (8000+)
then the result will produce a large and long scan of the cube and the
performance dropped.
if querying 1 customer only takes 1 second, in this case doing the same
calculation for 8000+ member can take 8000 seconds!!!!

its why its important to know what you manipulate and if the measures are
optimized for your type of query or not.

"Andy" <arb70sok (AT) hotmail (DOT) com> wrote

Thanks for the observation.

Interestingly as I broke my query down bit by bit (basically I chopped the
number of members on my long dimension by two each time) I got to a point
where both halves of the query ran in a "reasonably" time,
consuming reasonable resources on the server but the merged query consumed
all of the RAM and brought the server down (as my original query had).

Hmmmm.

Fortunately because my queries are generated programmatically it is fairly
straightforward for me to break them down into a large number of small
queries rather that a few large queries.
By doing this I can work around the problem.

However my hunch is that there may be a bug in the MDX parser or some other
part of the AS2005 infrastructure whereby it does not cope well with queries
that have extreme numbers of members along one or more dimensions.



"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote