Some Time Intelligence Wizard-generated calculations don’t work

MDX 3 Minutes
This is an issue I came across a while ago during beta testing, but now I know it is present in the RTM version and because I know this is going to be a major cause for confusion, I thought I’d blog about it. Put simply, the MDX for certain calculations generated by the Time Intelligence Wizard in BIDS doesn’t work. For example, try using the wizard to add a Year to Date calculation to the [Date].[Calendar] user hierarchy in the Adventure Works cube. You’ll end up with a bit of MDX Script like this:
 

/*
Begin Time Intelligence script for the [Date].[Calendar] hierarchy.
*/

Create Member CurrentCube.[Date].[Calendar Date Calculations].
[Year to Date] AS "NA" ;

( [Date].[Calendar Date Calculations].[Year to Date],
{
[Measures].[Internet Sales Amount]
},
[Date].[Calendar Year].[Calendar Year].

Members ) =
Aggregate(
{ [Date].[Calendar Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
)
) ;
/*
End Time Intelligence script for the [Date].[Calendar] hierarchy.

*/

 
When you try to run any query on this user hierarchy (or indeed on the constituent attribute hierarchies) you’ll find that you only see Year to Date values for the Year level – which isn’t exactly useful. Here’s a query which illustrates this:
 

SELECT

[Measures].[Internet Sales Amount]
*
[Date].[Calendar Date Calculations].[Calendar Date Calculations].ALLMEMBERS
ON 0,
[Date].[Calendar].MEMBERS
ON 1
FROM [Adventure Works]
 
So, how can we fix this? It depends on how complicated your own Date dimension is – luckily/unluckily, the Date dimension in Adventure Works is more complicated than average. For me, any solution would have to meet the following criteria:
  • It should work on the [Date].[Calendar] user hierarchy.
  • It should work on any meaningful combination of attribute hierarchies. So, in Adventure Works, this should mean you should not see values when you are looking at the [Date].[Semester of Year] hierarchy on its own, but if you have crossjoined [Date].[Year] with [Date].[Semester of Year] then it does make sense to see YTD values.
  • It should somehow work around the mysterious ‘internal error’ messages that started appearing in OWC – but not in my own MDX in SSMS – when I started testing my code.

After an awful lot of head-scratching I came up with the following scriptlet which is the only way I could see to meet the first two criteria properly (I tried to find a simpler approach, believe me) and minimised the appearance of the bug in the third bullet:

/*
Begin Time Intelligence script for the [Date].[Calendar] hierarchy.
*/

Create Member CurrentCube.[Date].[Calendar Date Calculations].[Year to Date] AS "NA" ;
( [Date].[Calendar Date Calculations].[Year to Date],
{
[Measures].[Internet Sales Amount]
}
,
[Date].[Date].[Date].Members
) =
Aggregate(
{ [Date].[Calendar Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
)
) ;

SCOPE([Date].[Calendar Date Calculations].[Year to Date], [Measures].[Internet Sales Amount]);
SCOPE([Date].[Calendar Year].[Calendar Year].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
SCOPE([Date].[Calendar Semester].[Calendar Semester].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
SCOPE([Date].[Calendar Quarter].[Calendar Quarter].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
SCOPE([Date].[Month Name].[Month Name].Members);
THIS=( [Date].[Calendar Date Calculations].[Year to Date],TAIL(EXISTING([Date].[Date].[Date].Members),1).ITEM(0).ITEM(0));
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
/*
End Time Intelligence script for the [Date].[Calendar] hierarchy.
*/

 
Basically, what the first assignment does is perform the YTD calculation at the [Date].[Date].[Date] level only; next, the series of nested SCOPEs returns the YTD value for the last existing member on the [Date].[Date].[Date] level for other meaningful granularities, carefully excluding the combinations of dimensions which don’t make sense. Mosha, if you’re reading, is there a better way to do this?

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

Published

10 thoughts on “Some Time Intelligence Wizard-generated calculations don’t work”

  1. Chris – I think you overcomplicated it. I haven\’t played with it too much, but it seems to me that the following should work just fine for you:Scope( { [Measures].[Internet Sales Amount] }); ( [Date].[Calendar Date Calculations].[Year to Date], [Date].[Calendar Year].[Calendar Year].Members, [Date].[Date].Members ) = Aggregate( { [Date].[Calendar Date Calculations].DefaultMember } * PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember ) ) ;End Scope ;

    Reply

  2. Unfortunately that doesn\’t meet the second of the criteria I listed – that the calculation should work for meaningful combinations of attributes. Take the following query:SELECT{[Measures].[Internet Sales Amount]*[Date].[Calendar Date Calculations].[Calendar Date Calculations].ALLMEMBERS}ON 0,[Date].[Calendar Year].members*[Date].[Calendar Quarter of Year].membersON 1FROM [Adventure Works]Given that the [Calendar Quarter] attribute is hidden and the [Calendar Quarter of Year] isn\’t, I think it\’s reasonable to assume that at some point a user is going to want to run a query like this one (even with the user hierarchy visible). With my calculation I get the correct YTD values, but with yours I see the same values for YTD as for the [Current Date] member. However, that said, I\’m not sure that all the levels of nesting are absolutely necessary… I\’ll need to do some more testing.

    Reply

  3. Well, I disagree with you here – I beleive the second criteria is meaningfully satisfied. Calendar Year and Calendar Quarter of the Year are truly unrelated attributes, and therefore it isn\’t reasonable to get something like running sum over it. Of course you can try to fake it, like you did in your example – but it goes against the model, is unreliable (i.e. it still can be easily broken) and will suffer from performance problem. Not to mention that this solution won\’t work as soon as you move from YTD to something like QTD, where you may have partial attribute overlaps w.r.t. Exists – in fact this can happen even with YTD – if weeks don\’t fit nicely into years.

    Reply

  4. Mosha,Try telling a user that Calendar Year and Calendar Quarter are "unrelated" and they will laugh in your face. Just because they\’re not physically constrained by a member property in your AS model doesn\’t mean that the corresponding domain entities are conceptually unrelated – they are clearly not unrelated. Presence of a one-to-many relationship is not a reasonable criteria for judging "related" attributes from the *users\’s* perspective (I think it\’s safe to assume that we, as BI developers, appreciate the relevant AS implementation constraints and concerns, and that AS cannot – and should not attempt to – infer semantic relationships other than those explicitly indicated). Chris is simply trying to come up with an innovative solution to a real world problem here that is not addressed out of the box – something I would have hoped you would encourage, rather than simply declaring it unreasonable.Jon

    Reply

  5. Well, I agree with you on one thing – this solution is not optimal from a performance point of view. In fact, if this was my cube, I\’d try to remodel it so I could use the much better PeriodsToDate approach which is certainly going to perform better.However we\’ll have to agree to disagree about whether [Calendar Year] and [Calendar Quarter of Year] are unrelated attributes. You and I can look at the dimension design and see that yes, strictly speaking they are unrelated, but from a non-technical user\’s point of view it\’s obvious that they are. People are going to want to model Time dimensions in this way to get the ability to put Years and Quarters on opposing axes in queries, but they\’re also going to expect that Year to Date calculations will still work too. And as I said, I don\’t think this is an unreasonable expectation.I\’m not trying to pass this solution off as anything other than a way of making YTDs work with the AdventureWorks Date dimension; it\’s certainly not a generic solution and different dimensions will need different approaches. So, regarding the problem of attribute overlap, if you were looking at weeks without a year in context then I think this would be a valid case for either returning NA or implementing a specifically-formulated business rule to cover this case (eg when week overlaps a year, a YTD value for a week should be calculated based on the year of the first day of the week). All I\’m saying is that it\’s a tricky problem and one which needs a bit of thought when implementing a solution.

    Reply

  6. Chris & Jon – I think both of you really missed the point here. When I said that these attributes are not related – it wasn\’t some technicality that someone didn\’t define member property or such. "Calendar Quarter of the Year" is _inheritely_ unrelated to Years. Indeed, no matter what your time dimension is, there always will be only 4 quarters in a year – I think we all should agree on that. Doing running sum calculations on something which always has cardinality 4 is meaningless and unreasonable. And obviously, the Calendar Quarter attribute, to which Year is related will behave just fine with my version of calculation. I understand what you are saying about user requirements, and I will be the first to say that user is always right. And trust me – I work enough with users. But the right approach to answer user requirements is to build a correct model instead of building wrong one and bending the rules – from all my experience I can tell that the later approach is fragile doomed for failure. Our role as BI developers is to take user requirements and build a right model for them.

    Reply

  8. I can understand the "Create Member …Scope…".I just dout that is there any tools to execute that script? If there is ,please introduce some or offer a link, thanks!

    Reply

Leave a ReplyCancel reply