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