This blog summarises the (many) new features in Analysis Services 2016 (Tabular)
Part four of a five-part series of blogs

Microsoft have made lots of improvements to SSAS (Tabular model) - we particularly like the new DAX editor! This blog lists all of the most interesting changes, divided into 4 separate sections.

  1. What's new in Analysis Services 2016 (tabular model)?
  2. Improvements to editing DAX in SSAS 2016 (Tabular Model)
  3. Changes to the DAX language in SSAS Tabular 2016
  4. Bi-directional / many-to-many relationships in Tabular 2016 (this blog)
  5. Other improvements to Analysis Services 2016 Tabular

This is part of a wide-ranging blog explaining the new features in every part of SQL Server 2016.

Posted by Andy Brown on 08 July 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Bi-directional / many-to-many relationships in Tabular 2016

For complex models, this will be a life-changer!  To see how it works, consider this simple model:

Two table model

We want to use the SpeciesId field to join the two tables together.

If you want to try this yourself, you can download this Excel workbook, then import the data from its two worksheets into a new tabular model.

Here are the contents of the two tables (each species contains one or more animal types)

Species table Animal table
The Species table The Animal table

Simple relationships

As always in SSAS Tabular, you can create a relationship by clicking one field and dragging it onto another:

Creating a relationship

As always, SSAS will sort out which way round the relationship should go.

Analysis Services works out that the species id is unique in the left-hand table but not in the right-hand one, and creates a one-to-many relationship:

One-to-many join

So far, so familiar ...

Making a relationship bi-directional

You can double-click on any such relationship to edit it:

To both tables

Set the relationship to filter both tables.

What this means

To see the effect of this, consider some pivot tables.  For both types of relationship, you can create a pivot table counting the number of animals per species:

Normal counting

This is completely normal behaviour: count how many animals (children) there are for each species (parent).

 

Where things get very different is when you try to show the number of parents for a child field.  Here's what you'd get with a normal one-directional relationship if you try to show the number of species for each number of legs:

Child to parent

Because normally the relationship is only one-way, you'd see something fairly meaningless.

 

With a two-directional relationship, you'd see this: 

Bi-directional pivot table

There is one species which has no legs, one which has two legs and three which have four legs. The numbers don't add up because crocodiles and snakes have different numbers of legs, but are both reptiles.

 

The implications: many-to-many relationships

One of the implications of this is that it's MUCH easier to model many-to-many relationships.  Consider this relationship diagram in a model:

Normal relationships

I haven't included the data for this, but the tblCast table links films and actors (it models the fact that one film can contain many actors, but one actor can also appear in many films).

If you create pivot tables showing the number of films per actor, or the number of actors per film, you'll get this:

Films per actor Actors per film
Number of films per actor Number of actors per film

However, I'll now make the two relationships bi-directional as shown above:

Two bi-directional relationships

Here I've chosen To both tables for each relationship's filter.

This now gives the following pivot tables:

Films per actor 2 Actors per film 2
Number of films per actor Number of actors per film

This has saved the messy attempts needed to model many-to-many relationships in SSAS Tabular 2012/2014!

This blog has 0 threads Add post