buckets or quartile

Hi all,

Dreaded moment has come:)

I need to produce a report against MSAS 2005 with quartiles buckets, meaning that besides actual value of the measure, it needs to display following:

Value 1st 2nd 3rd 4th
X Min - Max Min - Max Min - Max Min - Max
-
80 85 - 95 75 - 85 55 - 75 12 - 55

where 1st is range of values of top 25% performing entities and so forth. It's supposed to be sliceable by any hierarchy in the cube.

I tried to use TopPercent 25%, but never could get it work in calculated member.

Any idea, comment, reference highly appreciated.
Thanks

You might consider using the Excel.Quartile function that comes with the ExcelMDX library in SSAS.|||

An alternative is to create a "band dimension" and set the bandID in the fact table prior to loading. This will give you greater control if you are planning to use this type of banding in many queries.

eg. Band dimension structure

BandId StartBand EndBand Description

1 -99999 11 Less than 12
2 12 55 12 to 55
...
n 85 95 85 to 95
n+1 96 9999999 greater than 95

|||

Can you use the Descritization properties on the attribute? Set it for 4 buckets, and change the label properties to have it display what you need.

-Kory

|||

Let's clarify the problem a bit.

Suppose Value is Sales, which aggregates as Sum. Suppose for tuple (Geography.USA.Redmond, Time.Year.2005) the value of Sales is 80. How the value of 1st would be calculated? It is stated as "top 25% performing entities". What is "entity" and how the "performance" is calculated?

Suppose Geography dimension has the key attribute - Employee, which is the granularity attribute. Are employees entities?

Suppose in 2005 the employees of Redmond showed sales as: Emp1=10, Emp2=20, Emp3=30, Emp4=20. Total is 80.

How do we calculate 1st value?

|||

Extendong your example with employees in particular geography top 25% performing employees (Emp1=10, Emp2=20, Emp3=30, Emp4=26, Emp5=15, Emp6 = 22, Emp7=35, Emp8=14) will be Emp3 & Emp7. The report will look like this:

Redmond 30 37

22 26

15 20

10 14

Employees are entities and performance is highest value of the sale in particular geography.

Thank you.

|||

is it some kind of Add-on for AS? How it can be installed and used?

Thank you

|||

It is kind of strange that with your last example the numbers for the employees you provided did not sum to 80, like you showed with your very first example. Along with the 1stValue - 85-95 being more than Value - 80 in the first example. I am thinking about the additional rules not yet explained.

Let's move up in the Geography hierarchy a bit. Consider tuple (Geography.USA, Time.Year.2005) (let's not have states, since in my first example i showed Geography.USA.Redmond). Will you need calculation of those 4 values for this tuple also? Will it be something like scanning aggregated sales in cities, bucketizing them and reporting?

What i am trying to get clarified is - do you need just one static report (is "report" an MDX query or report for MS Reporting Services?), where you have predefined 2 levels of the same hierarchy, one measure and for each member of the first hierarchy we calculate buckets from the values of the measure when the member is drilled down to the second level?

|||

the measure is not additive.

The report will calculate quartiles for the cities only.

For some reason, TopPercent did not work properly, so I could not create calculated members or named set. So far I have calculated quartile values for each city and store them in a cube as member properties.

We are using ReportPortal on SPS, because there are many other hierarchies, which users might want filter or multi-select, so dynamic reporting is preferred.

I would like to thank you all for help and suggestions. Strange, but TopPercent still does not work. May be MSFT folks can shed some light on its usage.

Thank you very much again

|||You can use it in SSAS if you have Excel installed on the server.