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.
Do you know whether SP1 CU5 already fixed SSRS issues?
No, I don’t
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.
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)
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.
I believe this is the issue Chris is referring to in his blog http://connect.microsoft.com/SQLServer/feedback/details/789435/ssrs-2012-problems-with-related-nested-multi-valued-parameters-after-applying-sp1-cu4
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.
Thanks Sanjay, this is useful to know. Have you reported the internal error to MS? If you haven’t, you should.
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