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?
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.