Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Some Time Intelligence Wizard Calculations are Inefficient

with one comment

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.

Written by Chris Webb

March 7, 2007 at 9:27 pm

Posted in Uncategorized

One Response

Subscribe to comments with RSS.

  1. We tried this and find it slower than the code generated by the BI Wizard.  And we got into a really strange issue: with the code generated by the wizard we can add calculated members to the set.  If we change the scope to the top as suggested, we get an error: calculated members are not allowed in the set; it works fine with the wizard code.

    Unknown

    April 4, 2007 at 10:09 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,857 other followers

%d bloggers like this: