Sql-server – MDX Query for RANK regardless of hierarchy

mdxsql-server-2012ssas

I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the groupers in advance.

For example, say I have a calculation already for Efficiency which is defined as $$/hour.

I would like to add an Efficiency Rank calculation as well that will tell me what relative position the current member has in it's set.

The Syntax I tried was

RANK([Measures].[Efficiency].CurrentMember, [Measures].[Efficiency])

Which I realize is incorrect.

I can't seem to locate a guide to this anywhere else, so I'm hoping there is some established best practice or pattern for this since it seems like it would be a relatively common business requirement.

Best Answer

Please explain in more detail what you meany by "groupers"?

For instance, if you want to rank a member within a set, you need to tell SSAS what the set is that you are ranking within.

RANK( Tuple, Set, Measure to rank by)

RANK( Machines.Machines.CurrentMember, Machines.Machines.AllMembers, Measures.Efficiency)

Now, I think that what you are trying to do is have that all members be dynamic?

The question is "How would you both specify the current member and the set?"

And here's a kicker: the frontend tool you're using will make a difference here as well

Related Solutions

MDX calculated member to band a measure

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY 
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.

SSAS / MDX Query – Need row total

Try adding the All member into the set on rows:

SELECT 
  NON EMPTY 
    {[Measures].[Order Count]} ON 0
 ,NON EMPTY 
    {
        [Date].[Year].[Year].ALLMEMBERS*
        [Date].[Quarter].[Quarter].ALLMEMBERS*
        {
          [Order].[Customer].[Customer].ALLMEMBERS
         ,[Order].[Customer].[All]
        }
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON 1
FROM [Order]
CELL PROPERTIES VALUE

Best Answer

Related Solutions

MDX calculated member to band a measure

SSAS / MDX Query – Need row total

Related Question