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.

Answer 2

This is relatively quick using SSAS against AdvWrks. I amalgamated you two custom structures (& needed to change from FUNCTION as I don't think this is part of MS's implementation of mdx):

WITH 
  MEMBER [Measures].[previous_buys] AS 
    Tail
    (
      NonEmpty
      (
        {NULL : [Date].[Calendar].CurrentMember.PrevMember}
       ,[Measures].[Internet Sales Amount]
      )
    ).Item(0).Item(0).Member_Key 
SELECT 
  NON EMPTY 
    [Measures].[previous_buys] ON 0
 ,NON EMPTY 
    [Product].[Product Categories].[Product] ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].&[20071015];

It results in the following: