Create Member
CurrentCube.[Period].[Year-Month Period Calculations].[Twelve Month Moving Average]
As "NA" ;
Scope(
{
[Measures].[Sales]
}
) ;
// Twelve Month Moving Average
(
[Period].[Year-Month Period Calculations].[Twelve Month Moving Average],
[Period].[Month].[Month].Members,
[Period].[Month].Members
) =
Avg(
{
ParallelPeriod(
[Period].[Year-Month].[Month],
11,
[Period].[Year-Month].CurrentMember
) : [Period].[Year-Month].CurrentMember
},
[Period].[Year-Month Period Calculations].DefaultMember
) ;
End Scope ;
I’ve highlighted the problem in bold/red: the [Period].[Month] attribute is mentioned twice in the subcube definition; apparently it happens when the target level of the calculation is also the key attribute of the dimension. The fix is fairly simple, though, and is to remove the second mention of the attribute ie in this case the reference to [Period].[Month].Members. This has been confirmed as a bug and will, I’m promised, be fixed in SP2.
Talking of the Time Intelligence Wzard, will these calculations work as expected in Excel 2007? A couple of recent posts raised some doubts:
http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/54f2ecee7e59d848?hl=en&
>>
Hi NG,
I tested these days Excel 12 with ASY. I realized that the Pivot Table uses sub cubes to filter. This has some bad side effects with some calculated members that aggregate using LASTPERIODS (12) over a time period. When you put the time dim in the filter and you use a calculated measure with LASTPERIODS (12) the 12 time members are filtered out by the sub cube and you just get the member in the filter. In Excel XP the calculated measures work fine because Excel XP uses the WHERE clause to filter and LASTPERIODS (12) returns 12 members. Has anybody an idea if this is a Excel bug? Whats wrong here?
Andreas >>
http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx
>>.. What this really means is that our subselect does not set the current coordinate in the cube (in Moshas words it "merely does top level Exists with axis and applies visual totals"). This is really important to remember if you ever want to use the EXISTING operator for some fancy MDX calculations, because if your client tools use subselects (which, for example, Excel 12 does extensively I am told), you might run into some unexpected results.
>>
I agree with Deepak on this one… This really is a big issue. It really means that the type of time calculations that the BI wizard creates cannot be used with Excel 2007 (and at least one other BI tool that I know of). In my opinion, the best solution would be to make the subcube-statements set the current coordinate of the cube, but I am not sure this is a technically viable solution.
Soooo….with all the headaches in the Time Intelligence portion of SSAS 2005 – could someone provide me with a "good" example of implementing a MTD / YTD calculation that can be used across all measures? I would like to see a code example of using AdventureWorks give me a universal calculated member that can be used across any measure / measure group.
1. Is this possible?
2. Ideas
3. Anyone have the lowdown on Time Intelligence in SSAS 2005 and why it is such a pain?!
Deepak, Chris, and Mosha I look to you!!
Thanks
Dave Harper
It should be possible to write, but it all depends on what behaviour you\’re expecting to see (cf the debate on the older post about whether YTD calcs should work with a \’Month of Year\’ attribute). The latest version of the code generated by the wizard is probably the best candidate for a general calc though, so long as you watch out for the bug mentioned in this post…