PrevMember bug

An interesting bug came up on the newsgroup this week, concerning the PREVMEMBER function. You can read the original thread here, which includes Mosha’s explanation of what the problem is exactly (full credit to him for taking the time to investigate this and posting back to the ng to share the details – he deserves a medal for his community involvement).

I wonder how many cubes out there have calculated members which are suffering from poor performance as a result of this? Not many, I guess. The only scenario I could think of when this bug might be a problem would be if you had a previous period growth calc, a reasonably large time dimension (probably going down to day or hour level and consisting of several years) and a query with a lot of tuples on rows or columns. But if you do think you are experiencing this problem, how can it be worked around? Well, knowing that PREVMEMBER is the problem, I rewrote the query in the thread to avoid it and used a combination of RANK and ITEM instead:

with
member measures.myrank as ‘rank([Customers].[Name].[Amy Petranoff], [Customers].[Name].[Amy Petranoff].PARENT.CHILDREN)-2’
member [Customers].[CM] as ‘[Customers].[Name].[Amy Petranoff].PARENT.CHILDREN.ITEM(MEASURES.MYRANK)’
SELECT { [Customers].[CM] } ON COLUMNS ,
NON EMPTY  {   { [Store].[Store Name].Members  } * {
[Product].[Product Name].Members  } }   ON ROWS
FROM [Sales]
where ([Measures].[Unit Sales]) 

This performs much better than the PREVMEMBER version (only 4 seconds rather than 75), although it doesn’t look very elegant. But what about a more realistic scenario? Going back to the previous period growth example, consider the following query in Foodmart:

WITH MEMBER MEASURES.[PREVMONTH SALES] AS ‘MEASURES.[UNIT SALES] – (MEASURES.[UNIT SALES], TIME.CURRENTMEMBER.PREVMEMBER)’
SELECT { MEASURES.[UNIT SALES], MEASURES.[PREVMONTH SALES] } ON COLUMNS ,
NON EMPTY
CROSSJOIN( 
CROSSJOIN([Store].[Store Name].Members ,[Product].[Product Name].Members)
,{[Time].[1997].[Q1].CHILDREN,[Time].[1997].[Q2].CHILDREN })
ON ROWS
FROM [Sales]

This to me seems like a very common query: find the current month’s sales and the growth from the previous month. I therefore rewrote the query to use RANK and ITEM instead of PREVMEMBER, so that it looks like this:

WITH
MEMBER MEASURES.PREVTIME AS ‘(MEASURES.[UNIT SALES], TIME.CURRENTMEMBER.PARENT.CHILDREN.ITEM(RANK(TIME.CURRENTMEMBER, TIME.CURRENTMEMBER.SIBLINGS)-2))’
MEMBER MEASURES.[PREVMONTH SALES] AS ‘MEASURES.[UNIT SALES] – MEASURES.PREVTIME’
SELECT { MEASURES.[UNIT SALES], MEASURES.[PREVMONTH SALES] } ON COLUMNS ,
NON EMPTY
CROSSJOIN( 
CROSSJOIN([Store].[Store Name].Members ,[Product].[Product Name].Members)
,{[Time].[1997].[Q1].CHILDREN,[Time].[1997].[Q2].CHILDREN })
ON ROWS
FROM [Sales]

Running both these queries on my laptop, from a cold cache, the first executes in 73 seconds and the second executes in 65 seconds. Not an enormous difference, then, but it might be more significant on a larger time dimension or a much larger query. I don’t think I’ll be changing the way I write any of my standard MDX calculations as a result…