Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

PrevMember bug

with 5 comments

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…

Written by Chris Webb

April 15, 2005 at 11:09 am

Posted in MDX

5 Responses

Subscribe to comments with RSS.

  1. This bug is really about LAG function (PREVMEMBER is just LAG(1) ), so there are a little bit more real-world scenarios

    Unknown

    April 15, 2005 at 3:44 pm

  2. Can you share some details on how you found the problem? Even if it affects LAG too, I would have thought that 95% of the uses of LAG and PREVMEMBER would be on Time dimensions, which are usually fairly small. But I would be interested to hear about any other common scenario that this bug is relevant to.

    Chris

    April 15, 2005 at 5:04 pm

  3. Yes, it was Time dimension with Date levelCalculations in my scenario used heavily specific date ranges like SUM({[Time].CurrentMember.Lag(28):[Time].CurrentMember})

    Unknown

    April 15, 2005 at 5:17 pm

  4. True, that inside engine PrevMember, NextMember, Lag and Lead – are all the same function. However, the bug doesn\’t always happen – the exact conditions for it are somewhat complex to explain, but Andrew\’s scenario has hit them. Actually perhaps there is even a workaround to ensure that those conditions are not met – I need to look deeper in it.

    Mosha

    April 16, 2005 at 12:27 am

  5. Actually, I\’ve just realised that the last query in this post is wrong – it doesn\’t do what it\’s meant to do. And when you rewrite it correctly, finding the RANK in .LEVEL.MEMBERS rather than .SIBLINGS, the it takes longer than the original. So it probably isn\’t hitting the bug at all. Any further information on when this bug is encountered, Mosha, would be welcome…

    Chris

    April 19, 2005 at 11:11 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers

%d bloggers like this: