Chris Webb's BI Blog

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

Excel 2010, Subselects, Named Sets and the Formula Cache

with 4 comments

Continuing the theme of the Formula Cache, you may remember a post from a while ago where I showed how using a subselect in a query forced query scope – so that SSAS was unable to cache the results of calculations for more than the lifetime of a single query. Now this is very significant if you have calculations that take a long time to evaluate and you’re using Excel as a client tool, because Excel makes extensive use of subselects in its queries.

For example, if we take the calculation ‘ExpensiveCalc’ from that previous post and use it in an Excel pivot table as below:

image

We’ll find that every time we refresh the pivot table it’s painfully slow. This is because we’ve selected just one Year on columns and Excel has generated the following MDX query, using a subselect, as a result:

SELECT
NON EMPTY
Hierarchize({DrilldownLevel({[Date].[Calendar Year].[All Periods]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS 
FROM (SELECT ({[Date].[Calendar Year].&[2001]}) ON COLUMNS 
FROM [Adventure Works])
WHERE ([Measures].[EXPENSIVECALC])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Not good. Luckily, we can avoid this happening in Excel 2010 by using the new named set functionality. If you go to the Pivot Table Tools/Options tab on the ribbon, and select ‘Create Set Based On Column Items’ from the Fields, Items & Sets menu:

image

…and create a new named set:

image

You’ll find that the MDX generated by Excel changes and there’s no subselect:

SELECT
NON EMPTY
{[Year 2001]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS 
FROM [Adventure Works]
WHERE ([Measures].[EXPENSIVECALC])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

This means that although the pivot table will be slow to refresh when you click OK, on subsequent refreshes you will be able to benefit from the FE cache and the refresh will be practically instant. This is a very useful trick if your users have a number of Excel pivot tables they open on a regular basis; it won’t cure all performance problems but it’ll cure some at least.

Written by Chris Webb

April 6, 2011 at 12:58 pm

4 Responses

Subscribe to comments with RSS.

  1. Useful piece of info. Thank you Chris..

    Jason Thomas

    April 7, 2011 at 10:09 am

  2. […] for SSRS reports (which almost always use subselects in the MDX created by the query editor) and Excel PivotTables (which sometimes, but not always, use subselects) and you may want to read this posts to get some […]

  3. I have used named sets to try and replace an existing pivot table, and the performance is vastly improved.

    Unfortunately, the named sets are not dynamic so if/when more members arrive in the cube the named set doesnt automatically contain them.
    Values for existing members are updated but new members are not automatically included.

    Dave C

    October 16, 2013 at 2:22 pm

    • I think you could use some custom MDX in your named set, for example using the .MEMBERS function, to make sure that new members were automatically included when they are created…?

      Chris Webb

      October 16, 2013 at 9:54 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,868 other followers

%d bloggers like this: