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]
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…
In relation to the orginal point I was exploring here concerning utility hierarchies versus utility dimensions, one point worth emphasising is that a utility hierarchy will always have an unwanted functional dependency on the key attribute of its parent dimension (i.e. since it will always be a member property of the key attribute). As a result, a strong hierarchy exists between the key attribute and the utility hierarchy\’s attribute and hence attribute overwrites come into play, as this example shows, which as far as I can see are never a good thing for a utility hierarchy since it should logically be independent of the parent dimension. Creating a utility dimension instead of a utility hierarchy will not introduce this additional complexity, and for this reason is now – for me – the preferable option in all circumstances.Jon
Chris,
Reading the post through again I think it might help understanding if you\’re a little more explicit about what\’s happening in the second query, when the calculated member is included:
There is an *explicit* overwrite of the [Date].[Calendar Date Calculations] attribute to the [Previous Period] member in the query-scoped calculated measure [Test], which in turn causes an *implicit* overwrite to the All level (this is the non-intuitive part) of all attributes relating to [Calendard Date Calculations] i.e. the key [Date] attribute: [Date].[Date]. So regardless of the co-ordinates on the query axes, the [Date].[Date] attribute is always at the All member when the [Previous Period] calculation is evaluated for a cell in this query in the context of the [Test] measure, which means the expression [Date].[Calendar].CurrentMember is the All member when evaluated at the [Date] level of the user hierarchy, which in turn means that asking for the PrevMember of this returns null hence the query results.
Sorry I\’m in a rush so I hope that\’s correct … it certainly takes a bit of wrapping your head around anyway!
Jon
BTW, there are additional complexities involved in computing Previos Period. I tried to cover some of those in this post, paying special attention to attribute overwrite rules and how to use them to your advantage: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx