Hi,

I have an ASP.NET application which uses ADOMD to
communicate with Analysis Services cubes located on the
same server. When the application submits an MDX query
like the one below an ASP.NET process recycles itself
(after reaching 60% of available RAM) and users get
application errors.

When I run the same query using MDX Sample Application
that comes with Analysis Services I see that the memory
usage grows continuously until all memory is finally used
up.

Is there a way to configure Analysis Services not to
allocate memory beyond certain limits? Setting Memory
Conservation Threshold in Analysis Manager does not solve
the problem.

Is there a way to make a query timeout gracefully after a
specified period?

I do have MS/AS Service Pack 3 installed.

Here is an example of a problematic query (using the
standard Foodmart 2000 database):

WITH
SET [RowSet] AS
'{[Product].AllMembers} * {[Customers].AllMembers}'
SET [ColumnSet] AS '{[Measures].[Store Sales]}'
Select
Subset(
Order([RowSet],[ColumnSet].Item(0),BDESC),
0, 100) ON ROWS,
[ColumnSet] ON COLUMNS
FROM [Sales]

Any advice will be appreciated.

/Stan

Another suggestion to rewrite the query is

WITH
SET [RowSet] AS
'{[Product].AllMembers} * {[Customers].AllMembers}'
SET [ColumnSet] AS '{[Measures].[Store Sales]}'
Select
Subset(
TopCount([RowSet],100,[ColumnSet].Item(0)) ON ROWS,
[ColumnSet] ON COLUMNS
FROM [Sales]

And, of course, as Brian said, depending on the cube, you could also use

WITH
SET [RowSet] AS
'NonEmptyCrossJoin({[Product].AllMembers} , {[Customers].AllMembers})'
SET [ColumnSet] AS '{[Measures].[Store Sales]}'
Select
Subset(
TopCount([RowSet],100,[ColumnSet].Item(0)) ON ROWS,
[ColumnSet] ON COLUMNS
FROM [Sales]

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================