I have a MOLAP cube with a customer dimension containing 1.3 million
records. 500,000 are used in the 50 mil. row fact table, the rest are
"potential" customers having demographic data.

Quite often reports will include the dimension won't do any filtering
on it. The resulting set will be a managable size, but using crossjoins
and even nonemptycrossjoins takes much longer than a SQL query would,
if it even finishes.

As an example, I am working on a query that will produce a flattened
sales comparison matrix for Reporting Services:

Store Customer ProductGrp Period1 Period2 Change
Store1 Customer1 ProductGrp1 2 5 3
Store1 Customer2 ProductGrp1 6 5 -1
Store2 Customer1 ProductGrp1 0 5 5

I can write a SQL version for the Cube's source database that runs in a
few minutes, but its counterpart in MDX using a NonEmptyCrossJoin is
rediculously expensive in time and resources. Would maxing out creation
of aggregations help? Any other suggestions?

The separate msmdvldm process isn't used in the current cube,
containing only the 500,000 relevant customers.

Thanks,
Tom McLeod

OLAP was designed to output a rowset consisting of millions of members.
You could spend months tweaking this and tweaking that, but I doubt
you will ever get close to the performance of your relational solution
when you are outputing this volume of data values. If the relational
works fine...why not use it?

- Monty

And is there no way to make a hierachy out of it...we had a scenario with
3-5 different dimensions with lots of empty combinations and making a
hierachy out of this made
a significant change.

Of course a hirachy with business logic would be preferred but otherwise how
about just taking the first letter as hierachy one, and so forth....

Besides this there is some litterature about VLDM - very large dimensions -
and you are way up there...

"Tom McLeod" <tmcleod3 (AT) gmail (DOT) com> wrote

Can you give an idea of the dimension design (how many levels/members,
etc), and of the MDX query used in Reporting Services? Also, are you
including any Calculated Measures in the query and, iso, do they have
their Non-Empty Behavior Property defined?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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