MDX with Excel - for future reference

Let's say you have data model that you want to enquire on. If you're using Power Query (in Excel) you might want to investigate a little further. In order to do that you need to unleash the power of MDX, which is complete and utterly crazy. The problem with MDX (unlike plain Excel functions) is that it's really hard to know what you're doing and there's very little documentation. Nevertheless it's important to read the documentation, and you'll get the hand of it - after a while.

So, how do I find out what was the last date in my cube that actually gave me some data?

My model consist of an SQL database, in which there's a table called dimPeriod, and measures which are stored in a Fact Table. The data is queried from Excel using Stored Procedures, and reported via PowerQuery. Obviously enough, the dimPeriod table includes days from the last couple of years to the next 20+ years. That's enough, and it's great as it warns me if I'm trying to enter the wrong date (yes, I'm using foreign keys to enforce the referential integrity).
Let's just say that [ALL ACP] is the table where there are measures. So I thought of using something like TopCount function to create a set. Unfortunately, it didn't give me the correct results:

=CUBESET("ThisWorkbookDataModel","TopCount([ALL ACP].[Datekey].Children,5)","Max Date Reported")


Explanation:
Cubeset - creates a cube set
"ThisWorkbookDataModel" - the data model that is stored in this workbook. That's true for Excel 2013, and basically, once you type the "Cubeset" function, open bracekts and the double qoute, it comes up with the name of the model.
TopCount - give you the first number
[All Acp] - the table that has measures in it
DateKey - yep, that's the datekey
Children - otherwise you'll get "all"
5 - just a number. I could (or should) have used 1, but 5 gives me a range of numbers to work with.

After inserting that set to a cell in Excel, all I needed was to enquire after that set:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",$G$30,1)


G30 is where the code above is inserted.
A problem: I got the minimum date.
Solution:

=CUBESET("ThisWorkbookDataModel","BottomCount([ALL ACP].[Datekey].Children,5)","Max Date Reported")


Changing from TopCount to BottomCount gave me the latest date to come in. Please note that there are no measures in the query.

You have two options to query on the result, which is in DateKey format.
The first one is to query the relevant data member. Let's say, you're looking for FY Year, you would use the following code:

=CUBEMEMBER("ThisWorkbookDataModel","EXISTS([Dim Period].[FY Month Name].children, [Dim Period].[DateKey].["&G32&"])")

Where G32 is where you result is
Alternatively, just get out the year, the month and the date using:
Year

 =LEFT(G32,4)

Month:

=MID(G32,5,2)

Day:

=RIGHT(G32,2)

Combining the Excel Formulas with the MDX ones gives you lots of power to manipulate the end results.

Adding parameters to my MDX query

 In my last post I've discussed a recursive MDX query. In this post I'll describe how I've parametrized the date for this query.
As you remember, in that query there was a set defined like this:
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)
       
    )     


In real life, of course, I don't want to make a specific date; I want to add the date as a parameter to my MDX query. The requirement is for "sum per client per month" as the data is stored on a monthly basis. After lots of mocking around I've done the following, and I recommend following the same procedure for anyone else who is trying to parametrize an MDX query in SSRS:

1. Create a new report in BIDS.
2. Define a data connection.
3. Copy the MDX query "as is" into the Query Designer (the DataSet part)
4. Define a parameter in the "parameters" tab

In order to achieve that I have to use SSRS: After I've created the Data Source and the Data Set and added the MDX "as is" into the Query Designer, I've created the report and formatted it to my liking. Then I've added the parameter in the "parameters" section. I called it "Month" and made it of data type text.
Now I need to add the "date" in the SET as a parameter, not a constant. Therefor, I had to change the defintion of the measure "OrderFum" set as follows:
SET [OrderedFUM] AS (
    FILTER(   
    ORDER (([Dim Portfolio Client].[Dim Client Group Id].children,
        STRTOMEMBER(@month)),        [Measures].[FUM Value], BASC),
        [Measures].[FUM Value]<>0)
    )

And in order to make the code more readable (=less references to parameters), I changed the other reference to "dim date" in the RANK function from

[Dim Date].[Id].&[20110630]

To


[Dim Date].[Id].currentmember.


And now in the "parameter" box I need to insert the following very clear code
[dim date].[id].&[this is where the day of the month actually gets in - the actual parameter]
For Example:
[dim date].[id].&[20110930]
You would expect that I would use the "@month" parameter differently, and chances are it could be true.While unfortunately there are changes in using StrToMember function between Sql Server 2008 R2 to Sql Server 2005, the StrToMember Functions are quite complicated, as Mosha clearly explains. In order to make this code a bit more usable, I just added [Dim Date].[id].&[20110930] as a default parameter; the beauty is, that if my business analyst wants to play around with this, he just needs to change the date, not the entire text in the text box. Of course it is prone to human error; I, for once, forgot that it is [Dim Date] and not [DimDate].... And yes, I've tried StrToValue but to no avail.
For conclusion: definitely not intuitive, get ready to bang your head in the wall several times during using parametrised MDX with SSRS.

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.

MdxScript(DB) (8, 5) Parser: The syntax for ',' is incorrect

This problem appeared all of a sudden out of the blue in my Cube, while trying to calculate a new member. I usually create a calculated new member using the "Calculate" sub-tab in the toolbar of the "Cube" Tab in SSAS (2008 R2).
The Solution is to go to "Script View" in the toolbar and this is what I saw there:


CREATE MEMBER CURRENTCUBE.[Measures].[Calculated Member]
 AS ,
VISIBLE = 1  ;   




With a red squiggly line under the "AS ,", as it did need a name.
Deleting those messy rows helped, as well as re creating the Calculated Members that I've needed.
Apparently, I was trying to create a new "calculated member" more than once and one of them wasn't edited. As I had quite a few (more than 10) I didn't even notice.
So while using the SSAS MDX features knowing actually how to write MDX is a useful feature even for beginners such as myself.