hi,

sql2005+as2005
processed cube ~2gb in size



AS2005 server:
4gb ram
1 cpu ~3gb xeon
2gb LAN
8 hdd RAID 10

AS2005 and sql installation is default.

the problem is that when any end-user tries to view data via pivot
table, the AS server's cpu jumps up to ~100% and these calculations
takes for N hours [actually i don't know what a hell as2005 is doing as
hdd IO or RAM don't jumps sky high] and all other users with small
queries must wait.

Previously the same cube was on AS2000 and no problems with "killing"
server’s CPU occurred. I assume that all “calculations” takes place on
the AS2K5 side as the excel file is only 200kb and the same file
refreshed from the AS2000 cube was ~20mb, so I assume that required
data where downloaded and calculated in the excel not on server...

is it possible to force as2005 to upload data to end-user and NOT to
make any recalculations at server side??


thanx

1 cpu?

any why do you have raid man

throw that crap away and go get a dual or a quad xeon

and for the record; OWC is a LOT better client than Excel.
performance; stability; portability; sharing report definitions

Excel-- as in XLS-- is a friggin disease

first AS2005 do ALL calculations at the server side, you can't change this
behavior.
This required a little more network access, but greatly improve the
calculations by keeping the cache on the server and share this cache swith
multiple users.

check your calculations and use the new Exists, Existing and nonempty
keywords if you can.
in my case I have greatly improve the performance by playing correctly with
these functions.

does your problem appear in 1 measure only? all measures?
do you use scopes?
use the profiler to see what's appends in your cube.
check if you have recursive calculations.

"benamis" <nera (AT) meilo (DOT) lt> wrote

thanx Jeje i'll try to play with these functions.

the problem is with all measures... these measures are big ~30K lines.

is it possible to precache just after the cube was processed ?


Jeje wrote:

30K lines = 30 000 rows in your source?
or the number of rows returned to the end user?
what type of measure do you have?
how the cube is aggregated?

you can precache your cube by executing some queries just after you have
processed your cube.

"benamis" <nera (AT) meilo (DOT) lt> wrote

Also, please give more info on your cube:
Any distinct count measures?
Parent child dimension?
Are you trying to see real measures or calucated measures?
If calculated measures, then do they have NON_EMPTY behaviour setup?
Anything else that is not standard


Regards



"benamis" <nera (AT) meilo (DOT) lt> wrote