Given a cube with five dimensions, four additive measures and no
calculated members, is it reasonable to expect that a non empty,
nested crossjoin of three dimensions should take over 40 seconds?

Selected dimensions have 10, 7000, and 10000 leaf members respectively
and the other two dimensions are sliced on a single member each.

The client is a 2-way 1.4Ghz P3 w/2GB RAM; server is an 8-way 1.4GHz
Xeon w/5GB RAM.

Last Query performance counters show the query is answered from cache;
client (MDX Sample App) spends 40 seconds CPU time (100% of one
processor) before displaying 200 result rows.

Member keys are all CHAR datatypes between 1 and 14 chars each. Will
changing to integer keys have a dramatic improvement?

Aside from rewriting the query to use NECJ (which as Mosha points out
isn't a general solution), is there anything else I could do to
improve the performance significantly?

Will upgrading to Yukon help?

Can you capture the exact MDX query on the client, perhaps using a log
file? This may provide some clues.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The MDX is just what you'd expect:

select {Measures.members} on rows,
non empty crossjoin(crossjoin(D1.members,D2.members),D3.memb ers) on
columns
where (D4.M4, D5.M5)

The cardinalities are: D1=10, D2=7000, D3=10000, D4=3, D5=25000

The MDX is automatically generated by Excel with the hierarchize and
drilldownlevel calls removed as they don't appreciably affect the
query time.

The density index is very low (3.8e-8):
2M fact table rows / 10*7000*1000*3*25000 (5.25e13) tuples

So I suspect the only alternative is NECJ but that rules out Excel as
a client.

Also, it seems as if there's an internal heuristic that says "if the
number of tuples generated by the crossjoin will exceed 1 billion then
don't return any positions on the axis." The above query scans 700
million tuples so it may fall just under an internal limit.

Not that you can use the NECJ() version of the MDX query in Excel, but
how long does that take to execute, by way of comparison? That would
give an idea of the gap.

Earlier discussions in the newsgroup suggest that there is a threshold
of cross-join set size, above which a faster algorithm for finding
non-empty tuples is used. This can be controlled in the connection
string:

http://groups.google.com/groups?q=no...hl=en&lr=&ie=U
tf-8&oe=utf-8&selm=eocezqilcha.4128%40tkmsftngp08&rnum=7
Subject: Re: NON_EMPTY_BEHAVIOUR calc member option
View: Complete Thread (6 articles)
Original Format

Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-11-25 05:24:12 PST

This is my understanding of how it all works:

Analysis Services it uses to remove empty cells: the 'old' one, which is
slow but 100% reliable, and the 'new' one (as used in NONEMPTYCROSSJOIN)
which is faster but can only be used when there are no calculated
members,
custom rollups etc in the query. To allow Analysis Services to use the
second algorithm more often, and therefore make queries faster, you can
use
the NON_EMPTY_BEHAVIOR property to say that a calculated measure will be
null whenever a given base measure is null. However, this does not
guarantee
that the second algorithm will be used! Analysis Services will still
sometimes use the old algorithm in certain cases.

Take the following example:

WITH MEMBER MEASURES.DEMO AS 'IIF(MEASURES.[UNIT SALES]>25000, 1,
NULL)',
NON_EMPTY_BEHAVIOR='[MEASURES].[UNIT SALES]'
SELECT
{MEASURES.[DEMO]}
ON 0,
NON EMPTY
CROSSJOIN(
[Product].[Product Family].MEMBERS, GENDER.GENDER.MEMBERS) ON 1
FROM SALES

You would expect it to simply assume that since Unit Sales is not null
for
this set of tuples, Measures.Demo will not be null (even though this is
not
actually true), and so in turn empty cells should appear despite the NON
EMPTY clause. However, if you run it on Foodmart 2000, you'll see that
even
though I've lied about the NON_EMPTY_BEHAVIOR of this calculated
measure,
Analysis Services hasn't believed me and has still filtered out the
empty
cells.

The key to getting it to believe my lie is the NON EMPTY THRESHOLD
connection string property (default value=5000). This is the lower
threshold
for the number of tuples(?) that must be evaluated for Analysis Services
to
decide that it is worth the effort of using the new algorithm; in the
above
example it's still using the old algorithm because the query is so
small.
So, if you rerun the above query with NON EMPTY THRESHOLD=1 then you'll
see
that the result set has changed, and it has now believed my lie: empty
cells
appear in the result set. The same thing happens with values up to 5,
but as
soon as you use NON EMPTY THRESHOLD=6 you'll see that it has gone back
to
the old algorithm and empty tuples are filtered again.

NB as with all this semi-documented stuff, it should be used at your own
risk.
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Less than one second with NECJ() as opposed to 40+ seconds with non
empty crossjoin(). There are no calculated members.