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:
[sourcecode language='text' padlinenumbers='true' highlight='4']
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]
[/sourcecode]
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:
[sourcecode language='text' highlight='4']
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]
[/sourcecode]
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:
[sourcecode language='text' ]
WITH
MEMBER MEASURES.ROOTRETURNS as
TUPLETOSTR(ROOT([Date]))
SELECT {MEASURES.ROOTRETURNS} ON 0
FROM
[Adventure Works]
[/sourcecode]
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:
[sourcecode language='text' ] ([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]) [/sourcecode]
Yuck. Anyway, with this gigantic tuple inserted into our calculation like so:
[sourcecode language='text' highlight='4']
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]
[/sourcecode]
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.
One thought on “Tuning MDX Calculations That Use The Root() Function”