With the recent release of SQL Server 2016 some of you old faithful SSAS Multidimensional users out there might be wondering if it’s worth upgrading. There is an official page describing what’s new in Analysis Services 2016 here:
https://msdn.microsoft.com/en-us/library/bb522628.aspx
…but it’s mostly concerned with SSAS Tabular and, unfortunately, it’s somewhat misleading. For example, it says:
a number of enhancements have been made to multidimensional models; for example, distinct count ROLAP optimization for data sources like DB2 and Oracle, drill-through multi-selection support with Excel 2016, and Excel query optimizations.
In fact the drillthrough/multi-select improvements (which I blogged about here) already shipped as part of SSAS 2014 and are reliant on improvements in Excel 2016 as much as in SSAS; similarly the Excel 2016 query optimisations are not reliant on any changes in SSAS 2016 and will benefit users of all versions of SSAS.
So what has actually changed with SSAS 2016 Multidimensional? I don’t know all the details on every change, but here’s what I know right now:
- As the above quote shows, there have been improvements in ROLAP distinct count performance for DB2 and Oracle. I’ve also heard there are improvements for the SQL generated in ROLAP mode to take advantage of SQL Server’s columnstore indexes.
- We now have Database Consistency Checker for SSAS, which you can read more about here or in Dustin Ryan’s post here. For Multidimensional it will only tell you whether your partition indexes are corrupt (it does a lot more for Tabular) and if they are you need to delete the database and then either restore it or re-deploy/reprocess.
- Extended Events for SSAS now have UI support in SQL Server Management Studio, although the UI still looks like it needs some work. I still see myself using Profiler for my SSAS performance tuning work for the foreseeable future – or at least until I work out what the extra information that Extended Events give you is useful for. I know other people, like Bill Anton, are more excited about Extended Events and their possibilities though.
- You can now use computer accounts to be members of the Analysis Services Administrators group in SQL Server Management Studio.
- If you are developing applications that need to create, alter, process or otherwise do stuff to an Analysis Services database in .NET code then you need to be aware that Analysis Management Objects (AMO) has been refactored to include a second assembly which “paves the way for future extensions to AMO, with clear division between generic and context-specific APIs”.
- There’s a new default setting for the MemoryHeapType server property that helps to avoid memory fragmentation and is relevant to Tabular and Multidimensional; Marco Russo has the details here.
Not the most exciting or inspiring set of changes, I have to say, even if there’s lots of cool new stuff in SSAS Tabular 2016. There are also usually other fixes and improvements like the MemoryHeapType property that get added but are never documented, and when I find out about them I’ll be sure to blog or tweet. If you find any please let me know!
Not really very much which is a shame. It’s such a good product and can be made so much better.
Hey Chris would you please blog on this https://feedback.azure.com/forums/556165-azure-analysis-services/filters/top? Especially for a ROLAP architecture?
Hi Bob, which feature request are you referring to specifically?
Hi Chris,
We have a client that we built a multidimensional cube for in 2000 over a Teradata data warehouse. 16 years later we have a project to expand the capability and retire the original application (still running on on Windows 2000 and Analysis Server 2000! – don’t ask – outsourcing, etc…).
The data warehouse is still on Teradata – but now Teradata has columnar and other features that scale much better than in 2000. The client likes Qlik-Sense and the Microsoft BI offerings, but both require extracts from Teradata and are limited by a closed analytic layer API. So a ROLAP offering with Analysis Server MD over the Teradata data warehouse with dynamic and economic memory expansion via Azure and an open MDX/XMLA calculation layer seems compelling.
This enhancement https://feedback.azure.com/forums/556165-azure-analysis-services/suggestions/16813708-support-for-multidimensional-models is the one I am talking about and a comment made by someone else captures it well :
“1) ROLAP against Azure DW – we want and need a fast data warehouse for large data sets, and we want our analysis tools to be in near real time, as the data warehouse loads, thus ROLAP against a data warehouse. Azure DW scales, and that is really important to the future of a DW
2) Multi-dimensional is better than tabular, has more features, is a better development tool
3) ROLAP reduces/eliminates the need to support large data sets in SSAS
4) ROLAP eliminates the need for dual data entry into both DW and SSAS”
You have a great following on your excellent blog so I thought you might be able to advance/influence this enhancement priority if you thought it was a good idea. It might bring Analysis Server MD back from a near death experience.
Regards,
Bob Doss
Trust me, a lot of people have been lobbying hard for MD support in Azure SSAS, including me! I know the dev team would love to do this too, but I don’t know when or if they will have the time or resources to do it. No-one is a bigger fan of MD than I am, but the reality is that Tabular and Power BI are the priorities for MS now.
In the meantime, remember that SSAS Tabular 2016 (and therefore Azure SSAS) supports Teradata in DirectQuery mode: https://msdn.microsoft.com/en-us/library/hh230898.aspx I’m willing to bet that any SSAS 2000 cube can be recreated in SSAS Tabular 2016 fairly easily.
Hi Chris,
another problem that was a nightmare to me is the “arbitrary shape” problem: queries that involve different levels on the same hierarchy do not use the cache (not at all, even in the repeated reads), so they are horribly slow.
Think to a query where you want the list of all harbors in the Iberia peninsula facing the Atlantic Ocean: you must list the Hispanic harbors, but you know that all the harbors in Portugal satisfy this condition!
In MDX the solution is simple: you put a .children.
The problem is with Excel that generated queries of this kind.
MSFT has put a solution somewhere ?
Thanks
No, there has been no change at all with this problem. My advice would be to change the hierarchy, adding an extra attribute so that you could select all the harbours you want by selecting a single member – this would give you the best possible performance and avoid the arbitrary shaped set problem completely.