By: Koen Verbeeck | Comments (2) | Related: > Analysis Services Development
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.
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.
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.
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:
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.
With a simple setting, we can rectify the situation. 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:
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.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
