In following mdx if i use topcount() it gives me result in fraction of
second but when i use bottomcount() it takes several minutes. If anyone
has idea please help

WITH

SET [TimeRange] as '{[Time].[All Year].[1997].[3].[7]:[Time].[All
Year].[2000].[1].[3] }'

select { [Measures].[vami], [Measures].[prevpeak],
[Measures].[drawdown]
} on columns,
{bottomcount([timerange],5, [Measures].[drawdown]) } on rows
from [MonthlyPartnerData]
where ( [Fund].[All Fund Id].[2])

Thanks,
Sanjeev

Hi Sanjeev,


Just wondering whether some/all of the measures in the query are
calculated, which could affect query response times. And is this
simplified version equally slow?

SET [TimeRange] as '{[Time].[All Year].[1997].[3].[7]:[Time].[All
Year].[2000].[1].[3]}'

select {[Measures].[drawdown]} on columns,
{bottomcount([timerange],5, [Measures].[drawdown])} on rows
from [MonthlyPartnerData]
where ([Fund].[All Fund Id].[2])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

this works more better with topcount but with bottomcount same result.

[Measures].[vami], [Measures].[prevpeak],
[Measures].[drawdown] all are calculated member using with. [drawdown]
is dependent on [vami] and [prevpeak].

In that case, can you give an idea of the logic behind these calculated
measures (in particular, are they, in turn, dependent on the Time
dimension)? And is the Time dimension regular or parent-child?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

here they are , they are dependent on time dimention,
time dimention contains {year,quarter,month}

MEMBER [Measures].[vami] as 'IIF ( [time].currentmember IS
[timerange].Item(0),
1000 + (1000 *( [Measures].[MTD ROR]))
,[time].prevmember + ([time].prevmember *( [Measures]. [MTD ROR] )))'

MEMBER [Measures].[prevpeak] as ' iif([time].currentmember IS
[timerange].Item(0),1000, iif([Measures].[vami] >[time].prevmember
,[Measures].[vami], [time].prevmember) )'

[Measures].[MTD ROR] is a simple measure.