// Many to Many in SSAS Tabular
This blogpost will cover the aspects of the many-to-many feature from SQL Server 2016 – including:
- Prerequisites
- The old way
- The new way
This post is based on data from the AdventureWorksDW2012 database.
Prerequisites
In order to test the new many-to-many feature from SQL Server 2016 SSAS Tabular you’ll need to download the latest CTP from Microsoft – it can be found here:
Also you’ll need the Visual Studio 2015 and the add-in for Business Intelligence:
https://msdn.microsoft.com/en-us/library/mt204009.aspx
Choose the SSDT October 2015 Preview in Visual Studio for download.
After a bit of waiting with the installation, you are ready to test the functionality.
The old way
Before showing the new (and for me right way) to do the many-to-many in SSAS Tabular, let me first show you how it was done prior to SQL Server 2016 CTP 3.0.
Thanks to the two brilliant guys from SqlBI Marco Russo ([T][2],[L][3]) and Alberto Ferrari ([T][4],[L][5]) we’ve had below approach for quite a while now.
First of all you need to build a bridge table with the column that links the two tables and build a model like below illustrates.
The m2mKey is a concatination of the SalesOrderNumber and SalesOrderLineNumber as the Tabular still does not have the ability to handle two joins at the same time.
Then all measures that need to take the DimSalesReason into account needed to be rewritten with some DAX coding:
Sum of UnitPrice:=CALCULATE(SUM([UnitPrice]);vBridgeSalesReason)
Then the output will look something like this:
The new way
With the CTP 3.0 release and the SSDT addon for Visual Studio 2015 now this get’s as easy as 1,2,3.
First of all, it is now possible to build a datamodel directly without any bridge tables like this:
Note the highlighted area – here you can see the many-to-many relationship. This is modelled when creating the relationship in the model like this:
Remember to select the Filter Direction to « To Both Tables ».
And that is it!
The result without doing DAX formulas:
HAPPY CODING 🙂