Top N data in MDX uniserse?

[Former Member]

Hi,

I want to get the top ten of

[Artikel].[Alle Artikel]

based on

[Measures].[Netto Umsatz]

.

I created an object (I named it RankTest ) in the universe and put the following code in the select part:

<EXPRESSION>Rank([Artikel].[Alle Artikel].CurrentMember, 
Order([Artikel].[Alle Artikel].children, [Measures].[Netto Umsatz], 
BDESC))</EXPRESSION>

I create a filter and put the following code in the where part:

<FILTER EXPRESSION="Rank([Artikel].[Alle Artikel].CurrentMember, 
Order([Artikel].[Alle Artikel].children, [Measures].[Netto Umsatz], BDESC))">
<CONDITION OPERATORCONDITION="LessOrEqual">
<CONSTANT CAPTION="10"/></CONDITION></FILTER>

For the object RankTest I always get 0 back, so my ranking did not work.

Do you have any hints?

Thanks for helping

Kind regards

Jens

[Former Member · Accepted solution]

Hi,

I assume that you have selected "Artikel" characteristic in the query otherwise ithe expression won't work.

By the way the syntax is incorrect: currentmember expression works with a dimension or a hierarchy not with a member.

So correct the syntax and use the following expressions:

<EXPRESSION>Rank([Artikel].CurrentMember, 
Order([Artikel].[Alle Artikel].children, [Measures].[Netto Umsatz], 
BDESC))</EXPRESSION>

<FILTER EXPRESSION="Rank([Artikel].CurrentMember, 
Order([Artikel].[Alle Artikel].children, [Measures].[Netto Umsatz], BDESC))">
<CONDITION OPERATORCONDITION="LessOrEqual">
<CONSTANT CAPTION="10"/></CONDITION></FILTER>

Didier

[Former Member · Accepted solution]

Hi Didier,

first thanks for helping!

If I use your syntax correction I get an error back. The message is:

"The Artikel dimension contains more than one hirachies. The hierarchy must therefore be indicated explicitly"

Any Idea?

Kind regards

Jens

Edited by: Jens Hafft on May 10, 2010 5:05 PM

[Former Member · Accepted solution]

Hi,

In SAP BW, the hierarchy technical_name is built like this: characteristic on 30 characters + hierarchy name

So for instance, here is a hierarchy technical_name based on Sales organization characteristic:

[0D_SALE_ORG                   PM_SALES_ORGANIZATION]

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

we are working with MSAS, not with BW.

Kind Regards

Jens

[Former Member · Accepted solution]

Hi Didier,

now I get data back, but the statement runs for 2,5 hours!

In the Select part I use:

<EXPRESSION>
  Rank([Artikel].[Alle Artikel].CurrentMember, 
    Order([Artikel].[Alle Artikel].currentmember.level.members, 
    [Measures].[Netto Umsatz], BDESC)
    )
</EXPRESSION>

In the Filter I use:

<FILTER EXPRESSION=
  "Rank([Artikel].[Alle Artikel].CurrentMember, 
    Order([Artikel].[Alle Artikel].currentmember.level.members, 
    [Measures].[Netto Umsatz], BDESC)
  )"
><CONDITION OPERATORCONDITION=
  "LessOrEqual"><CONSTANT CAPTION="10"/>
</CONDITION></FILTER>

In the query I use the Filter, the KIP and Ast Artikel ID

The generated MDX Code is:

WITH MEMBER [Measures].[65407E0E-3F18-42FA-A8,83,83,E3,DA,30,48,2] AS 
	Rank([Artikel].[Alle Artikel].CurrentMember, 
		Order([Artikel].[Alle Artikel].currentmember.level.members, [Measures].[Netto Umsatz], BDESC))  
	SELECT  { [Measures].[65407E0E-3F18-42FA-A8,83,83,E3,DA,30,48,2] }  ON COLUMNS , 
		NON EMPTY FILTER( ADDCALCULATEDMEMBERS( [Artikel].[Artikel].[AST ARTIKEL ID].MEMBERS ) ,  
		[Measures].[65407E0E-3F18-42FA-A8,83,83,E3,DA,30,48,2] <= 10) 
		DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_CAPTION ON ROWS 
	FROM [VE_UMSATZ]	

