Ranking - TOP N
We have to create a several TOP N scenarios on a universe built on top to BEx query. Since conditions in BEx query are not supported in Universe we created one in Universe using MDX.
Following is the syntax we used for the MDX TOP 10 customers by sales revenue measue. I don't want to prompt.
<FILTER EXPRESSION="Rank ([0CUST_SALES].CurrentMember, Order([0CUST_SALES].CurrentMember.Level.Members, [Measures].[4FKC4CRQFE71L4O5GOCT4XYK4], BDESC) )"><CONDITION OPERATORCONDITION="LessOrEqual"><CONSTANT CAPTION="10"/></CONDITION></FILTER>
Syntax is ok but when this condition is placed in the query it doesn't bring back the results. Query runs forever and we ended cancelling the process/program on BW. Captured the MDX in BW and executed in MDXTEST. It runs forever too. Volume is not too high. If I remove the condition all the result set comes back in 30 secs. There is something wrong with the MDX being passed to BW,
BW is on 7.0 SP16 and XI 3.1.
Did anyone have success implementing this type of scenario? Any known issues resolved in later SPs? Any alternative solutions?