Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

An MDX Challenge: Debtor Days

with 9 comments

As I said in my previous post, last night’s event at the Experience Music Project was good fun. There was live music, free booze and of course – and this is probably evidence that I need to be taken away by the men in white coats – conversation naturally turned to the topic of tricky MDX problems. Richard Halliday (I hope I’ve got your name right) came up with an interesting calculation for me which he referred to as ‘debtor days': if I understood correctly, he had a cube with a measure containing values of individual debts incurred by customers and was interested in finding out for any given customer and any given day, the minimum number of days it took from the current date backwards in time for the total of that customer’s debts to reach a given value. After digesting it overnight I had a go at implementing it this morning and found that it was a really fascinating problem – although getting the right value is fairly tricky and worth discussing, there are some great opportunities for optimisation too which I wanted to blog about.
 
First let’s translate the problem into Adventure Works: we’ll use the Date dimension and the Customer dimension, and find out how many days you need to go back from the current date for the current member on the [Customer Geography] user hierarchy for the cumulative total of Internet Sales Amount to exceed 10000. Here’s my sample query with my calculated member:
 

with member measures.daysto10000 as
iif(
count(nonempty(null:{{[Date].[Date].currentmember} as mycurrentdate}.item(0),[Measures].[Internet Sales Amount]) as mynonemptydates)=0,
null,
iif(isempty([Measures].[Internet Sales Amount]) and (not isempty((measures.daysto10000, [Date].[Date].currentmember.prevmember)))
, (measures.daysto10000, [Date].[Date].currentmember.prevmember)+1,
iif(
count({{} as myresults,
generate(
mynonemptydates
, iif(count(myresults)=0,
iif(
sum(subset(mynonemptydates, count(mynonemptydates)-mynonemptydates.currentordinal), [Measures].[Internet Sales Amount]) > 10000
, {mynonemptydates.item(count(mynonemptydates)-mynonemptydates.currentordinal)} as myresults
, myresults)
, myresults
)
)
})=0, null,
count(myresults.item(0): mycurrentdate.item(0))-1
)
)
)

select
descendants
([Date].[Calendar].[Calendar Year].&[2004]
,
[Date].[Calendar].[Date]) on 0,
non empty
descendants
(
[Customer].[Customer Geography].[State-Province].&[HH]&[DE]
,
[Customer].[Customer Geography].[Postal Code])
on 1
from [Adventure Works]
where(measures.daysto10000)

