I'm having a severe performance issue hitting one of my cubes with a pivot
table in excel that I need help with. I'm a relative noobie to analysis
services. In my cube, I have a dimension containing 25K serial numbers.
When I drag this dimension to the row fields area, all 25K serial numbers
show up in about 4 seconds. I wish that were quicker, since selecting the
same data from a relational database query from excel takes only one second
or so, but that's not the serious issue. The problem comes when, after doing
nothing else like selecting another dimension or measure, I try to select one
or more individual serial numbers. When I do so, excel takes 100% of the CPU
and pretty much freezes.

Now there are other instances when this inexplicable behavior occurs. How
can I tune my system to prevent this? I'm not looking at aggregation at this
point because I'm only dealing with a dimension here. I have not done any
tuning at all at this point. I will provide any additional information
required to help get me out of this quagmire.

Thanks in advance, Brian Parker

If you can create an intermediate level in the dimension, above the 25K
serial numbers, that should alleviate the problem - Excel initially only
has to list the intermediate level members. Also, when you select a few
serial numbers, the MDX query size should reduce, because Excel
typically enumerates the excluded, rather than selected, members.


If the scenario in this KB article applies, you can try selecting
"Include Hidden Items in Totals":

http://support.microsoft.com/default...b;en-us;307787
OLAP Cube
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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