Hello... I'm capturing Analysis Services activity in SQL Profiler.
Aside from capturing the typical MDX query, etc. I am seeing a bunch of
'non-cache data' entries that are chewing up time. As a result, the
query response is slower than I would expect. We have created all the
aggregations the aggregation wizard allows for the partitions in the
cube, and we are talking about a cube that is less than 50GB.

Can someone help me understand the non-cache entries and how to 'cache'
those items so I don't take the performance hit?

Thanks, Dean

the first case is when a user query the cube against non cached data, cold
cache.
so the first access is always slower because the cache is empty, next
queries against the same cells in the cube will ask cached results. (warm
cache)
so you can preload the cache by executing common used MDX queries against
the cube after you have loaded the cube with new data.

another case can be caused by bad written queries or calculated measures. it
could appends that AS can't cache the result and must always reevalute the
formula. try to get a deeper look in these formulas.


"Dean" <dfurness (AT) quilogy (DOT) com> wrote

Theoretically, then, if I run the query I captured in Profiler in the
MDX query tool, and cache it, I should not see the non-cache entries -
correct? Or even with multiple executions of the query, same deal.

If I still see non-cache, then I can bet the calculated measures are
causing it - agree?

--Dean

yes, you can take a look at the formula if the same query result in same
non-caching accesses.
but maybe there is other reason, like a lack of memory.

I d'ont know how the cache works in detail

"Dean" <dfurness (AT) quilogy (DOT) com> wrote

You can warm the cache using the "ascmd.exe" sample provided with the SS2005.

"Jeje" wrote: