What’s new in Analysis Services 2012 Multidimensional?

Reposted from Chris Webb's blog with the author's permission.

Now that SQL Server 2012 has been released, I’ve just realised I’ve not seen a definitive list of what’s new in SSAS 2012 Multidimensional. In fact I’m surprised I haven’t got round to writing a blog post about this… after all, despite the fuss over Tabular, Power View and all the other cool new stuff I guess most existing SSAS users are going to be more interested in staying with the Multidimensional model when they upgrade.

So what is new? Here’s a list of everything I know about plus some relevant links:

• The infamous 4GB limit on string stores in dimensions has now been fixed. See:
  http://msdn.microsoft.com/en-us/library/gg471589(v=sql.110).aspx
  http://raphaelmsbithoughts.wordpress.com/2011/07/22/denali-ctp3-analysis-services-new-feature-scalable-string-storage/
• Distinct count performance in ROLAP mode has been improved:
  http://msdn.microsoft.com/en-us/library/bb522628(v=sql.110).aspx#bkmk_distinctCount
• SSAS now supports NUMA and more than 64 processors:
  http://msdn.microsoft.com/en-us/library/hh548655(SQL.110).aspx
  This is a very detailed, must-read blog post on the subject:
  http://blogs.msdn.com/b/psssql/archive/2012/01/31/analysis-services-thread-pool-changes-in-sql-server-2012.aspx
  All this may be why processing is much faster in some cases, as Bob Duffy notes here:
  http://blogs.prodata.ie/post/Analysis-Services-2012-Faster-Processing.aspx
• I’ve seen it mentioned in several other places that there have been 300 other improvements for performance, scalability and reliability in the SSAS engine. I’ve not seen them documented anywhere (maybe they are in a KB article) but they’re likely to be minor tweaks/bug fixes similar to what you’d get in any SP. Nevertheless if you run into the specific scenarios they address then they could be very significant.
• SSAS now supports XEvents for monitoring:
  http://msdn.microsoft.com/en-us/library/gg492139(v=sql.110).aspx
• There are some new Profiler events. BOL notes the Resource Usage and lock-related events, but the former (and I’m pretty sure the latter) were also made available to 2008 R2 users with the last SP:
  http://msdn.microsoft.com/en-us/library/bb522628(v=sql.110).aspx#bkmk_traceResource
  http://msdn.microsoft.com/en-us/library/bb522628(v=sql.110).aspx#bkmk_traceLocks
  However the really interesting new events are “Calculation Evaluation” and “Calculation Evaluation Detailed Information” which give an insight into what’s going on in the Formula Engine. I hope we get some information on how to interpret the contents of these events.
• There’s a new Analysis Services PowerShell provider and cmdlets:
  http://technet.microsoft.com/en-us/library/hh213141(v=sql.110).aspx
  http://www.databasejournal.com/features/mssql/analysis-services-powershell-provider-sqlas-in-sql-server-2012.html
• All development work is now done in Visual Studio 2010, and BIDS is now called SQL Server Data Tools (SSDT).
• The cube browser has now disappeared from SSDT, and is replaced by the control used by the SSRS query generator. This is because the old browser used the OWC control which was deprecated years ago and finally had to be replaced; it’s a massive step backwards because this control is truly awful: for example you can no longer put anything other than Measures on columns in your query. Arrggghhh! I’ve started using Excel instead, but Excel won’t always be available on some sites and it’s a hassle to move to a different app to check your figures.

Have I forgotten anything? Have you found anything changed that isn’t documented? If so, please leave a comment. I’ll update this post as and when I find/remember new stuff.

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 .