On a cold cache this executes in a touch under 15 seconds on my laptop. The select statement puts all of the days in the year 2004 on columns, all of the postal codes in Hamburg on rows, and slices on the calculated measure defined in the with clause. Here are some things to notice about the calculated measure:

  • The outermost iif simply says that if the set of dates from the start of the Date level to the current date contains no values at all for Internet Sales Amount, then return null. If there are values then the set of dates with values is stored in the named set mynonemptydates, declared inline.
  • The next level of iif represents a recursive calculation, and I found that this was one of the extra touches that made a big difference to performance. It says that if the current date has no value for Internet Sales Amount but the value of the calculated measure is not null for the previous day, then simply add one to the value of the calculated measure from the previous day – this avoids a lot of extra work later on.
  • The next level of iif is where I do the main part of the calculation and this is going to need a lot of explaining… Put simply, I’ve already got from step 1 a set of members representing the dates from the start of time to the current date which have values in and what I want to do is loop through that set from the end backwards doing a cumulative sum, stopping when that sum reaches 10000 and then taking the date I’ve stopped at and finding the number of days from that date to the current date. Originally I attempted the problem like this:

    iif(
    count(
    tail(
    filter(
    mynonemptydates
    , sum(subset(mynonemptydates, mynonemptydates.currentordinal-1), [Measures].[Internet Sales Amount]) > 10000
    )
    ,1) as myresults
    )=0, null,
    count(myresults.item(0): mycurrentdate.item(0))-1

    Here I’m filtering the entire set to get the set of all dates where the sum from the current date to the end of the set is greater than 10000, then getting the last item in that set. This seemed inelegant though – if we had a large set then potentially we’d be doing the expensive sum a lot of times we didn’t need to do it. It seemed better to loop through the set backwards and then somehow be able to stop the loop when I’d reached the first member which fulfilled my filter criteria. But how was this going to be possible in MDX? I didn’t manage it completely, but I did work out a way of stopping doing the expensive calculation as soon as I’d found the member I was looking for. Let’s take a look at the specific section from the main query above:

    iif(
    count({{} as myresults,
    generate(
    mynonemptydates
    ,
    iif(count(myresults)=0,
    iif(
    sum(subset(mynonemptydates, count(mynonemptydates)-mynonemptydates.currentordinal), [Measures].[Internet Sales Amount]) > 10000
    , {mynonemptydates.
    item(count(mynonemptydates)-mynonemptydates.currentordinal)} as myresults
    , myresults)
    , myresults
    )
    )
    })=0,
    null,
    count(myresults.item(0): mycurrentdate.item(0))-1
    )

    What I do first is declare an empty set inline called myresults. I then use the generate function to loop through the set nonemptydates. The first thing you’ll see after the generate is an iif checking if the count of myresults is 0, and the first time we run this check it will be so we need to do our cumulative sum. Because generate loops from the start of a set to the end, and I want to go in the other direction, I get the current ordinal of the iteration and then find the cumulative sum from the item that is that number of members away from the end of the set up to the end of the set. Once I’ve got the cumulative sum I can check if it is greater than 10000; if it is, then I return a set from the iif statement and at the same time overwrite the declaration of myresults with a set of the same name which now contains that one member. As a result, at all subsequent iterations the test count(myresults) returns 1 and I don’t try and do the cumulative sum again. I was quite pleased at finding I could do this – I hadn’t realised it was possible. It only makes about 0.5 seconds difference to the overall query performance though.

  • Finally, on the last line of the calculated measure I can take the member I’ve got in the set myresults and using the range operator find the number of days between it and the current date, which I’ve also stored in a named set called mycurrentdate.

Pretty fun, eh? No, please don’t answer that question. But if you can think of an alternative, better-performing way of solving this problem I would love to hear it…

UPDATE: it turns out that Richard Tkachuk not only had a go at the same problem (although his interpretation of what it is is slightly different) but got just as excited about it as I did, and wrote up his findings here:
http://www.sqlserveranalysisservices.com/OLAPPapers/ReverseRunningSum.htm

Written by Chris Webb

May 12, 2007 at 2:41 am

Posted in MDX

9 Responses

Subscribe to comments with RSS.

  1. I need help on the following MDX query…

    WITH
    Member [Measures].[ValueIfBlank] AS
    \’Head(
    Filter( LastPeriods( 13, [Dim Time].[Year - Month].currentmember ),
    NOT IsEmpty ([measures].[AverageValue])
    ),
    1
    ).Item(0)\’
    Member [Measures].[MemberIfBlank] AS
    \’Head(
    Filter( LastPeriods( 13, [Dim Time].[Year - Month].currentmember ),
    NOT IsEmpty ([measures].[AverageValue])),
    1
    ).Item(0).Name\’

    SELECT
    {
    [measures].[AverageValue]
    ,[Measures].[MemberIfBlank]
    ,[Measures].[ValueIfBlank]
    } on columns,
    {
    [Dim Time].[Year - Month].[Year].&[2007].Children,
    [Dim Time].[Year - Month].[Year].&[2006].Children,
    [Dim Time].[Year - Month].[Year].&[2005].Children
    }
    ON ROWS
    from [Sales History]
    If the same month last year doesn’t have data (If it’s Empty); then find the next month till you reach the next non empty month. Eg, when comparing June 2007 to June 2006, and june 2006 is empty, then find the next month till you find the non-empty month; Could be May, April, March 2006 and moving forward.
    So far I was trying to get the logic work, but then the measure ‘ValueIfBlank’ always returns null.
    I am not sure if this is the right approach and if it is; then may be something is wrong with my Query.

    Any kind of help is appreciated.
     
    Thank you in advance
     
    Manish
     

    Manish N.

    June 6, 2007 at 8:28 pm

  2. Hi Chris,

    Just found your blog and it’s very helpful for someone like me who’s just starting on MDX after spending years on SQL!

    I am tackling the debtor days problem as we speak, however there are two differences in our setup that you could advise on?

    The first is that rather than summing up to an arbritary figure (10,000 in your example), we sum up to another value in the cube, so the business questions is how many days does it take to bill the amount that we currently have outstanding?

    The second thing is in our cube we have a generic [measure].value member, and then a KPI dimension that looks up the required KPI. So in our case we have measure.value (as you have measure.internet sales amount) but I also need to add a slicer in there to only use the measures.value for the given member of the KPI dimension – is this possible?

    Any advise is greatly appreciated.

    Thanks

    Mike Dobing

    Mike Dobing

    September 19, 2011 at 2:14 pm

  3. Hi Chris,

    Thanks for replying so quickly, I did manage to get the first solution working but like you say the performance isn’t the best. I’m looking at the solution referenced above so see if I can improve it.

    I’m struggling at the moment (i’ve learning MDX on the fly after years on SQL) using ClosingPeriod and a semiadditive measure, it’s very painful! Do you recommend any books to help me get up to speed quicker? I’ve got the standard stuff (Professional SSAS 2008 with MDX and others) but I was wondering if there’s an MDX reference book that you would recommend?

    Cheers, again!

    Mike

    Mike Dobing

    September 20, 2011 at 11:50 am

  4. Hi Chris, on the Debtor Days example – have you every encountered the Daily Sales Outstanding challenge? Wherein you count back the amount billed until you reach your current debt amount, giving you basically the number of days required to bill your current debt?

    A good example of this issue is posted here:

    http://social.msdn.microsoft.com/Forums/eu/sqlanalysisservices/thread/b3b4a980-235b-4cb0-942a-d91316af9fa6

    But I don’t think the solution is valid.

    Thanks

    Mike

    Mike Dobing

    October 13, 2011 at 11:56 am

  5. Hi Chris, on the Debtor Days example – have you every encountered the Daily Sales Outstanding challenge? Wherein you count back the amount billed until you reach your current debt amount, giving you basically the number of days required to bill your current debt?

    A good example of this issue is posted here:

    http://social.msdn.microsoft.com/Forums/eu/sqlanalysisservices/thread/b3b4a980-235b-4cb0-942a-d91316af9fa6

    But I don’t think the solution is valid.

    Thanks

    Mike
    (duplicate)

    Mike Dobing

    October 13, 2011 at 11:57 am

  6. Hi Chris,

    Have also worked with IBM MDX query.

    Amit Joshi

    August 8, 2012 at 11:40 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers

%d bloggers like this: