Public preview of many-to-many relationships in Azure Analysis Services
Published date: 10 June, 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.
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.