Public preview of many-to-many relationships in Azure Analysis Services

Posted on 10 June 2019

Target availability: Q3 2019

Many-to-many relationships in Azure Analysis Services are based on M2M relationships in Power BI. They allow relationships between tables where both columns are non-unique. Many-to-many relationships were announced for SQL Server Analysis Services 2019 in the CTP 2.4 blog post.

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 normalise 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 normalise the Product dimension into separate tables; one at the granularity of Product and the other at the granularity of Product Category.

ClientCacheRefreshPolicy
Many-to-many relationships are currently engine-only features. SQL Server Data Tools (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.