Excel 2007 Pivot Table - Poor MDX?

[Database Administrator]

Hi Folks,
Am getting stumped. I have a simple cube, 9800 records in a single fact
table. 10 dimensions, all nicely star-schema. Very little in the way of
hierarchies. Tiny!

In Excel the user wishes to use a Pivot table.
They are using a simple base measure (summed).
They have a dimension in the filter where they choose 22 of 24 members.
They pull three dimensions down to rows - first is a hierarchy with 10
members at the top, maybe 4 children for each; second with about 1000 members
(clients), the last with about 4000 (contracts).
When trying to execute this "OLAP Query Executing"... and it never comes back.

Looking at Profiler:
The Filter is built into a Calculated Member (on Measures) - uses AGGREGATE,
listing the 22 members.
The rows are being requested using a hideous tangle of CROSSJOIN HIERARCHIZE
DRILLDOWN EXCEPT ADDCALCULATEDMEMBERS... (spaghetti).

I am guessing that essentially it is summing individual members over one
dimension while full-cross-joining 1000*4000 members on the other...

Put this in SSMS:
Run without the Calc Member - 30ish seconds
Run without the hierarchy bit - 30ish seconds
Run together... roll on the apocalypse.

Finally, the questions:
Might anyone suggest anything to set in Excel to improve behaviour?
Is there anything that you might suggest to cube/dimension structure which
might be able to improve behaviour?

I have suggested building a hierarchy to provide a navigation path to the
included members but the users are pushing back (too many combinations).

I apologise for the longshot nature of the question; I am going round in
circles! Any help welcome...

Thanks...

[Database Administrator]

Out of curiosity, how many measures have you defined...not calculated
measures, just standard measures from the fact table?

-exologic

[Database Administrator]

Oh, also, are you getting the same bad query perf in Sql Server
Management Studio using the same MDX query as with Excel, that is,
referencing the same cell set?

-exologic

[Database Administrator]

I have 5 measures: 3 simply sum, 1 count, 1 distinct count. Fact count is
9500ish.


"fimiani@exologic.com" wrote:

> Out of curiosity, how many measures have you defined...not calculated
> measures, just standard measures from the fact table?
>
> -exologic
>

[Database Administrator]

The performance is just as bad.

I would post the code but after posting up my question the server has gone
t-u (owned by managed services) and I cannot connect... users are getting
upset...

"fimiani@exologic.com" wrote:

> Oh, also, are you getting the same bad query perf in Sql Server
> Management Studio using the same MDX query as with Excel, that is,
> referencing the same cell set?
>
> -exologic
>

[Database Administrator]

FM,

Ok, so it's the cube, not some issue with the UI...that is what I
figured.
Simplest thing: Did you run aggregation design on your patitions, even
a default one?
Are you 32 or 64 bit SSAS? Do you have other cubes that run fine, and
just this one is the problem, or is this the only cube that you are
running?

I have had small cubes run badly for various reasons.

Do you have any MDX Scripts defined other than just CALCULATE at the
top?

Just trying to pinpoint *what* is the problem first, and *then* how to
fix it.

Are the end-users generally running the same reports daily or are they
truly ad hocing around at random? If the former, you could just run
those canned queries ahead of the end users doing it (via several
methods, one of them just doig so with an SSIS package, etc.) and then
the result sets would be cached and that would speed things up quite a
bit.

-exologic

[jcmjunior]

The problem is the lot of mdx requests that the pivot table generates whie you navigate in hierarchized dimensions. It's not a pivot table problem neither server problem.


Try to use profiler to see this.