Very Computer

Hi there,

Im trying to speed up a very common query:

TOPCOUNT(
    NONEMPTYCROSSJOIN(
        {[cust].[cust].members},
        {[Prod].[Prod].members}
    ),
    20,
    [Measures].[qty]
)

with the "generate(A,crossjoin(A.currentmember,B))" as
suggested by Spofford in "MDX solutions":

TOPCOUNT(
  generate(
     {[cust].[cust].members},
     topcount(
         NonEmptyCrossjoin(
             {[cust].currentmember},
             {[Prod].[Prod].members }
         ),
         20,
         [Measures].[qty]
     )
  ),
  20,
  [Measures].[qty]
)

This results in a query that takes many times longer than
the original query, even the Test MDX button in proclarity
to check the syntax take about a minute (!).

What am i doing wrong (or is this teqnique only relevant
when crossjoin > 2 dimensions) ?

/Sverker

Actually, I wouldn't (and don't) suggest you could optimize
NonEmptyCrossJoin() in the way that you optimize CrossJoin(). Sometimes
you need to use CrossJoin(), and then I would recommend the
optimization. But NonEmptyCrossJoin() executes differently than
CrossJoin(), leveraging the internal indexes of the cube.

Off the top of my head, I can't think of a general-purpose optimization
for your TopCount (NonEmptyCrossJoin()) expression.

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab

ISVs & IT organizations: Find out how DSS Lab can speed your
development!