The public CTP 2.0 of SQL Server 2022 is now available at aka.ms/getsqlserver2022! This public preview includes the following enhancements for Analysis Services tabular.
- Power BI support for SQL Server 2022 Analysis Services tabular models as a DirectQuery connection for composite models
- Improved MDX query performance
- Improved resource governance
- Query interleaving now includes short query bias with fast cancellation
Power BI support for SQL Server 2022 Analysis Services tabular models as a DirectQuery connection for composite models
We are excited to announce you can create composite models in Power BI Desktop by connecting to SQL Server 2022 Analysis Services tabular models!
When you upgrade or deploy your tabular models to SQL Server 2022 Analysis Services this DirectQuery connection becomes available in Power BI Desktop starting with May 2022 release. Business users can then combine the SSAS 2022 model, or even individual tables within the model, with other imported or DirectQuery tables to meet their reporting needs. The connection to the SSAS 2022 tabular model works like the existing composite model experience using DirectQuery to other Power BI datasets or Azure Analysis Services in Power BI Desktop.
This transformative feature now allows your organization to build upon your existing on-premises SSAS 2022 tabular solutions with Power BI Desktop right away to create reusable and refreshable combinations of data sources for ad hoc and continuing reporting needs. Keep in mind that for a published composite model dataset to refresh on the Power BI service, a standard mode Power BI Gateway is needed to facilitate the connection to each SSAS 2022 tabular model.
Improved MDX query performance
Business users utilizing SSAS 2022 tabular models in Multidimensional Expressions (MDX) clients, such as Microsoft Excel, can now enjoy improved query performance. With an update of the Analysis Services tabular engine in SQL Server 2022, previously only available and Azure Analysis Services, performance improvement alleviates some of the optimization burden for measures used in a PivotTable.
Announcing improved MDX query performance in Power BI | Microsoft Power BI Blog | Microsoft Power BI has more details about this feature.
Improved resource governance
Ensure resource governance for your users. In SSAS 2019, the Query Memory Limit setting only applies to memory spools where intermediate DAX query results are created during query processing. Now in SSAS 2022 it also applies to Multidimensional Expressions (MDX) queries.
Improved resource governance for Azure Analysis Services | Azure updates | Microsoft Azure and Analysis Services memory properties | Microsoft Docs has more detailed information about resource governance as well as other Analysis Services memory properties.
Query interleaving now includes short query bias with fast cancellation
SSAS 2022 tabular models in high demand by your users? No problem. In high-concurrency scenarios with import models, the new default setting for query interleaving of short query bias with fast cancellation can effectively relieve bottlenecks and get data back to your business users faster. Think of this as an overtake lane, where faster queries and cancellations are no longer stuck behind slower queries. Queries that use the least CPU of the storage engine run before more CPU intensive queries instead of in the order they were queried. This is the same default behavior available in Azure Analysis Services and Power BI.
Next steps
- Download the public preview CTP 2.0 at aka.ms/getsqlserver2022
- See a demo of Power BI Desktop composite models with SSAS 2022
- Learn more about this exciting new version on the SQL Server blog