We find great pleasure in announcing RC1 of SQL Server 2019 Analysis Services (SSAS 2019). The SSAS 2019 release is now feature complete! We may still make performance improvements for the RTM release.
RC1 introduces the following features:
- Custom ordering of calculation items in calculation groups
- Query interleaving with short query bias for high-concurrency workloads
- Online attach for optimized synchronization of read-only replicas
- Improved performance of Power BI reports over SSAS multidimensional
- Governance setting to control Power BI cache refreshes
SSAS 2019 features announced in previous CTPs are recapped here:
- Calculation groups for calculation reusability in complex models (CTP 2.3)
- Governance settings to protect server memory from runaway queries (CTP 2.4)
- Many-to-many relationships can help avoid unnecessary “snowflake” models (CTP 2.4)
- Dynamic measure formatting with calculation groups (CTP 3.0)
We think you’ll agree the pace of delivery from Analysis Services engine team has been phenomenal lately. The SSAS 2019 release demonstrates Microsoft’s continued commitment to all our enterprise BI customers whether on premises, in the cloud, or hybrid.
Calculation groups
Calculation groups address the issue of proliferation of measures in complex BI models often caused by common calculations like time-intelligence. SSAS models are reused throughout large organizations, so they tend to grow in scale and complexity.
The public preview of calculation groups was announced for SSAS 2019 in the CTP 2.3 blog post, and Azure Analysis Services on the Azure Updates blog. Calculation groups will soon be launched and supported in Power BI Premium initially through the XMLA endpoint.
We are grateful for the tremendous enthusiasm from the community for this landmark feature for Analysis Services tabular models. Specifically, we’d like to thank Marco Russo and Alberto Ferrari for their excellent series of articles, and of course Daniel Otykier for ensuring that Tabular Editor supported calculation groups since the very first preview (CTP 2.3).
Here’s a link to the official documentation page for calculation groups: https://aka.ms/CalculationGroups. It contains detailed examples for time-intelligence, currency conversion, dynamic measure formats, and how to set the precedence property for multiple calculation groups in a single model. We plan to keep this article up to date as we make enhancements to calculation groups and the scenarios covered.
Custom ordering calculation items in calculation groups (Ordinal property)
RC1 introduces the Ordinal property for custom ordering of calculation items in calculation groups. As shown in the following image, this property ensures calculation items are shown to end-users in a more intuitive way:
SQL Server Data Tools (SSDT) support for calculation groups is being worked on and is planned by SQL Server 2019 general availability. In the meantime, in addition to Tabular Editor, you can use SSAS programming and scripting interfaces such as TOM and TMSL. The following snippet of JSON-metadata from a model.bim file shows the required properties to set up an Ordinal column for sorting purposes:
{
"tables": [
{
"name": "Time Intelligence",
"description": "Utility table for time-Intelligence calculations.",
"columns": [
{
"name": "Time Calc",
"dataType": "string",
"sourceColumn": "Name",
"sortByColumn": "Ordinal Col"
},
{
"name": "Ordinal Col",
"dataType": "int64",
"sourceColumn": "Ordinal",
"isHidden": true
}
],
"partitions": [
{
"name": "Time Intelligence",
"source": {
"type": "calculationGroup"
}
}
],
"calculationGroup": {
"calculationItems": [
{
"name": "YTD",
"description": "Generic year-to-date calculation.",
"calculationExpression": "CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))",
"ordinal": 1
},
{
"name": "MTD",
"description": "Generic month-to-date calculation.",
"calculationExpression": "CALCULATE(SELECTEDMEASURE(), DATESMTD(DimDate[Date]))",
"ordinal": 2
}
]
}
}
]
}
Query interleaving with short query bias
SSAS models are reused throughout large organizations, so often require high user concurrency. Query interleaving in RC1 allows system configuration for improved user experiences in high-concurrency scenarios.
By default, the Analysis Services tabular engine works in a first-in, first-out (FIFO) fashion with regards to CPU. This means, for example, if one expensive/slow storage-engine query is received and followed by two otherwise fast queries, the otherwise fast queries can potentially get blocked waiting for the expensive query to complete. This is represented by the following diagram which shows Q1, Q2 and Q3 as the respective queries, their duration and CPU time.
Query interleaving with short query bias allows concurrent queries to share CPU resources, so fast queries are not blocked behind slow ones. Short-query bias means fast queries (defined by how much CPU each query has already consumed at a given point in time) can be allocated a higher proportion of resources than long-running queries. In the following illustration, the Q2 and Q3 queries are deemed “fast” queries and therefore allocated more CPU than Q1.
Query interleaving is intended to have little or no performance impact on queries that run in isolation; a single query can still consume as much CPU as it does with the FIFO model.
For details on how to set up query interleaving, please see the official documentation page: https://aka.ms/QueryInterleaving
Online attach
Online attach can be used for synchronization of read-only replicas in on-premises query scale-out environments.
To perform an online-attach operation, use the AllowOverwrite option of the Attach XMLA command. This operation may require double the model memory to keep the old version online while loading the new version.
<Attach xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> <Folder>C:\Program Files\Microsoft SQL Server\MSAS15\OLAP\Data\AdventureWorks.0.db\</Folder> <AllowOverwrite>True</AllowOverwrite> </Attach>
A typical usage pattern could be as follows:
- DB1 (version 1) is already attached on read-only server B.
- DB1 (version 2) is processed on the write server A.
- DB1 (version 2) is detached and placed on a location accessible to server B (either via a shared location, or using robocopy, etc.).
- The <Attach> command with AllowOverwrite=True is executed on server B with the new location of DB1 (version 2).
- Without this new feature, the administrator is first required to detach the database and then attach the new version of the database. This leads to downtime when the database is unavailable to users, and queries against it will fail.
- When this new flag is specified, version 1 of the database is deleted atomically within the same transaction with no downtime. However, it comes at the cost of having both databases loaded simultaneously into memory.
Improved performance of Power BI reports over SSAS multidimensional
RC1 introduces optimized DAX query processing for commonly used DAX functions including SUMMARIZECOLUMNS and TREATAS. This can provide considerable performance benefits for Power BI reports over SSAS multidimensional. This enhancement has been referred to as “Super DAX MD”.
To make end-to-end use of this feature, you will need a forthcoming version of Power BI Desktop. Once the Power BI release is shipped and validated, we intend to enable it by default in a SSAS 2019 CU.
Governance setting for Power BI cache refreshes
The ClientCacheRefreshPolicy governance setting to control cache refreshes was first announced for Azure Analysis Services on the Azure blog in April. This property is now also available in SSAS 2019 RC1.
The Power BI service caches dashboard tile data and report data for initial load of Live Connect reports. This can cause an excessive number of cache queries being submitted to SSAS, and in extreme cases can overload the server.
1500 compatibility level
The SSAS 2019 modeling features work with the new 1500 compatibility level. 1500 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.
Download Now
To get started with SQL Server 2019 RC1, find download instructions on the SQL Server 2019 web page. Enjoy!