Chris Webb's BI Blog

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

Subselects And Formula Engine Caching In SSAS 2012

with 13 comments

One of the many interesting things that caught my eye in the new SSAS Tabular Performance Tuning white paper is actually about new functionality in the SSAS 2012 Multidimensional and nothing to do with Tabular! It turns out that in the DAXMD release of SSAS 2012, ie SQL Server 2012 SP1 CU4, some work was done to enable the Formula Engine to cache the results of MDX calculations for longer than the lifetime of a query when a query includes a subselect. Here’s an excerpt from the paper:

Certain types of queries cannot benefit from MDX caching. For example, multi-select filters (represented in the MDX query with a subselect) prevent use of the global MDX formula engine cache. However, this limitation was lifted for most subselect scenarios as part of SQL Server 2012 SP1 CU4 (http://support.microsoft.com/kb/2833645/en-us). However, subselect global scope caching is still prevented for arbitrary shapes (http://blog.kejser.org/2006/11/16/arbitrary-shapes-in-as-2005/), transient calculations like NOW(), and queries without a dimension on rows or columns. If the business requirements for the report do not require visual totals and caching is not occurring for your query, consider changing the query to use a set in the WHERE clause instead of a subselect as this enables better caching in some scenarios.

This is a subject I’ve blogged about in the past, both 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 background. In my experience, if you have a lot of complex MDX calculations on your cube (financial applications are a great example), this issue can have a major impact on your overall query performance, even if it isn’t immediately obvious that this is the case. On builds of SSAS before 2012 SP1 CU4, even if Storage Engine caching is working properly, if a query references a lot of MDX calculations and includes a subselect it will be consistently slow however many times you run it because the calculations will need to be re-evaluated every time the query is run.

I’ve heard of a few problems with CU4 regarding SSRS so I don’t recommend upgrading your production SSAS servers just yet, but when these problems have been ironed out in the next full service pack I think this could be a compelling reason for many people to move to SSAS 2012. There’s also still a limitation whereby queries that return a single cell value and use a subselect may still not be able to use the global Formula Engine cache, but hopefully this will be dealt with in a future release too. Overall, though, I’m extremely pleased to see yet another improvement to the Multidimensional engine.

Thanks to Jeffrey Wang for answering my questions about this functionality.

Written by Chris Webb

August 7, 2013 at 12:10 am

13 Responses

Subscribe to comments with RSS.

  1. Do you know whether SP1 CU5 already fixed SSRS issues?

    Marco Russo

    August 7, 2013 at 12:21 am

  2. This is excellent info. Good to know Multi-dimensional had a ‘good ride’. Thanks Chris.

    Regards,

    Min Shi
    QUANTIUM
    Level 27, 25 Bligh Street
    Sydney NSW 2000

    M: +61 411 219 217
    T: +61 2 9292 6420
    F: +61 2 9292 6444
    W: http://www.quantium.com.au

    [cid:image003.jpg@01CD9025.B969A2B0] [cid:image004.jpg@01CD9025.B969A2B0] [cid:image005.jpg@01CD9025.B969A2B0]
    The contents of this email, including attachments, may be confidential information. If you are not the intended recipient, any use, disclosure or copying of the information is unauthorised. If you have received this email in error, we would be grateful if you would notify us immediately by email reply, phone (+ 61 2 9292 6400) or fax (+ 61 2 9292 6444) and delete the message from your system.

    Min Shi

    August 7, 2013 at 6:01 am

  3. Great catch Chris!
    What kind of CU4 issues do you heard ?
    One month ago in one our clients after install we lost all SSRS functionality on that SharePoint

    In another server another installation without errors but DAXMD didn’t work

    On two others servers everthing worked (but we reinstalled SharePoint from zero)

    João Lopes

    August 7, 2013 at 10:20 am

    • I’ve only heard stories – I can’t point to anything substantial, but some people have mentioned that they had problems with SSRS after installing CU4, and continued to have problems after installing CU5. In general I wouldn’t want to install a CU build in production anyway, unless it was an emergency.

      Chris Webb

      August 7, 2013 at 10:49 am

  4. Thanks for the information on sub selects Chris. As far as CU4/5 are concerned yes it did have impact on SSRS.
    Another thing it’s doing is DAX queries against multi-dimesional cube is also throwing an “Internal error: An unexpected exception occurred” error on one of our internal cube. It works fine against the Adventureworks mind you. Not sure if anyone else has seen this issue.

    Sanjay Khadilkar

    August 20, 2013 at 2:51 pm

    • Thanks Sanjay, this is useful to know. Have you reported the internal error to MS? If you haven’t, you should.

      Chris Webb

      August 20, 2013 at 11:20 pm

      • Yes, I have reported it to Microsoft on connect. I haven’t seen any responses from MS yet.

        The strange thing is , the earlier version of the same cube worked fine in Preview mode of DAXMD

        Sanjay Khadilkar

        August 21, 2013 at 7:51 pm

  5. […] Subselects And Formula Engine Caching In SSAS 2012 […]

  6. […] such as declaring any other calculated members in the WITH clause, using subselects in your query (unless you have SSAS 2012 SP1 CU4), using non-deterministic functions and so […]

  7. […] Engine from caching the result of a calculation for longer than the lifetime of a query (see here and here for just two examples). Reading the new SSAS 2012/2014 Performance Guide, I spotted the […]

  8. […] Engine from caching the result of a calculation for longer than the lifetime of a query (see here and here for just two examples). Reading the new SSAS 2012/2014 Performance Guide, I spotted the […]


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,299 other followers

%d bloggers like this: