Hi Marco, When there is a dimension table (for instance customers) with many columns that have attributes for that customer, each customer can only have 1 of the attributes from each column. Are there risks to keeping these columns in the same table due to Auto-Exist? If you wanted to filter by different tag combinations in the customers this could mess up calculations, therefore separating the columns into 1:1 relationships may be necessary? Thanks, Josh
Not sure why this should be an issue for your scenario. Also consider the latest changes to Autoexists: pbidax.wordpress.com/2024/10/22/understanding-the-impact-of-value-filter-behavior/
I’m wondering whether a 1-1 relationship would be okay in following scenario: Chart of account table. I have three hierarchies on the table (balance sheet, P&L Legal, P&L Management). Each hierarchy requires about 20 level fields (key + caption). I can model this as 1) a very wide table with all the level fields; or 2) four tables, one simple chart of account table, and three coa/hierarchy tables. What to do? Anyone has a preference? Currently I’m using a single very wide table. Just wondering whether for master data tables with parallel hierarchies (eg profit centers) it would make sense to factor out the hierarchy level fields using a 1-1 relationship.
You introduce a performance penalty with the relationship, but probably the dimension is not too large. Ideally, a single table is better. But if you don't like to use folders to organize the many fields available, you can use the approach described if it works well for your scenario.
Would you be willing to do a video on how to resolve two fact tables connected to one dimension table, but one fact table does not connect to several other dimension tables. As of now I only could connect the facts tables with a many to many pairing..which worked. But this cannot be best practice can it? I also see no way to connect the other dimension tables to the one fact table ins sql. The reason this is relevant is because I need to do a calculation using both fact tables.
Srr bro but i have to say this. Please stop using so many bodylanguages when u try to say something. I really believe you have something usefull to say, but i keep getting distracted by ur overexagerrated bodylanguage. Chillax bro, dont be so scared that people won't understand you. Peace.
Unfortunately, I was on a chair. When I stand up, I also walk back and forth, increasing the noise in the communication! Sorry, it's hard to reset to the default factory settings now. Peace and love!
Oh thank goodness - i spent 8 minutes wondering why they were two separate tables in the first place :D
Great video! Should do a whiteboard session on circular dependencies in the future
Good Explanation. Thank-you
Great! Thanks a lot Marco, this was just what I needed!
It (almost) always comes down to a good model.
I use 1-to-1 relations to extend a table from a published dataset (accessed via a direct query connection) with additional fields
It makes sense for composite models, indeed.
Hi Marco,
When there is a dimension table (for instance customers) with many columns that have attributes for that customer, each customer can only have 1 of the attributes from each column. Are there risks to keeping these columns in the same table due to Auto-Exist?
If you wanted to filter by different tag combinations in the customers this could mess up calculations, therefore separating the columns into 1:1 relationships may be necessary?
Thanks,
Josh
Not sure why this should be an issue for your scenario.
Also consider the latest changes to Autoexists: pbidax.wordpress.com/2024/10/22/understanding-the-impact-of-value-filter-behavior/
Very nice 👍👍👍👍🙂
Why did it use two FKs instead of only one ProductKey?
I’m wondering whether a 1-1 relationship would be okay in following scenario:
Chart of account table. I have three hierarchies on the table (balance sheet, P&L Legal, P&L Management). Each hierarchy requires about 20 level fields (key + caption). I can model this as 1) a very wide table with all the level fields; or 2) four tables, one simple chart of account table, and three coa/hierarchy tables. What to do? Anyone has a preference? Currently I’m using a single very wide table. Just wondering whether for master data tables with parallel hierarchies (eg profit centers) it would make sense to factor out the hierarchy level fields using a 1-1 relationship.
You introduce a performance penalty with the relationship, but probably the dimension is not too large. Ideally, a single table is better. But if you don't like to use folders to organize the many fields available, you can use the approach described if it works well for your scenario.
Would you be willing to do a video on how to resolve two fact tables connected to one dimension table, but one fact table does not connect to several other dimension tables. As of now I only could connect the facts tables with a many to many pairing..which worked. But this cannot be best practice can it? I also see no way to connect the other dimension tables to the one fact table ins sql. The reason this is relevant is because I need to do a calculation using both fact tables.
Wait for the next Whiteboard episode about many-to-many relationships! In less than two weeks...
Srr bro but i have to say this. Please stop using so many bodylanguages when u try to say something. I really believe you have something usefull to say, but i keep getting distracted by ur overexagerrated bodylanguage. Chillax bro, dont be so scared that people won't understand you. Peace.
Unfortunately, I was on a chair.
When I stand up, I also walk back and forth, increasing the noise in the communication!
Sorry, it's hard to reset to the default factory settings now.
Peace and love!