with
member measures.daysto10000 asiif(
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].[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))-1Here 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
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
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
Hi Mike,
Well, the simple answer would be that instead of hard-coding the value 10000 you’d instead reference the measure containing the value you wanted to use instead. If you wanted to reference a particular KPI, you’d use a tuple something like (Measures.GenericMeasure, KPI.KPI.&[My KPI]) instead of 1000.
However, this approach doesn’t perform well in general, and I wonder whether you want to do something more like this:
http://cwebbbi.wordpress.com/2011/03/15/events-in-progress-part-3-average-age-of-unpaid-invoices/
Chris
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
Well, if you’re looking for a basic introduction to MDX then I hear “MDX Step by Step” is good. If you’re looking for something more advanced then there’s always “MDX Solutions” (which is a little out of date, but covers some topics that no other book does) or Tomislav Piasevoli’s new book (http://cwebbbi.wordpress.com/2011/08/11/new-mdx-book-mdx-with-microsoft-sql-server-2008-r2-analysis-services-cookbook/)
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
Is it something like this? http://cwebbbi.wordpress.com/2011/03/15/events-in-progress-part-3-average-age-of-unpaid-invoices/
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)
Hi Chris,
Have also worked with IBM MDX query.
Hi Chris,
I’m a regular follower of your blog and believe you can answer my question posted here http://stackoverflow.com/questions/25584574/mdx-calculation-with-excel-multi-select.
Thanks,
Mike.
Hi Mike,
The way to solve this problem would be as follows, I think:
1) Create a calculated measure that returns the count of the number of products selected. This should be fairly straightforward using Mosha’s blog posts as a guide.
2) Make sure you have a measure that returns the number of products associated with each order (this could be a distinct count measure).
3) Create a calculated measure that filters the set of all Sales Orders where the measure in step #2 is the same as the measure in step #1, and then counts the number of items in that set. Mosha’s blog post on optimising count/filter calculations like this will be useful here: http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx
HTH,
Chris
Hi Mike, I’ve done some more thinking about this – it’s a much more difficult problem than it looks but I have a solution. I’ll write a blog post on it next week.