It is a weak point from the modeling point of view, because it might introduce ambiguity. The weak point of canonical many-to-many relationships is the bidirectional relationships. In order to distinguish between the two models, we call the first one the “canonical” model, whereas the second model presented is the “new” model. Before going into the details of the tests, we look at a few considerations. In order to prove the above statement, we obviously need to perform tests. Therefore, the new model is an option that should be tested thoroughly before you move it into production. It all depends on the tables involved in the specific query. This second model produces different query plans that can be better in some scenarios, and not as good in other scenarios. That said, is it also better or at least comparable in terms of performance? Unfortunately, the quick answer is “it depends”. A filter from either Customer or Sport is applied first to CustomerSport and is then moved to Sales through the many-to-many cross-filter relationship.īecause of the absence of bidirectional relationships in this model, the model is surely more flexible than the previous one. This second model is visually more appealing and it does not require bidirectional relationships – which is most relevant. We have recently been discussing another possible option to model this scenario by using limited many-to-many cardinality relationships. This is the way we have been teaching many-to-many relationships over the years. By using this model, you can slice Sales by Sport thus obtaining the usual many-to-many non-additive behavior. The canonical way to model this relationship is shown below.Ī filter from Sport flows to CustomerSport, then it goes through the bidirectional relationship to Customer to finally reach Sales. Because each customer may play multiple sports, and each sport may be played by multiple customers, the natural relationship between customers and sports is a many-to-many relationship. We assigned to each customer a certain number of sports that they play.
Therefore, we created a many-to-many relationship between the Customer and the Sport tables. In the Contoso database, there are no many-to-many relationships. In this article, we first provide a description of the two techniques, and then we proceed with the performance analysis of both solutions, to provide insights on which technique to use and when. Both come with advantages and disadvantages that need to be understood in depth in order to make the right decision. The choice between the two models is not easy to make. If you are facing this situation, you can use a different modeling technique based on a limited many-to-many cardinality relationship, which would work even when it is set as a unidirectional relationship. Nonetheless, one of the reasons a bidirectional relationship cannot be created may be ambiguity. In this scenario, using a bidirectional filter relationship is the suggested solution. There actually is one scenario where bidirectional relationships are useful: when you need to create a model involving a many-to-many relationship between dimensions.
There are 3 ways to aggregate a dimension from a fact if you don't use bi-directional filters.Our readers know SQLBI’s position on bidirectional relationships: they are a powerful tool that should be used with great care or avoided altogether in most scenarios. Measure 2: Credits = SUMX('Practical Course Lecturers', CALCULATE(SUM(Courses), CROSSFILTER('Practical Courses', 'Practical Course Lecturers', Both),CROSSFILTER(Courses, 'Practical Courses', Both) )) Measure 1: Students = CALCULATE( SUM('Practical Courses'), CROSSFILTER('Practical Courses', 'Practical Course Lecturers', Both)) Here is how you achieve your desired result.I have corrected this in my example below. Your sample data doesn't match your results. That is why " it seems cumbersome to set two cross-filters to connect the fact table with the top-level table"
0 kommentar(er)
