Hello,I hope someone can help as I have been searching for days now
for a solution.

Environment:
SQL server 2000 Analysis server sp3a
Client: Excel XP pivot table
Fact table: 350 000 rows (Oracle 9i database table)


We have a MOLAP cube with 20 dimensions, 6 measures and 4 calculated
members. The pivot table that we connect to the cube is performing
very well with the dimensions placed on the various axis' . This
however is only the case when we are using the measures in the 'data'
area of the pivot table. As soon as we place one of the calculated
members ie Profit;([Measure].Sales value-[Measure].Cost of sales) into
the data area, excel starts running the OLAP query and when monitoring
the memory and CPU on the citrix server which is running the pivot
table , the memory starts to escalate and the CPU is extrememly high.
The memory just keeps on climbing until we close excel completely
(which closes the UI thread that has been spawned).

Findings:
a) When looking at the queries that have been sent to the server, I
can see that the query has executed 70 000 times with execution time
of 00:00 seconds.
b) Changing the execution location and isolation mode in the pivot
table connection string had no impact (it actually went slower)


In many of the newsgroups, I have found that quite a couple of people
have had the same problem. The solutions that have been provided are
a) Check the quality of the MDX that excel is producing
b) Buy an excel add-in
c) Create specific aggregations for the calculated members at cube
build time.
d) For divisible calculated members, check the NON EMPTY clause on the
properties of the member


Ignoring points (a) and (b) for the moment, is (c) a worthwhile
endeavour and if so , how can we create specific aggregations.

When running the storage design wizard, after about ten minutes , we
only have 4% performance with 470 aggregations and that seems a little
bit low to me.

It seems like Excel is doing the calculations as it cannot find any
aggregations in the cube and therefore sends an mdx query for each
item that is displayed in the axis on the pivot table and then gets a
result from the SQL server.

Any help would be much appreciated as I could not find anything at
Microsoft either except for the distinct count issue.

Thanks in advance

I don't think that (c) applies in your case, and I suspect that (d) is the
culprit here.
In Analysis Manager - go to the Advanced properties of the calculated
member, and set its Non Empty Behavior to [Measures].[Sales]
I beleive this should help in your case.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"Francois" <f_de_wet (AT) hotmail (DOT) com> wrote