What’s new for SQL Server 2019 Analysis Services CTP 2.4
We are excited to announce the public CTP 2.4 of SQL Server 2019 Analysis Services. This public preview includes the following enhancements for Analysis Services tabular models.
- Many-to-many relationships
- Memory settings for resource governance
Many-to-many relationships
Many-to-many (M2M) relationships in CTP 2.4 are based on M2M relationships in Power BI described here. M2M relationships in CTP 2.4 do not work with composite models. They allow relationships between tables where both columns are non-unique. A relationship can be defined between a dimension and fact table at a granularity higher than the key column of the dimension. This avoids having to normalize dimension tables and can improve the user experience because the resulting model has a smaller number of tables with logically grouped columns. For example, if Budget is defined at the Product Category level, it is not necessary to normalize the Product dimension into separate tables; one at the granularity of Product and the other at the granularity of Product Category.
Tooling
Many-to-many relationships are currently engine-only features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can use the fantastic open-source community tool Tabular Editor to create many-to-many relationships. Alternatively, you can use SSAS programming and scripting interfaces such as TOM and TMSL.
New 1470 Compatibility Level
To use many-to-many relationships, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.
Memory settings for resource governance
The memory settings described here are already available in Azure Analysis Services. With CTP 2.4, they are now also supported by SQL Server 2019 Analysis Services.
QueryMemoryLimit
The Memory\QueryMemoryLimit property can be used to limit memory spools built by DAX queries submitted to the model.
Changing this property can be useful in controlling expensive queries that result in significant materialization. If the spooled memory for a query hits the limit, the query is cancelled and an error is returned, reducing the impact on other concurrent users of the system. Currently, MDX queries are not affected. It does not account for other general memory allocations used by the query.
The settable value of 1 to 100 is a percentage. Above that, it’s in bytes. The default value of 0 means not specified and no limit is applied.
You can set this property by using the latest version of SQL Server Management Studio (SSMS), in the Server Properties dialog box. See the Server Memory Properties article for more information.
DbpropMsmdRequestMemoryLimit
The DbpropMsmdRequestMemoryLimit XMLA property can be used to override the Memory\QueryMemoryLimit server property value for a connection. The unit of measure is in kilobytes. See the Connection String Properties article for more information.
RowsetSerializationLimit
The OLAP\Query\RowsetSerializationLimit server property limits the number of rows returned in a rowset to clients.
This property, set in the Server Properties dialog box in the latest version of SSMS, applies to both DAX and MDX. It can be used to protect server resources from extensive data export usage. Queries submitted to the server that would exceed the limit are cancelled and an error is returned. The default value is -1, meaning no limit is applied.
Download Now
To get started with SQL Server 2019 CTP 2.4, find download instructions on the SQL Server 2019 web page. Enjoy!