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.
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.
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 ?
Hey, There would be no intermediate table when we use * to * relationships, i did not get a clear picture after 10:55, is he trying to say if distinct values are small in size then we should to go with direct * to * relationships otherwise in large size we should use intermediate table or bridge table? Please explain me if you got my point Thanks!
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! =)
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!
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.
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!).
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.
Sir, I have question I have customer dim connect to salesfact Supplier table connect to salesfact If i want to analyse sales by customer and supplier name. In this case it called as one to one relation as the above example. Both foreign keys are in fact tabke and each customer and supplier is have no relation from dim tables till confused. Can you clarify this
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
![SAFEPLANET - ดาวเคราะห์ [ THE PLANET ]](http://i.ytimg.com/vi/4X1XL3I_k3c/mqdefault.jpg)
I love the whiteboard series 💓
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.
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)
Thank you!
Perfect, thanks
Solution 2 is very cheap because, who is going to create a brand-only budget... real life examples are what we need, Mr. Russo.
Thank you sir
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.
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 ?
10:55 for when to use built in * to * instead of own table. It depends on size of intermediate table.
Hey,
There would be no intermediate table when we use * to * relationships, i did not get a clear picture after 10:55, is he trying to say if distinct values are small in size then we should to go with direct * to * relationships otherwise in large size we should use intermediate table or bridge table? Please explain me if you got my point
Thanks!
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! =)
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!
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.
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!).
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.
Sir, I have question
I have
customer dim connect to salesfact
Supplier table connect to salesfact
If i want to analyse sales by customer and supplier name. In this case it called as one to one relation as the above example. Both foreign keys are in fact tabke and each customer and supplier is have no relation from dim tables till confused.
Can you clarify this
That usually works out of the box without requiring any additional relationship.
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 🤣