Hi,
I have a very slow running MDX query (see below) Everything runs great
until I come to this one which needs to jump over a level within the
dimReferrerSearch dim as the leafe data has over 64k members. I use the
Descendants function to achive this.

The object of the query is to return the top X leafe members based on a
messure.

Does anyone know why this query is running slow ?

Kev



WITH SET [Date Range] As ' { [dimISODay].[All dimISODay].[1 Aug
2003]:[dimISODay].[All dimISODay].[28 Aug 2003] } ' MEMBER
[dimISODay].[Range] As ' Aggregate ( { [Date Range] } ) ' SELECT Non Empty
{ [Measures].[First Time Visits], [Measures].[Repeat Visits],
[Measures].[Total Visits], [Measures].[Unique Visit] } ON COLUMNS, Non Empty
{ { SUBSET ( { Order ( { Descendants ( [dimReferrerSearch].[All
dimReferrerSearch].[Search Engines].[Google].[google.com].Children ,
[dimReferrerSearch].[Search Phrase], LEAVES ) },[Measures].[Total
Visits],BDESC ) }, 0, 49 ) } } ON ROWS FROM vcubVisit WHERE (
[dimCampaignID].[All dimCampaignID].[200047], [dimCountry].[All dimCountry],
[dimISODay].[Range] )

How many members of [dimReferrerSearch] are returned as Descendants()
and then ordered? A lot of memory may be consumed by a large set, and
divide-and-conquer using Generate() can greatly reduce the memory
needed. For example, based on the Microsoft OLAP Solutions book p.333,
the query could be recast like this, using TopCount():

2003]:[dimISODay].[All dimISODay].[28 Aug 2003] } ' MEMBER
[dimISODay].[Range] As ' Aggregate ( { [Date Range] } ) ' SELECT Non
Empty
{ [Measures].[First Time Visits], [Measures].[Repeat Visits],
[Measures].[Total Visits], [Measures].[Unique Visit] } ON COLUMNS, Non
Empty
TopCount(Generate([dimReferrerSearch].[All
dimReferrerSearch].[Search Engines].[Google].[google.com].Children,
TopCount(Descendants([dimReferrerSearch].CurrentMember,
[dimReferrerSearch].[Search Phrase],LEAVES),[Measures].[Total
Visits],50)),[Measures].[Total Visits],50) ON ROWS FROM vcubVisit WHERE
(
[dimCampaignID].[All dimCampaignID].[200047], [dimCountry].[All
dimCountry],
[dimISODay].[Range] )
The other thing to consider is whether the set is sparse over the given
slicer, in which case it could be greatly reduced in size by
NonEmptyCrossJoin() prior to TopCount().


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!