Re: URGENT: Is there a way to stop an MDX query?

#1

Quote:

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.

Set Client Cache Size=... connection string property in your ASP.NET app.

Quote:

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

You can try to change it to

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

This should perform better. Another rewrite would be

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

But you need to understand semantics of NonEmptyCrossJoin function to say
whether it will be applicable in your app.

--
==================================================
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.
==================================================
"Stan Kondrat" <stan.kondrat (AT) beyond2020 (DOT) com> wrote

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: URGENT: Is there a way to stop an MDX query?

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: URGENT: Is there a way to stop an MDX query? - 11-06-2003 , 02:42 PM