// Many to Many in SSAS Tabular

Many to Many in SSAS Tabular

This blogpost will cover the aspects of the many-to-many feature from SQL Server 2016 – including:

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:

Technet

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.

M 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:

Old way result

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:

m2m the new way

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:

m2m filter direction

Remember to select the Filter Direction to « To Both Tables ».

And that is it!

The result without doing DAX formulas:

m2m old way result

HAPPY CODING 🙂