MDX query to find the last unused value in icCube

Question

Following a post from Chris Web, I’m looking for a quick way to find the latest purchase from a customer.

I use the MDX instruction as follows:

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 takes a very long time. Is there an easy way to speed up this request. Since icCube is slightly different from Microsoft MDX, I cannot just copy the Chris Web solution.

Any idea?

Answer 1

The main problem we encountered with this solution is scalability as we evaluate {NULL: [Time]. [Time] .currentmember.prevmember} count members.

I thought that using the Reverse with Head function would not evaluate the entire set, but the current implementation of an empty function “materializes” the set. This means that we value all participants. Not a valid solution yet.

Another solution and more elegant is to use a recursive function. This should drastically reduce 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 have a lot of empty dates, you can complicate the algorithm a little, which went down to the level of the month to check the void. This will be evaluated for a whole month at one iteration instead of 30/31, which we will have in the daily version.

---

The last and fastest one relies on the icCube aggregation mechanism. 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 an input value and max as an aggregation method. Then we will use eval - important as we cache the subcube - on the set with this new measure.

Answer 2

This is relatively fast using SSAS vs AdvWrks . I combined you with two custom structures (& need to change with FUNCTION , since I don't think this is part of MS mdx implementation):

 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]; 

This leads to the following: