Top N Material By Brand

[former_member215191]

HI,

i need to get Top 6 Material by Brand to xcelsius via QAWS, here is the filter condition i am applying in the universe, the query runs longer time and returns no rows.

<FILTER EXPRESSION="Rank([0MATERIAL].CurrentMember, Order([0MATERIAL].CurrentMember.Level.Members,[Measures].[4KWCWHG0HZMUH44RJSRYY406T], BDESC))"><CONDITION OPERATORCONDITION="LessOrEqual">

<CONSTANT CAPTION="6"/></CONDITION></FILTER>

Edited by: RAMADIVYA on Feb 19, 2011 2:18 AM

[Former Member · Accepted solution]

Can you write the whole query in SQL please ?

[former_member215191 · Accepted solution]

WITH MEMBER [Measures].[F61FAF40-5B67-47C6-BE,32,2F,D5,12,34,23,1F] AS

' Rank([0MATERIAL].CurrentMember,

Order([0MATERIAL].CurrentMember.Level.Members,

[Measures].[4KLSS32LOSSARVMNE5NCQ46IC], BDESC))

' SELECT { [Measures].[4KLSS32LOSSARVMNE5NCQ46IC] } ON COLUMNS ,

NON EMPTY FILTER( CROSSJOIN( [ZLEADBRND].[LEVEL01].MEMBERS,

[0MATERIAL].[LEVEL01].MEMBERS ),

[Measures].[F61FAF40-5B67-47C6-BE,32,2F,D5,12,34,23,1F] <= 5)

DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM

[ZGOPAM50/ZGOZGOPAM50_Q011_SQ_MB_TOP_20]

but when i replace 0MATERIAL with other charestric like country, it is working find, but with 0material no nows are being returned and taking very long time.

Edited by: RAMADIVYA on Mar 7, 2011 2:19 PM

[MariannevL · Accepted solution]

Did you do a query without the rank filter to see how long it takes to calculate?

What the calculated values are for each member (any weird values there)?

And how many rows it retrieves then...

May be the 0material dimension is very big or you are missing indices to help speed up the calculation.

Sounds like you did everything right on the XML side, but the performance is a problem.

Ask if the BW people can look into that.

Good luck,

Marianne