Hi,

I have found several posts to this or related groups when I was searching
for memory leak and cellset in google, although none of the follow-ups (if
any) showed a clear solution to the problem (compare Tony Procter in thread
http://groups.google.de/groups?hl=de...3DN%26tab%3Dwg )

I have a cube that I access through adomd within an Excel-VBA-App. My cube
contains 6 dimensions of which 4 have less than 20 members and 2 dimensions
with 800 to 900 members each . Total size of the cube is 0.89 MB on one
partition.

Now I execute a crossjoin query on the two large dimensions:

Set cs = New Cellset
cs.Source = "SELECT CROSSJOIN(Dim1-leafmembers, Dim2-leafmembers) ON ROWS,
SomeMeasures on COLUMNS FROM MyCube WHERE (Dim3-Slicemember)"
cs.Open

On cs.Open, the memory used by my process raises by 80 MB. If I close the
cellset, deallocate it and execute the same query with a different
slicemember and a new cellset, another 80 MB come on top, without the first
block being released. So, after 7 to 8 queries my app has consumed more than
0.5 GB of memory.

The memory is released only when I close the ado-connection. Client Cache
Property has no impact on the behaviour described.

If anybody has experienced the same problem and knows about a solution
(except disconnect/reconnect to the cube) or about the state of this problem
at microsoft (bug or by design), I would be very grateful for a reply.

Thanks,
Bernd Liebermann

Hi Bernd,

A memory leak is certainly one possible explanation of what you're seeing.
If I were you, I would make sure I had the SP3a versions of PTS installed on
your client (its an easy thing to forget to do - and it can sometimes get
overwritten by other installs) - I know there have been some bugs fixed to do
with excessive memory usage. It's also worth explicitly specifying MSOLAP.2
in your connection string.

One other possibility is that this is the client side cache taking the
memory, even though you've used the client cache size property (what values
did you try for that, incidentally?) . Are there any calculated members in
your query? Try putting Cache Ratio=0.01 and Cache Ratio2=0.01 in your
connection string and seeing if that makes any difference.

Finally, rewriting your query or redesigning your cube will probably make a
big difference. First, regarding the query, you're asking for 800*900=720000
members on rows - which is a *lot* of data, and surely more than you need to
display on screen at any one time. Can you break the query up into many
smaller queries, and only provide the data when the user needs it? Also, are
there a lot of invalid combinations here? A NONEMPTYCROSSJOIN instead of a
CROSSJOIN might help reduce the amount of data brought to the client.

It's also worth noting that for a query like this, you're highly unlikely to
be hitting any aggregations designed with the storage design wizard and this
is not going to help matters. If you read the following thread
http://groups.google.co.uk/groups?hl...com%26rnum%3D1
You'll find out how to monitor the aggregations your query is actually using
and how to build aggregations manually.

HTH,

Chris

"Bernd Liebermann" wrote:

Hi Chris,

Thank you very much for the quick reply. I used SELECT NON EMPTY
CrossJoin(...) in my MDX and was unaware of the functional equivalent
NonEmptyCrossJoin(...). This made a really big difference: stunningly, now
memory remains stable on a rasonable level. I tried also "Client Cache Size
= 3" and "Cache Ratio=0.01;Cache Ratio2=0.01" as you suggested, but neither
had any impact.

Thanks again, you helped me a lot.

Best regards,
Bernd Liebermann

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:8E793BE8-735C-4DF7-956C-FC7596F45773 (AT) microsoft (DOT) com...

Our particular memory leak was in AS rather than ADOMD/PTS Bernd. The
following hotfix was prepared for it:
http://support.microsoft.com/default...b;EN-US;838840

Tony Proctor

"Bernd Liebermann" <123 (AT) internova-health (DOT) de> wrote

Note that NONEMPTYCROSSJOIN is not a functional equivalent of NON EMPTY
CROSSJOIN(..) - it has certain limitations that allow it to perform better
that you need to be aware of to do with calculated members. If you have any
calculated members in your query you need to make sure you're getting back
the right data.

"Bernd Liebermann" wrote: