A few weeks ago I got emailed by my friend and ex-colleague Jon Axon (who is now – quick plug – working for a startup run by another friend of mine,
Data Intelligence, doing cool stuff with pharmaceutical market research data and Analysis Services) highlighting some weird behaviour with calculations generated by the Time Intelligence Wizard. What he did was use the Time Intelligence Wizard to create a time utility hierarchy on the Date dimension in Adventure Works and then add the following, very straightforward previous period growth calculation to it:
CREATE
MEMBER CurrentCube.[Date].[Calendar Date Calculations].[Previous Period] AS
([Date].[Calendar Date Calculations].DefaultMember, [Date].[Calendar].CurrentMember.PrevMember);
He then noticed that while the calculation worked as expected most of the time, some of the time he was unexpectedly getting null values. So, for example, the following query works ok:
select
{([Date].[Calendar Date Calculations].[Previous Period], [Measures].[Internet Sales Amount])}
on 0,
[Date].[Calendar].
members on 1
from
[Adventure Works]
But this time it doesn’t, returning null at the Date level:
with member measures.test as ([Measures].[Internet Sales Amount],[Date].[Calendar Date Calculations].[Previous Period])
select
{[Measures].[Internet Sales Amount], measures.test}
on 0,
[Date].[Calendar].members on 1
from
[Adventure Works]
Now I had no idea what was going on here and neither did he, but he came to the conclusion after reading
Richard Tkachuk’s paper on attribute relationships that there was some attribute overwriting going on here – you don’t have the same problem if you create a separate time utility dimension rather than a new hierarchy on the Date dimension – and he emailed Richard to ask him. Richard explained that the calculated measure in the example above was overwriting the [Calendar Date Calculations] hierarchy so the call to
[Date].[Calendar].CurrentMember.PrevMember was always acting on the All member of [Date].[Calendar] and therefore always returning null, and that in order to get the correct results the tuple used in the calculated measure would have to include [Date].[Date].Currentmember so:
with
member measures.test as
([Measures].[Internet Sales Amount],[Date].[Calendar Date Calculations].[Previous Period],[Date].[Date].
Currentmember)
select
{[Measures].[Internet Sales Amount], measures.test}
on 0,
[Date].[Calendar].
members on 1
from
[Adventure Works]
In fact, I remember Jon and I coming across something similar a while ago and being confused about it then and I’m still not sure that I understand this behaviour properly now in order to use it in future. Although you can work around the problem in this particular situation, as I mentioned, by creating a separate time utility dimension, it seems contrary to everything I’ve ever known about MDX that adding [Dimension].[Hierarchy].Currentmember into a tuple should change the result it returns. And it’s not only me who’s confused: certainly calculations such as Year-to-Dates generated by the Time Intelligence Wizard suffer from the same problem, and I was also recently involved in a thread on the newsgroup where someone was getting quite irate about this topic:
(choice quote: "After working over 20 years with multidimensional analysis reading [Richard’s attribute relationships paper] makes me feel sick").
While I understand that there isn’t a simple solution to this problem (see the thread above for why) and that some complexity had to be introduced with the move to attribute-based dimensions, I still think there must be a better way of handling this. Yesterday I read this very perceptive post on Kathy Sierra’s blog:
…and I felt like I’d fallen into the ‘I suck’ trough in the graph. Microsoft did an awful lot of the things Kathy suggests to make an upgrade successful with AS2005 but I feel like one thing they didn’t do was "Try not to break things that were previously important to [users]" like the simple time series calculations discussed in this blog entry. At least I still have enough faith to believe that it will be fixed sometime in the future…