Tuning MDX Calculations That Use The Root() Function
Reposted from Chris Webb's blog with the author's permission.
Something that I have vaguely known about for years (for example from Mosha’s post here), but somehow never blogged about, is that using the Root() function in MDX calculations is not great for performance. I’m pretty sure that someone once told me that it was intended for use with defining subcubes for SCOPE statements and not inside calculations at all, which maybe why it hasn’t been optimised. Anyway, here’s an example of the problem and how to work around it.
Take the following query on the Adventure Works cube:
WITH
MEMBER MEASURES.DEMO AS
([Measures].[Internet Sales Amount],
ROOT([Date]))
SELECT
{[Measures].[Internet Sales Amount],
MEASURES.DEMO}
ON 0,
NON EMPTY
[Date].[Calendar].[Calendar Year].MEMBERS
*
[Customer].[Customer].[Customer].MEMBERS
ON 1
FROM
[Adventure Works]
It returns sales for all customers by year, and the calculated measure returns the sales for each customer across all dates using the Root() function.
On a warm SE cache (which means the amount of time taken by the query will be dependent on how quickly SSAS can do the calculation and evaluate the Non Empty) on my laptop this takes a touch over 7 seconds:
Notice also that the Calculate Non Empty End event tells us that the Non Empty filter alone took 2.8 seconds (see here for some more detail on this event).
Now if you rewrite the query, replacing the Root() function with the All Member on the Calendar hierarchy like so:
WITH
MEMBER MEASURES.DEMO AS
([Measures].[Internet Sales Amount],
[Date].[Calendar].[All Periods])
SELECT
{[Measures].[Internet Sales Amount],
MEASURES.DEMO}
ON 0,
NON EMPTY
[Date].[Calendar].[Calendar Year].MEMBERS
*
[Customer].[Customer].[Customer].MEMBERS
ON 1
FROM
[Adventure Works]
The query returns the same results, but in just over 5.5 seconds and with the Non Empty taking about 10% of the time it previously took.
I’m making a big assumption here though: the Root() function in the first query returns a tuple containing every All Member from every hierarchy on the Date dimension, not just the All Member from the Calendar hierarchy, so while these two queries return the same results the calculations are not equivalent. You can still get a performance improvement, though, by replacing the Root() function with the tuple it returns, although the resulting MDX will look very messy.
First, to find what the Root() function returns just use a query like this:
WITH
MEMBER MEASURES.ROOTRETURNS as
TUPLETOSTR(ROOT([Date]))
SELECT {MEASURES.ROOTRETURNS} ON 0
FROM
[Adventure Works]
Run it in SQL Server Management Studio and you can copy/paste the tuple from the query results:
Here’s the tuple I get from my (somewhat hacked around) Date dimension:
([Date].[Fiscal].[All Periods],[Date].[Calendar].[All Periods],[Date].[Calendar Weeks].[All Periods], [Date].[Fiscal Weeks].[All Periods],[Date].[Fiscal Year].[All Periods],[Date].[Date].[All Periods], [Date].[Calendar Quarter].[All Periods],[Date].[Fiscal Quarter].[All Periods], [Date].[Calendar Semester].[All Periods],[Date].[Fiscal Semester].[All Periods], [Date].[Day of Week].[All Periods],[Date].[Day Name].[All Periods], [Date].[Day of Month].[All Periods],[Date].[Day of Year].[All Periods], [Date].[Calendar Week].[All Periods],[Date].[Month Name].[All Periods], [Date].[Calendar Year].[All Periods],[Date].[Fiscal Semester of Year].[All Periods], [Date].[Calendar Semester of Year].[All Periods],[Date].[Fiscal Quarter of Year].[All Periods], [Date].[Calendar Quarter of Year].[All Periods],[Date].[Month of Year].[All Periods], [Date].[Fiscal Week].[All Periods],[Date].[Calendar Week of Year].[All Periods], [Date].[Fiscal Week of Year].[All Periods],[Date].[Current Date].[All Periods], [Date].[Is2002].[All Periods],[Date].[Month Day].[All Periods])
Yuck. Anyway, with this gigantic tuple inserted into our calculation like so:
WITH
MEMBER MEASURES.DEMO AS
([Measures].[Internet Sales Amount],
[Date].[Fiscal].[All Periods],[Date].[Calendar].[All Periods],[Date].[Calendar Weeks].[All Periods],[Date].[Fiscal Weeks].[All Periods],[Date].[Fiscal Year].[All Periods],[Date].[Date].[All Periods],[Date].[Calendar Quarter].[All Periods],[Date].[Fiscal Quarter].[All Periods],[Date].[Calendar Semester].[All Periods],[Date].[Fiscal Semester].[All Periods],[Date].[Day of Week].[All Periods],[Date].[Day Name].[All Periods],[Date].[Day of Month].[All Periods],[Date].[Day of Year].[All Periods],[Date].[Calendar Week].[All Periods],[Date].[Month Name].[All Periods],[Date].[Calendar Year].[All Periods],[Date].[Fiscal Semester of Year].[All Periods],[Date].[Calendar Semester of Year].[All Periods],[Date].[Fiscal Quarter of Year].[All Periods],[Date].[Calendar Quarter of Year].[All Periods],[Date].[Month of Year].[All Periods],[Date].[Fiscal Week].[All Periods],[Date].[Calendar Week of Year].[All Periods],[Date].[Fiscal Week of Year].[All Periods],[Date].[Current Date].[All Periods],[Date].[Is2002].[All Periods],[Date].[Month Day].[All Periods])
SELECT
{[Measures].[Internet Sales Amount],
MEASURES.DEMO}
ON 0,
NON EMPTY
[Date].[Calendar].[Calendar Year].MEMBERS
*
[Customer].[Customer].[Customer].MEMBERS
ON 1
FROM
[Adventure Works]
The query is a little slower – just over 6 seconds- but still faster than the first query using Root(), and the Non Empty filter is still fast:
Something else to watch out for with Root(), and another good reason not to use it in calculations, is that it returns an error in certain multiselect scenarios as Richard Lees describes here.
 |
Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ . |
Tags: mdx, performance