Hi All

I have MSAS 2000.
My cube has 30 to 40 mdx calculated measures which are interconnected
with each other i.e one measure calls another calculated measure and
that measure may call another calculated measure.
that cost the query performance while creating reports. It becomes slow
when reports get access or make any reports.
can anybody tell me how to improved the query performance and can give
the required hardware configuration of server.


Regards
Hash

before changing your server identify the bottleneck.
in AS2000 by default most of the calculations are evaluated on the client
side!
So, first play with your connectionstring to force the calculation on the
server side:
execution location=3;Default Isolation Mode =1

else try these connectionstring option:
cache ratio2=0.001; cache policy=7
But verify if your formula continue to provide good results because
sometimes these changes can cause formula evaluation issues.

Monitor your server using the performance monitor. you'll found some
counters like hit cache % etc...
And specially the last query:query num
this counter display how many queries are executed against the cube.
A bad formula can result in hundreds or thousand of small queries which is
really bad!!!
(recursive queries or bad usage of the non empty behavior can cause this)
a good formula and a good MDX query should result in 1 query num only or a
small number of query num for complex formula, but hundreds... no!
so if you suffer this problem, revise your calculated members & formulas to
optimize them.

monitor the server CPU & memory usage.

also remember to use the usage based optimization wizard to add missing
aggregations.

now you'll have a better idea of what's appends on the server and/or the
client and you are ready to optimize the system.

Jerome.

"Hash" <harshal.patil (AT) sify (DOT) com> wrote

i have a simular issue that I hope this answer may pertain to, although I am
using AS 2005. I have a cube that when a user goes to the web front end, the
cube seems to generate all the aggregates at first use. this is of course
slow. My connection string doesnt have "execution location=3" in it, but it
does have "Default Isolation Mode =0". Should I enter the "executioin
location" part? should I change the default isolation mode to =1? what do
these numbers mean?
thanks in advance!

Carl Henthorn

"Jeje" wrote:

with AS2005 the execution location has no impact.
everything is executed on the server side. (so these isolation and execution
location option have no impact)

the first access to a cube is always slower because the cache is empty.
(cold cache)
following queries perform better because you have a warm cache.

you can execute some queries on the server to fill the cache.
So after you process a cube, execute these queries which fill the server
cache and the first access will be better.

if the connection between the web server and the cube server is a local
network and if you are using AS2005 Enterprise edition, set the compression
level connectionstring option to 0 (no compression)
You'll bypass the overhead of this compression which is not required on a
local network.
the compression is not possible with the standard edition of AS2005.

"Carl Henthorn" <CarlHenthorn (AT) discussions (DOT) microsoft.com> wrote