Hi everyone,
I was wondering if anybody can shed some light on this.

I am still experiencing severe memory and cpu problems
while using excel 2002 as client. The problems only occur
when a large parent child dimension (non-leaf data
visible) is used in combination with filtering by several
members of another small dimension.
A user is choosing say two or three elements as filter by
checking the multiple members box. The effect is
increased by the number of filters used.
The data result of the query is a subset of the original
data after filtering. The client will use an extreme
amount of memory (up to all of it as far as I can tell)
and CPU useage goes up to 100% (flatline).

By altering the connection string in the .oqy file in the
folder "\Documents and Settings\<Account>\Application
Data\Microsoft\Queries\" I managed to shift the work to
the analysis server itself.

I used:
Connection=Provider=MSOLAP.2;Data
Source=<Servername>;Execution Location=3;Default
Isolation Mode=1;Initial Catalog=<Databasename>;Client
Cache Size=25;Auto Synch Period=10000

However, now the server is flatlining during this type of
query. However, it responds to other queries in the
meantime. When the query is comleted, cpu goes idle
again. The client goes up to 100% CPU time shortly,
whenever the result set arrives.

I am running analysis services SP3 and recently updated
to SP3a after reading about FIX320719 MSKnowledgebase. I
did not notice any difference in server or client
behaviour afterwards.

Does anyone have advice on this? I know that this has
been an issue before and that SP3 has supposedly fixed
the problem.

TIA,
Lutz

I just found this on
http://vht-can.shadow-realm.org/arc/news-200207.html


quote:
"When using Microsoft Excel 2002 to make a PivotTable
with data from a large Online Analytical Processing
(OLAP) cube, Excel may suck up all the available system
resources on the computer and then hang. This problem
will happen if three conditions are met:

1) The OLAP cube has a calculated member;
2) "Include Hidden Items in Totals" button on the
PivotTable toolbar is not selected;
3) There is at least one NONEMPTY clause in the query
used to create the OLAP cube.

The best workaround is to reverse condition #2 and select
the "Include Hidden Items in Totals" option.