Share via

What’s new for SQL Server 2019 Analysis Services CTP 2.4

  • Article

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!

Comments

  • Anonymous
    April 07, 2019
    Any updates to calculation groups? (especially MDX support)? I've been proof-of-concepting a model using calculation groups and loving it so far, but lack of MDX support is of course limiting what I can do with it thus far :)
    • Anonymous
      April 25, 2019
      Hopefully in the next CTP if all goes according to plan ...
  • Anonymous
    April 20, 2019
    Hi Christian,Is it possible to use "Many-to-many relationships" and 1470 Compatibility Level in Azure Analysis Services? When do you estimate to have it activated if not yet? Which Visual Studio should we use to leverage this feature with AAS? Thanks in advance !
    • Anonymous
      April 25, 2019
      Hopefully in the next CTP if all goes according to plan ...