Recursive Documented MDX

The challange:

I was asked by my business analyst to group the end of month results like this:

FUM	Number of clients (CG ID)	Sum of FUM
Up to $100,000
$100,001 - $250,000
$250,001 - $500,000
$500,001 - $1,000,000
$1,000,001 plus

Summarise the value of the portfolio per client and group them according to their values. In a way it means to give a dimension value to the measures. That has to be done, of course, while not losing the granuality of the data.

The data, obviously, looks like this:
Client id, Security id, Security value, date id

(and we insert the data on a monthly basis).
In order to achieve this I've googled, and googled, and googled some more, and reached the following links:
First, there is a term called Recursive MDX, and thanks to Mosha's excellent work you can read about it. Another thanks are due to Richard Lee, who explains when not to use it (hint: there are plenty of date functions in MDX that exist right now, so please learn them if you need to summarise "by month up till now"). I also read his post regarding Group by Measure Range Dynamically and the code below is based on his. The only difference you'll find below is that I've added much needed documentation, as I wasn't even sure about what I was doing (a hint: lots of copy, paste, try & error...)So here's the code, and below please find the documentation:



WITH
MEMBER Measures.FumRange  AS
CASE
    WHEN [Measures].[FUM Value] <= 0 THEN 'Negative'
   WHEN [Measures].[FUM Value] > 0 AND  [Measures].[FUM Value]<=100000 THEN '0-100K'
   WHEN [Measures].[FUM Value] >100000 AND [Measures].[FUM Value] <=250000 THEN '100K-250K'
   WHEN [Measures].[FUM Value]> 250000 AND [Measures].[FUM Value] <= 500000 THEN '250K-500K'
   WHEN [Measures].[FUM Value] > 500000 AND [Measures].[FUM Value] <= 1000000 THEN '500K-1M'
   WHEN [Measures].[FUM Value] > 1000000 THEN 'Over 1M'
END   
SET [OrderedFUM] AS (
    FILTER(   
    ORDER (([Dim Portfolio Client].[Dim Client Group Id].children,
        [Dim Date].[Id].&[20110630]),
        [Measures].[FUM Value], BASC),
        ISEMPTY ([Measures].[FUM Value] )=0)
       
    )   

MEMBER Measures.FumRank AS RANK (([Dim Portfolio Client].[Dim Client Group Id].currentmember,
                                    [Dim Date].[Id].&[20110630]),
                                    [OrderedFum])
                                   
MEMBER MEASURES.RangeTot AS
    IIF((OrderedFUM.item(Measures.FumRank-2), Measures.FumRange)= Measures.FumRange,
             Measures.[FUM Value] + ( Measures.RangeTot ,OrderedFUM.item(Measures.FumRank-2)),
             Measures.[FUM Value])
   
MEMBER MEASURES.RangeCountCG  AS
    IIF((OrderedFUM.item(Measures.FumRank-2), Measures.FumRange)= Measures.FumRange,
            1 + (Measures.RangeCountCG, OrderedFUM.item(Measures.FumRank-2)),
            1)
MEMBER MEASURES.RangeTotal AS
    IIF((OrderedFUM.item(measures.FumRank),Measures.FumRange)=Measures.FumRange,
        NULL,
        Measures.RangeTot),
    FORMAT_STRING = "$#,##0.00;-$#,##0.00"                
   
   
SELECT {[Measures].FumRange, Measures.RangeTotal, Measures.RangeCountCG} ON 0,
NONEMPTY(OrderedFUM, Measures.RangeTotal) ON 1                                   
FROM [xplan DWH]


So, what's going on in here?
(The definitions are from Kevin S. Goff Article: The Baker's Dozen. I recommend reading it as he writes clearly and the information is invaluable)
First of all, we're defining our own measures, and that's why I had to begin with

WITH

keyword, tells that this is what comes below is the list of measures to use.
Second, the measures are:
A list of the values to group by, a "CASE" statement: the same in MDX as it would be in SQL.


MEMBER Measures.FumRange  AS
CASE
    WHEN [Measures].[FUM Value] <= 0 THEN 'Negative'
   WHEN [Measures].[FUM Value] > 0 AND  [Measures].[FUM Value]<=100000 THEN '0-100K'
   WHEN [Measures].[FUM Value] >100000 AND [Measures].[FUM Value] <=250000 THEN '100K-250K'
   WHEN [Measures].[FUM Value]> 250000 AND [Measures].[FUM Value] <= 500000 THEN '250K-500K'
   WHEN [Measures].[FUM Value] > 500000 AND [Measures].[FUM Value] <= 1000000 THEN '500K-1M'
   WHEN [Measures].[FUM Value] > 1000000 THEN 'Over 1M'
END   


Then I need to define the SET. The Set statement 

SET [OrderedFUM] AS (
    FILTER(   
    ORDER (([Dim Portfolio Client].[Dim Client Group Id].children,
        [Dim Date].[Id].&[20110630]),
        [Measures].[FUM Value], BASC),
        ISEMPTY ([Measures].[FUM Value] )=0)
       
    )     

enables me to group all the Sec Value (we use the term FUM for this measure for some reasons) by client id according to the month of June. Not all the clients have FUM value every month, and that's why I FILTER to check if there is value, using the ISEMPTY keyword. I order BASC by the measures and please note that
[Dim Portfolio Client].[Dim Client Group Id].children

is Actually equivalent to

[Dim Portfolio Client].[Dim Client Group Id]
I added it for readability only. 

Now I needed to Rank every member of the set according to their value, using the RANK Keyword.

  MEMBER Measures.FumRank AS RANK (([Dim Portfolio Client].[Dim Client Group Id].currentmember,
                                    [Dim Date].[Id].&[20110630]),
                                    [OrderedFum])


And now the real fun begin.
First of all, I need to summarize all the members of a certain set that falls in a certain category, as defined in the first measure. This is where the recursive MDX is defined:
MEMBER MEASURES.RangeTot AS
    IIF((OrderedFUM.item(Measures.FumRank-2), Measures.FumRange)= Measures.FumRange,
             Measures.[FUM Value] + ( Measures.RangeTot ,OrderedFUM.item(Measures.FumRank-2)),
             Measures.[FUM Value])


Or, in English:
The Measure "RangeTot" gives you for each member the previous [FUM VALUE] in the  measures. The IIF statement says: If the the measure falls within the FUM range, then add it to the previous one (note the recursion defined), if it doesn't fall than this is our stopping point (the false condition).

But if you try to run it you wouldn't get much, you actually need to call this measure using
MEMBER MEASURES.RangeTotal AS
    IIF((OrderedFUM.item(measures.FumRank),Measures.FumRange)=Measures.FumRange,
        NULL,
        Measures.RangeTot),
    FORMAT_STRING = "$#,##0.00;-$#,##0.00"                 


And I've formatted the string to show "currency" as give it much required readability.
The same recursion is required in order to count the number of clients in the client group, and this is done like this:

MEMBER MEASURES.RangeCountCG  AS
    IIF((OrderedFUM.item(Measures.FumRank-2), Measures.FumRange)= Measures.FumRange,
            1 + (Measures.RangeCountCG, OrderedFUM.item(Measures.FumRank-2)),
            1)
Once again, the condition for stopping is for not being in the range, then you just add one, otherwise continue counting.

And now in order to display the data:

SELECT {[Measures].FumRange, Measures.RangeTotal, Measures.RangeCountCG} ON 0,
NONEMPTY(OrderedFUM, Measures.RangeTotal) ON 1                                   
FROM [xplan DWH]


The measures RangeTotal must appear both on Rows and on Columns (0 and 1 Axys), otherwise it isn't summed up. I could have used Measures.RangeTot instead of RangeTotal ON 0, but that wouldn't have been formatter. Of course, there's no need to "format" a recursive expression and yes, this code works and displays the data as it should!

Comments:

1. Write the same code in SQL for a certain month just in order to validate your data; I love the way you can give more power to the business analyst by well defined measures in MDX
2. I'll write in my next post about how to parameterise the month and how to display the data.