Ahh, my old friend the Time Intelligence Wizard…. some of calculations it produces didn’t work at all at RTM, some were still buggy in SP1 and now I see from the following threads on the MSDN Forum started by David Beavonn it seems to be generating inefficient MDX:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1285248&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1290367&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1290538&SiteID=1
To be fair, the reasons why the code is inefficient aren’t completely clear but there’s at least one important rule that has emerged and that is if you can hard-code a unique name instead of using the DefaultMember function you should. David says a bit more than just this though, and I’ll try to summarise.
If you do use the wizard to create a year-to-date calculation you’ll get a bit of MDX Script looking something like this:
/*
Begin Time Intelligence script for the [Dim Time].[Hierarchy] hierarchy.
*/
Create Member
CurrentCube.[Dim Time].[Hierarchy Dim Time Calculations].[Year to Date]
As "NA" ;
Scope(
{
[Measures].[Order Quantity],
[Measures].[Sales Amount]
}
) ;
// Year to Date
(
[Dim Time].[Hierarchy Dim Time Calculations].[Year to Date],
[Dim Time].[Calendar Year].[Calendar Year].Members,
[Dim Time].[Dim Time].Members
) =
Aggregate(
{ [Dim Time].[Hierarchy Dim Time Calculations].DefaultMember } *
PeriodsToDate(
[Dim Time].[Hierarchy].[Calendar Year],
[Dim Time].[Hierarchy].CurrentMember
)
) ;
End Scope ;
/*
End Time Intelligence script for the [Dim Time].[Hierarchy] hierarchy.
*/
After various experiments and hints from PSS, he says that you need to make all of the following changes to improve the performance of these calculations (note that I’ve not reproed the poor performance myself – it’s not apparent on AdventureWorks – but he seems to know what he’s talking about so I’ll take his word for it):
- Replace any use of .DefaultMember with a hard-coded unique name
- Replace the use of the * operator with the Crossjoin function. This is interesting: in one of the threads above Mosha mentions that * can either mean crossjoin or scalar multiplication, and in some circumstances what it’s meant to mean is ambiguous; resolving this ambiguity hurts performance. But as David rightly points out, in the MDX above the context surely isn’t ambiguous: the first parameter of Aggregate() always takes a set, we’ve got braces around the reference to the default member and on the right hand side the PeriodsToDate function also always returns a set. So I’m wondering whether it might be safer to always use Crossjoin…?
- Replace the use of the Aggregate function with the Sum function. Mosha rightly points out that you can only do this when all of your measures are additive and is sceptical about whether it makes a significant impact on performance anyway.
As a result, the above section of script should look like this:
/*
Begin Time Intelligence script for the [Dim Time].[Hierarchy] hierarchy.
*/
Create Member
CurrentCube.[Dim Time].[Hierarchy Dim Time Calculations].[Year to Date]
As "NA" ;
Scope(
{
[Measures].[Order Quantity],
[Measures].[Sales Amount]
}
) ;
// Year to Date
(
[Dim Time].[Hierarchy Dim Time Calculations].[Year to Date],
[Dim Time].[Calendar Year].[Calendar Year].Members,
[Dim Time].[Dim Time].Members
) =
Sum(
Crossjoin(
{ [Dim Time].[Hierarchy Dim Time Calculations].&[Current Dim Time] },
PeriodsToDate(
[Dim Time].[Hierarchy].[Calendar Year],
[Dim Time].[Hierarchy].CurrentMember
)
)
) ;
End Scope ;
/*
End Time Intelligence script for the [Dim Time].[Hierarchy] hierarchy.
*/
I’d be interested to hear from anyone else out there who does manage to reproduce a massive improvement in performance after making these changes on their cube.