Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Bi-Directional Cross-Filtering in Analysis Services Tabular 2016 - Part 1


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Attend these FREE MSSQLTips webcasts >> click to register


Problem

With the release of SQL Server 2016, Analysis Services Tabular introduces a very important new feature: bi-directional cross-filtering. This feature allows us to support more complex models much easier without the need of creating extra - often complicated - DAX measures. In this tip, we'll introduce bi-directional cross-filtering and discuss a couple of use cases where you can use it.

Solution

The concept of bi-directional cross-filtering is simple: it allows a relationship to be filtered in both directions. In previous versions, a relationship was always defined as a one-to-many relationship, with the filter direction flowing from the one to the many side.

filter direction in bi-directional cross filtering

In the screenshot above, it means if you filter on the product category, the relevant subcategories will be filtered. However, if you would filter directly on sub categories, the product category table would be unaffected. This behavior made it difficult to implement some scenarios, such as a many-to-many relationship. In most cases, you had to construct special DAX measures using the CALCULATE function.

With bi-directional cross-filtering, you simply enable the relationship to be filtered in both directions. Let's take a look at some examples where we can use this to our advantage. Note that everything described in this tip is applicable to Power BI Desktop as well, as it's basically the same model engine behind the scenes. Currently, Excel 2016 (Power Pivot) doesn't support bi-directional cross-filtering.

Many-to-many Relationships

A many-to-many relationship is characterized by the presence of a bridge table between two other tables. In data warehouses those two tables are typically dimensions, for example a bank account dimensions and a bank account owner dimension (an owner can have multiple bank accounts and a bank account can have multiple owners). Such a bridge table is typically called a factless fact table.

many to many problem with a factless fact table

The AdventureWorks data warehouse sample database contains a many-to-many relationship: an Internet Sales is accompanied with one or more Sales Reasons. A sale can have multiple reasons, but for a reason you can also have multiple sales. In the screenshot you can see the problem: when you filter on reason, the bridge table FactInternetSalesReason is filtered, but the filters do not propagate to the intermediate dimension vSalesOrders. Thus, the fact table is never filtered. The vSalesOrders dimension is a view created in order to link the fact table to the bridge table. It only contains a distinct list of the concatenation of Sales Order header and line numbers. Tabular only supports relationships on single columns, so the sales order header and line numbers are concatenated together using calculated columns in the fact table and bridge table.

sales order header and line numbers are concatenated together using calculated columns in the fact table and bridge table

In Tabular 2012/14 you had no other choice than create extra DAX measures using CALCULATE to solve this issue. Since Analysis Services Multidimensional supports many-to-many relationships out of the box, this use case was a big disadvantage for Tabular models and a reason to keep developing OLAP cubes with SSAS Multidimensional.

Let's find out how we can deal with this issue. The entire model has the following structure:

sample data model for the data warehouse

Unnecessary columns have been removed from the model and columns not needed for the end user are hidden. You can find a link to download the model at the end of the tip. When we analyze the model in Excel, we can see the many-to-many relationship does not work: the count of the sales are repeated for each sales reason; the fact table is not filtered.

the many-to-many relationship does not work: the count of the sales are repeated for each sales reason; the fact table is not filtered

With a simple setting, we can rectify the situation. In the model, we need to set the relationship filter direction to "To Both Tables".

In the model, we need to set the relationship filter direction to "To Both Tables"

When we refresh the Pivot Table, we can see the effect of the setting:

Many to Many succeed

By setting a simple property, we have avoided complex modeling and the creation of extra measures.

Note: it is possible that when you create the model, the SQL Server Data Tools (SSDT) will detect the nature of the relationships and will set relationships automatically to bi-directional.

Conclusion

In this tip, we have shown how the new bi-directional cross-filtering feature of Analysis Services Tabular 2016 has simplified the set-up for many-to-many relationships. In the next part, we'll take a look at other use cases for bi-directional cross-filtering, such as row level security and measures on top of dimensions.

Next Steps
  • If you want to check out the Tabular model shown in the tip, you can download it here.
  • You can find more Analysis Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools