A little MDX problem

Hi,

I need some support for a MDX problem.

We have a cube with a measure, are region (with hierachy, but this doesn't matter at all) and two time dimensions (with the usual hierachies). The business problem is now:

You select a date (on any level) from Time1. If you would place the region on rows and Time2 on colums (the days) with nonempty enabled, you would get a number of members back (about 1000 numbers in Time2 per day in Time1). I don't what to see that 1000 numbers, I need a measure which is calulated like this:

- Sort these numbers by value

- Find the number which is as position 99%, so i.e. if you have 1000 numbers (but this number can change), you need the 990th member. So you have to count the members, multiply by .99 and you have the ordinal of the member you need

So: how to do this in MDX? I'm quite struggeling around with counts and sorts, the 1000 are returned quite quickly but when I start to sort them it's getting veeeerrrrryyyy slow...

I would be very happy if someone can help me with that...

Hi Thomas,

There are some problem parameters that I'm not sure of, so I'll assume that:

- Ordering is along a pre-determined level of a hierarchy (not based on query axes)

- Ordering is by a pre-determined measure (again, not dynamically determined)

- Desired position is 99th percentile ascending (ie. 1st percentile descending)

Based on these assumptions, here is an Adventure Works query which returns the [Date] name, value and ordinal of the 99th percentile [Order Count], by Product Category on rows:

>>

With

Member [Measures].[Orders99thMember] as

MemberToStr(Tail(TopCount(NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}) as DS,

Int(DS.Count/100)+1, [Measures].[Order Quantity])).Item(0).Item(0))

Member [Measures].[Orders99thName] as

StrToMember([Measures].[Orders99thMember]).Name

Member [Measures].[Orders99thValue] as

(StrToMember([Measures].[Orders99thMember]),

[Measures].[Order Quantity])

Member [Measures].[Orders99thOrdinal] as

CInt(99 * (NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}).Count)/ 100)

select {[Measures].[Order Quantity],

[Measures].[Orders99thName], [Measures].[Orders99thValue],

[Measures].[Orders99thOrdinal]} on 0,

NonEmpty([Product].[Product Categories].[Category].Members,

{[Measures].[Order Quantity]}) on 1

from [Adventure Works]

Order Qty Orders99thName Orders99thValue Orders99thOrdinal
Accessories 61,931 November 1, 2003 1663 417
Bikes 90,220 February 1, 2003 2654 1082
Clothing 73,598 July 1, 2003 3200 417
Components 49,027 September 1, 2003 4365 38

>>

|||

Deepak,

thanks for you excellent support... But one question: Where do you do the "sort" by order quantity? I do only see that you return the 99th procentile of the set... I don't think your assuptions will help since you can't define that a set is always sorted by a measure... I understand your second assumption that you mean that it's always the order quatity and not sometimes the quantity and sometimes the amount...

Thanks,

|||Sorry, I guess the topcount does the job... Thanks, it's not very fast but it does the job...