We have developed an Excel add-in with functions that make easier the access
of data from an OLAP Server.
My problem is the following:

We have customers that have big Excel spreadsheets using our add-in. Each
workbook contains like 14,500+ formulas (each formula is an MDX query to a
specific cube returning a cellset value).
1. The first time the Excel workbook opens it takes between 1 min 30sec to 4
min to recalculate.
2. After step 1 happens, every other user in a different workstation that
opens the spreadsheet will have recalc times of 40sec or less.
3. After several hours of using some of the sheets calculation times goes up
again to 1 min to 4 min.

It looks like the cache somehow reaches a threshold and then is cleaned.
My question is: Is there a way to tweak or configure the cache so it stays
without being cleaned for a specific amount of time or memory size?

Is there any other way to speed up MDX queries? Can seem to any
documentation on definitions of some of the connection string properties for
MSOLAP.2.

Please Help,

DT

Hi,

There is a connection string property called Client Cache Size, by default
its 25percent of available memory on the client machine. If the client
approches this it will flush the entire client cache and start fresh. You
can adjust this to be larger, but your client will run into the same issue
just further down the road.

There is a server side cache as well, which is cleaned by a cleaner thread.
You can control this cache by increasing the Memory Conservation Threashold,
but don't set it to your entire memory otherwise you could start to page or
run the server out of memory. If you have 2GB of ram in your server, set it
to 1.7.

14,000 queries in 1 to 4 min isn't really that bad.


"dwten" <dwten (AT) discussions (DOT) microsoft.com> wrote

Thanks for the info but we tried the client cache size already and didn't
found any improvement. We saw some by changing the server side Memory
Conservation Threshold.

But anyway, for us programmers 14,000 queries in 1 to 4 minutes is very
impressive. For the customers is not, and in fact they use to have a product
named TM1 that ran the same workbook in less than a minute, that is what is
getting me frustrated. We recommended a big box and OLAP Server scalability,
but it's the end user who has the final words, and right now the end user is
not happy.

DT

"David Botzenhart" wrote: