What's new in SQL Server Analysis Services 2019?
The focus of this book is on using SSAS 2019. What has Microsoft added to the product in its most recent release? Because Analysis Services is effectively broken into two types of databases – multidimensional and tabular – we will talk about the changes to each separately.
Multidimensional models in 2019
This is the short list. Microsoft has not made significant changes to multidimensional capabilities in Analysis Services since the SQL Server 2012 release. Even that release focused on the new xVelocity In-Memory Analytics Engine (aka Vertipaq) that would support tabular models. Microsoft considers the multidimensional model in Analysis Services mature and is not adding major features at this point. The focus is on bug fixes and various performance enhancements to the engine. The key takeaway here is that multidimensional models still have a place but are not receiving any significant updates. The following is from Microsoft's documentation:
Source
Tabular models in 2019
While multidimensional models are considered mature, Microsoft is continuing to make significant investments in tabular model technology. Since its release in 2012 until now, major changes have happened with tabular models.
Compatibility levels
When working with tabular models, you need to understand compatibility levels. Microsoft introduced compatibility levels to allow new versions of Analysis Services to be backward compatible while enabling significant changes to supported features. When creating a tabular model, it is recommended to use the most current compatibility level. However, if you have an existing model and want to upgrade to the latest SQL Server version, you can set your compatibility level to what you are currently running until you have a chance to update the level and test it with the new features. The compatibility level is set when creating a new project in Visual Studio.
SQL Server 2019 supports the 1500 (SQL Server 2019), 1400 (SQL Server 2017), and 1200 (SQL Server 2016) compatibility levels. The features released with SQL Server 2019 are included in compatibility level 1500.
Here are some of the key updates included with SQL Server 2019 Analysis Services (compatibility level 1500):
- Query interleaving
- Calculation groups in tabular models
- Governance setting for Power BI cache refreshes
- Online attach
- Many-to-many relationship support
Let's look at each of these changes in terms of what they are and why they matter.
Query interleaving
Query interleaving allows you to set how queries are handled based on query length and performance. Tabular model queries are handled in a first-in, first-out model (FIFO) by default. This means that a long-running query could make shorter queries run for longer if they follow that query in the queue. By enabling this feature, shorter queries can be executed during a long query run. This feature is only available for import models, not Direct Query. However, if you have a high-concurrency tabular model solution (lots of users or complex queries), this feature could improve performance for your users and reduce CPU pressure on the server.
Calculation groups
Calculation groups are used to group related calculations, which users often work with at the same time. This is really helpful with large complex models with many different calculations for the users to navigate. Microsoft calls out that Time intelligence will benefit from this significantly. For example, you can create a calculation group that has Current, Month-to-Date (MTD), Quarter-to-Date (QTD), and Year-to-Date (YTD) and call it xTD. When the user views the deployed model, they will see a calculation group as a single column they can add to their visual, which displays all four of these calculations as applied to a base measure such as Revenue. This feature has been added to improve usability in complex models.
Governance settings for Power BI cache refresh
The Power BI service caches data for dashboards and reports to improve performance and user experience when using live connections with tabular models. However, in some cases, this can cause a significant amount of queries with the possibility of overloading a server. This setting will override background refresh policies set on the client, preventing performance issues on the server.
Online attach
Currently, updates to tabular models require the model to be taken offline while deploying changes to the model. This results in downtime for the model. This feature allows model designers to deploy model changes live. This is similar to the shadow copy feature with multidimensional models, which supports the same online deployment.
The process currently is supported using XML for Analysis (XMLA) (more about that later). However, for tabular models, you will need to account for double the model's memory footprint during the online attach operation. The effective result is that during the attach process, both the new model and the old model will be in memory during the process. Once the process has completed, the old model will be removed. During the operation, users can continue to query the model and will start using the new model once it is loaded.
Many-to-many relationship support
Many-to-many relationship support has always been an issue with tabular models. This change allows relationships to be created between two tables where the relationship may not be unique. For example, if you have a fact table that is aggregated to the month, you will now be able to use the month value from a date table that has daily granularity. This allows cleaner, simpler models that are easier to use. Next, we will look at the tools that are used with SSAS.
