Chris Webb's BI Blog

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

Archive for March 2007

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

AS Partition Slicing

leave a comment »

There’s a great, very detailed discussion on how Analysis Services partition slicing works by Eric Jacobsen on the SQLCat blog here:
 
 

Written by Chris Webb

March 5, 2007 at 11:53 pm

Posted in Analysis Services

VBA Functions in MDX

with 3 comments

Greg Galloway has done some really cool things on the Analysis Services Stored Procedure Project which I’ll be blogging about soon, but in the meantime as part of his research he’s found an interesting white paper by Irina Gorbach on the use of VBA functions in MDX and which ones have been implemented as internal MDX functions to improve performance. Here’s the MSDN Forums thread as background:
And here’s the paper itself:
 

Written by Chris Webb

March 3, 2007 at 5:58 pm

Posted in MDX

Oracle to buy Hyperion

with 2 comments

As Mark Rittman says (and he should know), "no doubt what Oracle are interested in here is Hyperion’s expertize in the area of analytic financial applications, and their no doubt high-value customer base."
 
What does this mean for Oracle BI and for Hyperion’s toolset, especially Essbase? Given that Oracle already have their own OLAP tools, one wonders… I think this can only be a good thing for Microsoft – surely existing Essbase users will now be questioning its future and be looking for migration options, not necessarily from Oracle.
 
UPDATE: the news is confirmed, the price tag $3.3bn. Here are Nigel Pendse’s initial thoughts:
Interesting that he thinks that "Essbase is likely to become Oracle’s primary OLAP server".
 
… but the fact remains that Oracle now has two MOLAP databases, and that’s one too many in the long term. So perhaps it’s the old Express users who need to be thinking about their migration options? And perhaps the Essbase people should remember that Oracle has bought a market-leading MOLAP before, and look what happened to it – not everyone is convinced that multidimensional is best.
 
UPDATE #3: I’ll link, last of all, to Mark Rittman again, who shares his thoughts on what’s happened and what it means for Oracle BI:
 
 
 
 

Written by Chris Webb

March 1, 2007 at 11:11 am

Posted in On the internet

Follow

Get every new post delivered to your Inbox.

Join 3,309 other followers