Many-to-Many Relationships - The Whiteboard #12
Vložit
- čas přidán 27. 12. 2022
- The many-to-many cardinality relationship in DAX is not what a many-to-many relationship is for the rest of the world...
Learn abstract DAX concepts in a more interactive way with "The Whiteboard" series. Read more: www.sqlbi.com/blog/marco/2022...
#thewhiteboard - Věda a technologie
Perfect, thanks
I love the whiteboard series 💓
Thank you!
Marco, this is a great serie of Whiteboard-sessions !!! I would appreciate if you could add a session about the do's and dont's of bidirectional relationships.
Thank you sir
3:00- bridge table type 1 (new information) (one-many--many-one)
7:30- bridge table type 2 (no new information) (many-one-one-many) (won't slow down your model if there are only a few hundred rows)
7:45- why does Product to Brand need to be bi-directional? We have this second kind of bridge table in real estate: a borrower can have multiple collateral, and conversely, a collateral can be connected to multiple loan numbers (ie. pari-passu loans). So you need an intermediary loan-to-property code table between the Loan and Property tables. This is one-to-many-to-many-to-one, and it's uni-directional and it works just fine for Loan to filter Property.
Solution 2 is very cheap because, who is going to create a brand-only budget... real life examples are what we need, Mr. Russo.
10:55 for when to use built in * to * instead of own table. It depends on size of intermediate table.
Hi Marco,
You guys always deliverying great stuff, thanks a lot!!!
But I've a doubt.. In this model, how can I distinct count the Costumer on my fact table, is it possibel doing that? Since I dont have a Costumer FK at my fact tabel..
In advanced, thanks a lot for the help! =)
Thank you for your great explanation, if I have a table with some of codes and its category and another table of the codes and its balance and stock in different stores and also category column (not all the codes are existed in the second table) now I want to create a relationship between category column in the two table (but power bi tell me it should be unique category value in both table and that is not my case ), I have tried middle table many to one one to many but it does not work ?
Thanks for the video. Can you please explain how does suggested data modelling technique help in ensuring account balance is not considered twice while calculating totals in the very first example you gave?
It's the nature of relationships in Tabular, they don't "join" tables as you would expect in SQL, they transfer a filter. The result is like a join in simple cases, but you never "duplicate" data this way (but you can still write bad DAX code producing wrong result, of course!).
Hi , Your all videos are very impressive and I am new to this Power BI, So please explain in coming videos how to create or Cohort analysis or retention analysis.
It's a very generic description. Look at www.daxpatterns.com for examples of specific algorithms implemented in DAX.
Hi Marco,
I was wondering if a change in the granularity of the fact table, to eliminate the many-to-many, would benefit performance or not?
To try to explain, in the first example, if we stored an account transaction multiple times in the fact table, one for each customer of the account, that would transform the model to a simple star model. With no need to have a bridge CustomerAccount table, we would just create direct relationships from the fact (which now stores data in a Customer X account granularity) to the two dimensions.
On the other hand, we'd also need to change the DAX of most measures as they would now become more complicated (and non-additive). If for example we wanted to count the number of accounts with transactions within a given period, we'd need a DISTINCTCOUNT instead of a COUNT.
Would you expect such an approach to benefit or worsen performance? I'm mostly interested in Direct Query settings nowadays - which is perhaps more complicated- as my current customer likes direct query a lot.
Buon anno!
Tell your customer that DirectQuery is very expensive. Same data, you get slower performance, so in order to achieve the same performance of imported models, they have to spend more. It's their choice, of course!
In general, duplicating data in the fact table is dangerous because you make it easier to get wrong results if you don't apply the right calculation on top of the data. The rule of thumb is to increase complexity for optimization if and only if you can demonstrate there is an overall benefit. Don't make blind assumptions, as they may be wrong and you spend more for less.
Thanks Marco (or Alberto), no risk re the calculations, I'm developing them, so no chance users with limited knowledge messing up things there.
Tried already telling them about the benefits of import mode, will cite you as well - better chances they'll listen this way 🙂
I'll run some test on the performance difference of virtual many 2 many vs. changing the granularity on the fact table on Direct Query myself to see what's best for their specific case.
Thanks for your reply, happy 2023!
What are the pro’s and con’s of creating the Brand table in DAX vs Power Query?
No differences - DAX guarantees that the Brand table is always synchronized, whereas if the definition is in Power Query you might lose synchronization if you don't refresh all the tables.
Min 9.37...please correct me if i'm wrong but from my understanding all tables (Product, Brand and Budget) have Single (not Both / Bi-Directional) relationships...is that true
At the second thought, Brand and Budget tables are related by Bi-Directional (= Both) relationship...is that true
Min 7.49...i think that's the answer: The Bi-Directional relationship is between the Product & Brand tables
Correct.
at 6:30, that is how my models look like 🤣