There was a time when a new release of SQL Server – and therefore a new release of SQL Server Analysis Services – was the most exciting thing in the world for me. New functionality! New things to blog about! Not so now that my focus, and Microsoft’s, is on Power BI and we get cool new functionality there every month. All the same there are still a lot of people running SSAS on-premises and SQL Server 2022 has just been released, so what’s new and is it worth upgrading?
There’s nothing about Analysis Services in the SQL Server 2022 GA announcement blog post, but you can find a list of what’s new here:
Most of the items listed here are performance optimisations, most of which have been available in Power BI and Azure Analysis Services for some time now (although we haven’t got parallel execution plans for DirectQuery in Power BI just yet 😉). Probably the most important in my opinion is MDX Fusion, the main effect of which is to improve the performance of Excel PivotTables and cube-function-based reports connected to SSAS Tabular – I saw some cases where MDX queries ran a lot faster when this rolled out for Power BI. All the features are applicable to SSAS Tabular although some are applicable to SSAS Multidimensional too; there are also a few other minor optimisations that aren’t listed. The new cloud-billing model announced here is only applicable to the SQL core engine and not to SSAS, SSRS or SSIS.
There are no deprecated features but Multidimensional’s data mining features and PowerPivot for SharePoint are now officially discontinued (which means that they are now no longer supported – see the definition of “discontinued” here).
As a Microsoft employee, obviously I’m going to say you should upgrade to SQL Server 2022. As a member of the Power BI product group I would add that you should also consider migrating all your on-prem SSAS Tabular models to Power BI Premium if you can: Power BI Premium is the strategic direction for enterprise BI as well as self-service BI and that’s where all the investment is going from now on. Don’t think about migrating to Azure Analysis Services instead – we’re already encouraging people to migrate from AAS to Premium! My colleague Dan English just posted a great walkthrough of the new AAS to Premium migration experience here, which is worth checking out.
Migration from SSAS Multidimensional to Power BI is a much more difficult task. You’ll need to rebuild your existing cubes and calculations from scratch manually in Power BI (there are no tools to automate migration because it isn’t possible to build them). Simple cubes should be easy to rebuild; more complex cubes, for example those with parent/child hierarchies, custom rollups or SCOPE statements for example, will be much more difficult to migrate and you may need to accept that you can’t reproduce some functionality exactly. You can always run SSAS Multidimensional in a virtual machine in Azure if you need to move to the cloud and there are VM images to make that easy.
The issue with AAS to PBI Premium is always the licensing differences, not the technical differences.
Agreed, and I accept that the licensing and pricing differences can be difficult to resolve, especially if you’re not using Power BI already.
In many cases, “difficult to resolve” means “two or three times the price” 🙂
Well as I said if you have multiple AAS databases you can save a lot of money by consolidating them into a single Premium capacity. The memory limit is per dataset and CPU for refresh (the most CPU intensive operation) is smoothed over 24 hours.
why migrate to PBI Premium? AAS works well. Whats the advantage of PBI Premium over AAS?
A couple of advantages of migrating to Premium: more data sources, one service to manage instead of two (if you’re using Power BI plus AAS), cost savings if you can consolidate multiple AAS databases onto a single (maybe already existing) Premium capacity, and in the future there will be more scalability/performance improvements that can’t be backported to AAS.
But if you always build out a data warehouse to load PBI from, the additional data sources in PBI aren’t an issue. We’re using ADF to load into the DW, not PBI.
Also, it depends on what you’re using for your DW. AAS can’t connect to many platforms you might want to use.
Huh, your post suggests that there is an (automated) way of moved tabular models to Power BI Premium? Or Pro?
There is a new migration tool for moving from AAS to Premium, yes. If you want to move from on-prem SSAS to Premium you can always go to Visual Studio and deploy direct to Premium via the XMLA Endpoint.
Hey Chris, can you double-check the link regarding your colleague Dan? It appears to be broken for me. Thank you!
Thanks for letting me know, it’s fixed now
Is SetSPN needed for SQL Server 2022 Analysis Services