We're running Excel as a client to several moderate-sized cubes that have
several dimensions in the 5-30k member ranges. We also have a number of
calculated measures, although no other calculated members in the
non-measures dimensions. Excel is installed on the same PC as AS, and users
access Excel through a Terminal Server session.

When the users pivot using the large dimensions, Excel will grab the CPU at
utilization may remain high for a minute or so after the query is cancelled.

What's happening here? I assumed that the Analysis Server would take most
of the CPU time, delivering the result of the query to the Excel client.
Instead, it looks like Excel (accessing only about 30-40 megs of the 2 gigs
available on the server) is trying to perform the entire process on its own.

I've tried changing the client cache size in the .oqy file to 75, presumably
to give Excel access to more memory, but this doesn't seem to make an
appreciable difference. I don't know if the .oqy file is read every time a
pivot table query is made or just when the pivot table is first created.

Does anyone have any suggestions? The calculated measures use some complex
MDX that can't be replicated easily in the underlying SQL databases, so I
can't get rid of them. I'd like to push more processing to analysis
services, or give Excel more memory.

Thanks.

Jon Pearce
Director, Analytics and Reporting
DGA Partners, Inc.
jpearce@the domain below
dgapartners.com

Following up on this - I ran an exceptionally long-running query on a 2-CPU
server and watched the Task Manager. Upon executing the query, the Excel
process spawned 4 tasks and grabbed 1 CPU, running at 50% of overall CPU
utilization and taking about 350 MB of memory. I pressed ESC to terminate
the task, which returned the pivot table to my control. However, Excel
continued to hold onto that memory and run at that CPU utilization. So I
executed the pivot again. This time Excel spawned a fifth task and now ran
up to 99% of the utilization of both CPUs, picking up an additional 350 megs
of memory. When I cancelled this task, again Excel held onto the memory and
kept the CPUs pegged.

When I closed the pivot table spreadsheet (leaving Excel open) Excel
continued to hold onto the 700 megs of memory and ran at 99% CPU
utilization. It wasn't until I exited Excel completely that its CPU
utilization and memory allocation dropped.

What the heck was it doing?? Anyone out there know? Does this mean that
when you abort a query you have to EXIT EXCEL to get control back again?

Jon


"Jon Pearce" <jpearce (AT) remove_this (DOT) dgapartners.com> wrote

Following up on this - I ran an exceptionally long-running query on a 2-CPU
server and watched the Task Manager. Upon executing the query, the Excel
process spawned 4 tasks and grabbed 1 CPU, running at 50% of overall CPU
utilization and taking about 350 MB of memory. I pressed ESC to terminate
the task, which returned the pivot table to my control. However, Excel
continued to hold onto that memory and run at that CPU utilization. So I
executed the pivot again. This time Excel spawned a fifth task and now ran
up to 99% of the utilization of both CPUs, picking up an additional 350 megs
of memory. When I cancelled this task, again Excel held onto the memory and
kept the CPUs pegged.

When I closed the pivot table spreadsheet (leaving Excel open) Excel
continued to hold onto the 700 megs of memory and ran at 99% CPU
utilization. It wasn't until I exited Excel completely that its CPU
utilization and memory allocation dropped.

What the heck was it doing?? Anyone out there know? Does this mean that
when you abort a query you have to EXIT EXCEL to get control back again?

Jon
"Jon Pearce" <jpearce (AT) remove_this (DOT) dgapartners.com> wrote

John,
see my posting "Flatliners" a few days back. The oqy is
read only once when the pivot table is created. So make
sure you alter the oqy before you create a pivot table to
reflect the changes. You can shift almost all load to the
analysis server by inserting EXECUTION LOCATION=3; into
the oqy connection string.

Other than that there does not seem to be a lot you can
do.

HTH Lutz

Hi Jon,

While I cannot give you specific feedback, because it will greatly depend on
the structure of your cube, dimensions and on the MDX used in calculated
members, I can try to explain some general symphtoms you are seeing.

1. The architecture of Analysis Services is such that by default it tries to
offload most of the calculations to the client side. The reasoning is that
if you have many clients, the computations can be distributed across each
one of them, therefore easying the load on the server. Since in your
scenario you have both Excel and the server on the same machine and you use
Terminal Services, it doesn't really matter who does the work - client
(Excel in this case) or server.
You can use Lutz's advice of providing Execution Location=3 (as well as
Default Isolation Mode=1) in the connection string if you have weak client
machines on the slow links connected to very powerful server.

2. The reason why you see Excel using CPU after the query is canceled is
most probably because Excel uses asynchronious query execution, which spawns
new thread to do all the work, and UI thread is responsible for the "Cancel"
button. When you hit cancel, Excel regains the control, but the
asynchronious thread will continue to run for some time. Typically this time
should not be long, but again depending on MDX you use in calculated
members, it can be longer.

So my best advice to you would be to optimmize the MDX expressions in your
calculated members. There are many different techniques to optimize MDX -
and there are many resources to look for. You can start with George
Spofford's book "MDX Solutions" which has chapter about it.
Maybe if you post your expressions in this newsgroup, some people will also
be able to help.

HTH,
Mosha.

--
==================================================
Mosha Pasumansky - 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.
==================================================

You are right. If the cube have large dimensions, then forcing execution on
the server prevents them from being downloaded to the client.
The reason why you don't see the server grabbing memory is because it
already grabbed it when started.

It is not possible to change the manner in which Excel generates MDX. I also
disagree that Excel generates "inefficient" MDX. I beleive the problem lies
with the complexity of the calculated members inside the cube. If you create
the same view using Analysis Manager Cube Browser, or some other 3rd party
tool - does it perform differently ? Remember - you need to recreate exactly
same view to have fair comparison.
NonEmptyCrossJoin function is great when it is applicable, but it is not an
universal answer to all performance problems.

Yes, such flat hierarchies can be part of the problem. Creating invisible
grouping level may help.

There is no one problem that can be fixed in PTS or Excel. Depending on the
definition of calculated members, there can be different solutions.
Therefore there is no universal fix which will magically make all the cubes
perform 10 times faster.

HTH,
Mosha.

--
==================================================
Mosha Pasumansky - 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.
==================================================

Yes! Undoubtedly! I have commented on this issue for years. Every other
tool I have tried that generates its own MDX is much, much faster with
calculated measures!

In Excel, the only way (of which I am aware) to see the MDX is to use the
MDX Property of the PivotTable object in VBA. Take a look at the MDX shown.
Now, I can't be certain that said MDX is actually what is used--since the
MSDN Library says,
http://msdn.microsoft.com/library/de...l/xlpromdx.asp
"Returns a String indicating the MDX (Multidimensional Expression) that
WOULD be sent to the provider to populate the current PivotTable view."
(Capitalization of WOULD added for emphasis.) Very strange description,
IMO.

I am an engineer and the lead user of a group using Analysis Services in a
fairly uncommon matter--not to analyze business data, but to analyze energy
use data. I led our group to OLAP/Analysis Services several years ago, and
was the person working with the OLAP developer in designing the measures.
Many of our measures are semi-additive. I am an Excel developer, and love
Excel, and I build prototypes of most of our specialized tools using Excel
prior to getting our development team to build more robust, server-based
versions. The query speed issue with Excel is the ONLY thing keeping us
from using Excel for all of our analysis tasks, since it is otherwise
superior to other tools for our purposes. I wish I knew the extent to which
various Excel add-ins improve the situation.+

--Bill Koran

This is interesting, because in my experience, Excel would oftern generate
better MDX, then say OWC.

Another way is to inject "Log File=..." into connection string (in the oqy
file). This is universal method to log MDX queries being passed to PTS for
all providers. This will give you the most reliable way to see what get
passed between Excel and PTS

I just did another test with Excel vs. Analysis Manager Cube Browser, and I
don't see anything wrong with Excel's MDX. Can you give examples, I will
look into them.

--
==================================================
Mosha Pasumansky - 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.
==================================================

Lutz - thanks for the suggestions. I'm not sure how to specify the
"non-empty behavior", but I have several good references that will help.

Jon


"Lutz Morrien" <nospam.lutz.morrien (AT) ocb (DOT) com> wrote

Mosha - thanks very much. The fact table in the cube is pretty small, so
I'm surprised at the time it takes to perform the requested queries - it
would take much less time to do relational queries from the same database.
I'll take a look at the calculated measures and see what can be done with
them. There was also a KB article suggesting something dealing with
"include hidden members in totals" that I'll try.

Jon

"Mosha Pasumansky [MS]" <moshap (AT) microsoft (DOT) com> wrote