If we use a different syntax, we get the answer in a few seconds.

Any hints?

Kind Regards

Jens

[Former Member · Accepted solution]

Hi,

What is the differnt syntax you mention? Can you provide a sample?

In XI 3.x we only support calculated measures whereas in XI 4.0 we will support calculated measures, calculated members and named sets that offer much more

capabilities.

Moreover the MDX generation has completely been redeveloped and improved. Last we do not aither flatten data.

See in attachment documents with MDX usage recomendations for more performance.

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

the generated snytax from Universe is this:

WITH MEMBER [Measures].[65407E0E-3F18-42FA-A8,83,83,E3,DA,30,48,2] AS 
	Rank([Artikel].[Alle Artikel].CurrentMember, 
		Order([Artikel].[Alle Artikel].currentmember.level.members, [Measures].[Netto Umsatz], BDESC))  
	SELECT  { [Measures].[65407E0E-3F18-42FA-A8,83,83,E3,DA,30,48,2] }  ON COLUMNS , 
		NON EMPTY FILTER( ADDCALCULATEDMEMBERS( [Artikel].[Artikel].[AST ARTIKEL ID].MEMBERS ) ,  
		[Measures].[65407E0E-3F18-42FA-A8,83,83,E3,DA,30,48,2] <= 10) 
		DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_CAPTION ON ROWS 
	FROM [VE_UMSATZ]

It take 2,5 hours to run.

The "normal" syntax for MSAS is the following one:

with set [Items] as
order	([Artikel].[Alle Artikel].children,
		([Measures].[Measures].[Netto Umsatz]),
		BDESC
		)
member [measures].[rank] as
rank	([Artikel].[Alle Artikel].currentmember,
		[Items]
		)
select [Items] on 1,
{[Measures].[rank],[Measures].[Netto Umsatz] } on 0
from VE_Umsatz

It takes 6 seconds to run.

So, you can see we have the problem with "rank". The problem is also described in the document you provided. The question now is, what can we do to bring the universe to create the second (performance) syntax?

Kind Regards

Jens

Edited by: Jens Hafft on May 25, 2010 10:49 AM

[Former Member · Accepted solution]

Hi,

As I mentioned in my previous answer we don't support Sets in XI 3.X. We will support Sets in XI 4.0.

As far I can see, you are using a filter in the fist expression to only retrieve the top 10 whereas you don't filter in the second expression and retrieve all members with their rank.

So based on the definition you provided, can you test this expression. Create a calculated measure but don't create a predefined filter:

Rank([Artikel].[Alle Artikel].CurrentMember, Order([Artikel].[Alle Artikel].children, [Measures].[Netto Umsatz], BDESC))

Tell me if it solves the preformance problem.

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

this does not help. It takes more than 3 hours to run. I think the problem is the order within the rank function.

Whats abot the topcount function how can we use this?

Kind regards

Jens

[Former Member · Accepted solution]

Hi Jens,

Topcount is definitely the best solution. Unfortunately this not supported in XI 3.x.

This function can be used in a named set but we don't support them.

I confirm that we will support named sets in XI 4.0 therefore we will support Topcount function.

Regards,

Didier

[Former Member · Accepted solution]

Hi Didier,

thanks for the information. I have seen the topcount function in the universe and I tried it, but it did not work. now I know why...

Do you have any idea how I can solve the ranking problem?

Kind Regards

Jens

[Former Member · Accepted solution]

Hi,

Unfortunately, the only choice we have is to create a calculated member.

Can you try this and see if it improves the performance?

Rank([Artikel].[Alle Artikel].CurrentMember, Order([Artikel].[Alle Artikel].CurrentMember.siblings, [Measures].[Netto Umsatz], BDESC))

Didier

[IngoH]

Hi,

take a look at this article - it has some examples:

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0a45246-ce76-2b10-e688-f5c82062...

Ingo