MDX Query to find the last ever non empty value in icCube

Question

Following the post from Chris Web I am looking for a fast way to find the last buy from a customer.

I use an MDX statement like the following:

WITH FUNCTION previous_buys() AS tail( nonempty({NULL:[Time].[Time].currentmember.prevmember} ,[measures].[sales amt]),1)
MEMBER [last buy] as previous_buys().(0).key

select [measures].[last buy] on 0
,      [Customers].[Customers].[name].members on 1
from [Store Sales]
where [Time].[Time].[day].&[2015-12-20T00:00:00.000]

This gives as expected, but it is taking a very long time. Is there an easy way to speed this query up somehow. As icCube is somewhat different then the Microsoft MDX I can not just copy Chris Web's solution.

Any idea's?

Answer 1

The main problem we're going to have with this solution is the scalability as we're evaluating {NULL:[Time].[Time].currentmember.prevmember} count members.

I thought that using a Reverse with a Head function would not evaluate the whole set, but the current implementation of the Empty function 'materializes' the set. This means we're evaluating all members. Not yet a valid solution.

Another solution and more elegant is using a recursive function. This will should reduce drastically the number of members evaluated.

WITH 
  FUNCTION previous_buys(t_) AS IIF( (t_,[Measures].[Amount]) = NULL,  previous_buys(t_.prevMember), t_ )
  MEMBER [last buy] as previous_buys( [Time].[Calendar].current).name

SELECT 
  [measures].[last buy] on 0,
  [Customers].[Geography].[Region]  on 1
FROM [Sales]
WHERE [Time].[Calendar].[Year].[2006].[Q1 2006].[Jan 2006].[8 Jan 2006]

If you've a lot of empty dates you could complicate a bit the algorithm going down to a month level for checking emptiness. This will evaluate a whole month in one iteration instead of the 30/31 we'll have in the day version.

---

The last and fastest by an order of magnitude is relying of the aggregation engine of icCube. What we want here is a measure that returns the last existing day.

The idea would be to add a measure with a date as input value and max as aggregation method. Then we would use eval - important as we're caching the subcube - on the set with this new measure.