Many-to-many in SSAS Tabular

Posted on Posted in Ikke kategoriseret

m2mWith the release of SQL Server 2016 CTP 3.0 also comes the ability to test the Many-to-Many functionality within the 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:

http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-community-technology-preview-3-0-is-available.aspx

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,L) and Alberto Ferrari (T,L) 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.

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

m2m_oldway_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_newwayNote 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_filterdirectionRemember to select the Filter Direction to << To Both Tables >>.

And that is it!

The result without doing DAX formulas:

m2m_oldway_result

HAPPY CODING 🙂

2 thoughts on “Many-to-many in SSAS Tabular

  1. Great!
    Does this work with DirectQuery also?
    If yes, then is there any imporvement on DirectQuery Many-to-many SQL performance using this technique?

    1. H,

      Thank you for your comment and reply. I’m sure that the DirectQuery methology will work here to. Then each partition could point to each individual table containing data. I think that would make an impact on performance – if the relational database delivers data fast enough.

      If you try this approach, can you then reply and let me know how it works out?

      Thanks
      Brian

Leave a Reply

Your email address will not be published. Required fields are marked *