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.
Great post, thanks for compiling this list Chris!
For the development, I think you mean Visual Studio 2010, not 2012… is that right?
Um yes, thanks for spotting that!
Great to see those changes in the new release and thanks a lot for summarizing it Chris. It is a pity that you have to catch your flight and could not stay with us after the SQL PASS event on Sat. in Dublin.
That browser change really sucks!!!
Thomas
True, it does not have expand/collapse functions for user defined hierarchies as well.
I agree that browser change sucks. Instead of upgrading to maybe a PivotTable like browser they are making it mostly unusable by making it SSRS like.
Regarding the OWC deprecation. The OWC component was used in a few separate areas of the product, all of which were impacted.
Other SSDT components affected are the calculation debugging and exploring data in a DSV. For calculation debugging, a replacement control was provided, same as for cube browsing. Exploring data in the DSV is the worst, as Microsoft simply removed the functionality for creating pivot tables and charts from DSV. Now you can only show data from DSV in a tabular format within SSDT.
The OWC components were also removed from SSMS and replaced with the MDX query designer.
I will note here that Microsoft did extensive side-by-side testing with SQL Server 2008 R2 and SQL Server 2012 tools. If the OWC deprecation makes you very sad, you can just use the old tools side-by-side with the new tools. You can manage a SQL Server 2012 multidimensional model very well from SQL Server 2008 R2 SSMS, and you can simply browse your cubes in OWC from there after deployment.
Thanks for detailing the changes, Chris — always appreciated. You are indeed correct that this new cube browser is undeniably terrible. I’d like to use stronger words, but this is a family blog after all.
In case anyone else finds it useful, I’ll point out that SSDT will only work once you install the SQL2012 client tools. Before that, I had tried downloading SSDT and installing it on my existing VS2010. Imagine my frustration when I was able to use the database tools, but unable to connect to SSRS, SSAS, etc. In short, keep in mind that two different sets of tools are flying under the SSDT banner.
Hi Chris
Great info, I’m trying AS2012 Distinct Count enhancement for ROLAP, but don’t see the AS2012 property parameter: OLAP\ProcessPlan\EnableRolapDistinctCountOnDataSource
Am I missing something??
You need to look in the msmdsrv.ini file and set it there.
has there been any enhancements to the formula engine, is it multi threaded now?
No, it’s still single-threaded.
Hi Chris, has your request for role playing measure groups been added? I noticed your workaround in the link below which I’m going to try out on a large data set however it would be more elegant to incorporate this in BIDS
http://cwebbbi.wordpress.com/2011/01/22/solving-the-events-in-progress-problem-in-mdx-part-2role-playing-measure-groups/
Sadly not, John…
I know it’s a bit off topic, but can we expect improvement with regards to SSRS and SSAS integration.
F.e. built in support for drilldown and drillup?
By the way, whatever happend to the great work IT-Workplace did with regards to the custom SSRS data source for SSAS, it doesn’t seem to be around anymore (?)
I think it’s safe to say that, after all this time, there won’t be any changes or improvements to SSAS/SSRS integration now!
No idea what happened to IT Workplace…
Hi Chris
Nice list of the new or edited features of SQL Server…
As we do the design of cubes mostly on servers where no excel is installed is there a plugin or something similiar to browse the cube than with the horrible new cube browser integrated in SSMS / SSDT?
We would really appreciate something to browse trough the hierarchies and all the stuff as we can do in SSMS 2008R2… Perhaps you have an idea?
Thx in advance and best wishes from switzerland
Pius
The only thing you can do is install SSMS 2008 R2. You can still use it to manage SSAS 2012 and the old cube browser still works OK.
Hey Chris
Thank you for your fast answer… I think the old SSMS is not really an option due to licence-problems.
I hoped that there is another solution for that newly created dilemma such as a add-in or something equivalent…
Regards
Pius
Chris,
I’m using Analysis Services 2012 Multi-Dimensional mode. I have a 64 logical processors box. When I run large # of queries, I only see AS using 16 Logical Processors. I’ve played around with some of the ini settings, but I can’t get it to use more than 16 logical processors. Have you seen this behavior?
It’s hard to say what’s going on here – which ini file settings have you changed, and are you running queries that are Storage Engine intensive (have you partitioned your measure groups and do your queries hit lots of partitions)?
Hello,
I’m testing SSAS 2012-SP1 on a very powerful server (4 sockets / 10 cores / 80 logical processors) with 512 Go RAM / this server is numa mode hardware / WS2012 standard edition and two disk partitions (1 Raid 5 1To) and the other SSD 512go dedicated to OLAP.
We have realized several tests between Tabular mode and Olap mode .. OLAP works perfectly on numa mode ! the 80 proc are all used during the compilations and the SE perfectly use all procs !. (very impressive !). our main fact table as more that 2 billions rows. and the respond time is less that 2 seconds on 80% or our Mdx querys (even when the SE has to aggregate from scrach).
as I said to Marco we have lot of problems with tabular engine (not compatible with numa mode, dont use the 80 procs ! ) and in conclusion OLAP with SSD disks is faster that Tabular ! and really more stable.
Very interesting results – thanks for sharing!
removing the OWC component is a huge step backward. why on earth would I need to install Excel on the Production server just so that I can verify the data on SSAS. I believe this is an ambush from Microsoft so that they can push for Excel as a BI client. very disappointed indeed.
I agree it was a step back, but it’s not a conspiracy. OWC had been deprecated for a long time before it was removed from Visual Studio – the SSAS team just didn’t have the resources to build a new control.
Thanks Chris for this post. Can you please inform is there anything else new in SSAS 2012? Or everything is already covered in above blog and comments?
No, there’s nothing else substantial in SSAS 2012 beyond what’s in this post.