Chris Webb's BI Blog

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

Some Time Intelligence Wizard Calculations are Inefficient

with 10 comments

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

10 Responses

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

  2. I tried all the above 3 and the query went up from 10min to 1 sec! And what I found is replacing Aggregate with SUM did the trick. Has anyone seen this?

    Vidya

    April 24, 2014 at 6:12 pm

    • Also, I used below solution and not the wizard as our design has to support multiple dynamic calendars –

      http://davefackler.blogspot.com/2008/06/handling-multiple-calendars-with-m2m.html

      Vidya

      April 24, 2014 at 8:40 pm

    • Yes, I have seen this sometimes. I think it’s related to the way Aggregate() changes the solve order of calculations, but I’m not sure. It doesn’t happen in all cases so you shouldn’t take it as a general rule that Sum() performs better than Aggregate().

      Chris Webb

      April 24, 2014 at 9:35 pm

      • Thanks for the links. I will check out the partition issue. I changed all the time calculation aggregations(YTD., QTD, MTD, WTD) to sum and it seems to have fixed it. When you say it doesn’t happen all the time, do you think the behavior would change for the above calcs? Or the same performance might not be gained if I used it some place else? Pls elaborate.

        Vidya

        April 25, 2014 at 2:43 pm

      • Sorry to be vague – I can’t say whether the improvement would change for these calcs. It depends on what other calculations are present on the cube, the structure of dimensions etc etc. The only thing I can say is that it is not the case that Sum is always faster than Aggregate.

        Chris Webb

        April 25, 2014 at 3:54 pm

  3. As of now I have to go with this current design as we want to have an option of adding/dropping calendars back-end and not have to update the cube every time. I did not find any other solution to fit this requirement.
    Do you have any suggestions of other options? Thanks for all your responses.

    Vidya

    April 25, 2014 at 4:10 pm

    • There isn’t a good alternative solution, unless you either partition your measure group differently or remove the m2m relationship and instead put all of your attributes in a single dimension (which probably isn’t feasible)

      Chris Webb

      April 25, 2014 at 10:35 pm

      • Thanks for the response. I will go with my current design then. Pls update this thread if in future you find any alternative solution for dynamic calendars.

        Vidya

        April 28, 2014 at 2:48 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 3,310 other followers

%d bloggers like this: