Appologies for this recurring question.

I have a cube with circumstances that would benefit from 100% server side
processing. I am using Excel PivotTables and OWC on the Client. Server side
processing would benefit due to the low volume of simultaneous users (with
slow equipment) and large number of calculated members and calculated cells
used. I am running AS on a 3.4ghz 32-bit machine hosting 2003 Server.

The connection string is set with the Excution Location and Default
Isolation Mode options set (Execution Location=3;Default Isolation Mode=1

I have tried:

. playing with the Large Level Threshold also with no avail.
. I am using various VBA functions and have ensured VBA
is installed on the server (VBA6).
. I am not using any other registed functions and no undocumented or
unsupported functions (such as CreatePropertySet)
. I am using SP3 and the client has the correct SP3 PTS installed.
. The system does use calculated cells to project values over time.
. The model is fairly complex using 3 different Calc Dims (Currency,
Limits and Scenarios)
. Model uses over 240 calculated members and 10 calc cells.
. It is an account cube (for financial modelling) but it does not use
a typical parent child account dimension instead is is a flat dim with many
measures and calc members)
. As far as I can tell - no measures return a string value and all
aggregations are SUM.

Sorry for the long question but this problem has been answered many times -
I have tried all suggestions and processing still occurs on the client.

Has anyone had this problem or know of a solution?

Thanks
Mark Morris
OLAP Solutions Architect
American Express.
mark.a.morris (AT) aexp_REMOVETHIS_ (DOT) com

Hi Mark,

The bad news is that because you're using Excel and OWC I'm almost certain
you won't be able to force execution location to the server, because they
rely on another connection string property called Default MDX Visual Mode to
do their VisualTotals-style totalling and this pins query execution to the
client. The only option you have is to use a different client I'm afraid.

However, it sounds like your cube might benefit from tuning on the modelling
and calculated member fronts. Can you provide more details on your cube,
dimensions and calcs?

Regards,

Chris



"Mark A Morris" wrote:

Chris

The cube structure is fairly large - I do believe we have some kind of
support with you - is there a way to send the detailed structure to MS for
support/suggestions.

-mark
mark.a.morris (AT) aexpREMOVECAPITAL...TERS (DOT) com

"Chris Webb" wrote:

I don't work for Microsoft any more! But this was the kind of problem I used
to deal with when I worked for Microsoft Consulting. I would suggest you
contact your MS account manager for details on how to engage MCS - these
things are always better dealt with onsite rather than remotely, in my
opinion - or to discuss any other options you might have available for
solving your problems.

In the meantime, if you would like to post more details I'm happy to help...

Regards,

Chris

"Mark A Morris" wrote:

Hi guys,

This is a very sensitive subject for me... Would really appreciate if
you could educate me in terms of how you track the execution location?
How do you find out that the execution takes place on the Client vs.
the server?

Thanks in advance!
Irina

Hi Irina,

In general, Analysis Services 2K query processing will always be split
between the client and the server. The client and the server can both do some
of the same tasks, and when people talk about getting a query to execute on
the server rather than the client they're in fact talking about getting the
more resource-intensive aspects of query processing to happen on the server,
and the bare minimum to happen on the client. So, the way most people track
execution location is by looking at Task Manager on the client and on the
server.

There no way to properly monitor the execution location of a query, and
there is no foolproof way of ensuring that queries will always execute on the
server rather than the client; it's something of a black box in this respect.
Since in many cases it makes sense to have queries execute on the client for
performance reasons, the developers have let the software make the decision
on what to do and we can only try to influence it one way or the other...

Why is this a 'sensitive subject' for you? If you're finding that your
queries are too resource-intensive on the client, I usually find that
execution location is a bit of a blind alley - you're much better off looking
at other ways of optimising the query. Or do you have security concerns?

Regards,

Chris





"Irina" wrote:

Thanks Chris.

Security is not an issue so far...Performance is. My Client is Cognos
PowerPlay (PP) Windows Client which goes to PP server. PP server host
MSAS cube connection and therefore appears to act as MSAS Client.
Hence I have 3 spots where the query exection theoretically might take
place: user machine, PP server and MSAS server.

Sometimes, the query takes a while ( and this while is no acceptable
from the user viewpoint).I just want to find out if moving execution
location ( even though it's a portion of the query) would make any
difference...

Irina

I'm afraid that I don't know anything about the architecture of Cognos
Powerplay, so I can't really comment, but it sounds like execution location
*could* help. However, as I said in my last mail, in my experience you're
going to be much better off looking at other ways of improving performance
such as aggregations and partitions. If you haven't seen it already, take a
look at the Analysis Services Performance Guide:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

I've also answered several questions on this topic recently, so if you
search this newsgroup for posts by me over the last few months you should be
able to find some useful advice.

If you don't make any progress, feel free to post up some details of your
cube (design, calculated members etc) and I'll try to help some more...

Regards,

Chris


"Irina" wrote:

Irina wrote:

Hi Irina,
Well I can give you a general idea there are some articles on
the web about optimization techniques for MDX queries, if you can go
through 'em you would know about some settings carried out on analysis
services which decide whether the execution happens at the server or the
client, for example there's something known as a "large Level Threshold
property" if you set this to say 1000 then if your query returns more
than 1000 members from the level involved then the execution occurs at
the server if it is less than 1000 then it happens at the client.This is
one way to do it ..there are others..check out william pearson's
articles on the net.Hope this helps.

Cheers.
Farhan.