Hello Marco! I have a question regarding the topic. So, isn't it simulation of left join to the fact table in regular relationship and the inner join in terms of limited one if we consider the same operation in sql?
It's hard to say that you have an inner join with a limited relationship, because the total of a calculation includes the items that are invalid but you don't see in the detail.
Hi Marco, excellent content and explanation. Will Vertipaq analyzer spot the "invalid relationship" that is happening behind the "limited relationship" in the model? I would presume it would, since it analyses the difference between the cardinality of one to the other side of the relationship. However, since I do not have a dataset to test it, I would appreciate having your feedback. Many thanks in advance.
Marco great video! So now we understand not to expect a blank row for an RI violation across a limited relationship. But why would Microsoft implement it like that? I have seen questions from auditors about this issue cropping up in a financial report. Why couldn't MS have added the blank row even for a limited relationship? Is the performance hit too severe?
Hi Marco, I have a primary key column that has 1.7 million distinct values and is the highest cardinality column in my model This column is used to join my fact table How to reduce the size of the model as this primary key column is consuming more space
You should reduce the cardinality of that dimension. But if you cannot, you have to pay that price (which is still smaller than a relational database).
It's complex to provide the sample file because it is connected to published Power BI file that wouldn't work if the file is opened outside of the tenant.
Thank you so much, Marco. I really enjoyed this explanation.
Well explained Marco R.! You made it cristal clear to me. Many thanks!
Thanks Marco! Always great content.
Awesome video! How then do we ‘fix’ or work with this in our models? Especially when we can’t explain this technical bit to end users..
Thank you so much for the explanation. May I know in what situations would one want to use limited relationship rather than a regular one?
Hello Marco! I have a question regarding the topic. So, isn't it simulation of left join to the fact table in regular relationship and the inner join in terms of limited one if we consider the same operation in sql?
It's hard to say that you have an inner join with a limited relationship, because the total of a calculation includes the items that are invalid but you don't see in the detail.
Hi Marco, excellent content and explanation.
Will Vertipaq analyzer spot the "invalid relationship" that is happening behind the "limited relationship" in the model?
I would presume it would, since it analyses the difference between the cardinality of one to the other side of the relationship. However, since I do not have a dataset to test it, I would appreciate having your feedback. Many thanks in advance.
Potentially yes, but if it doesn't work submit the issue on github.com/sql-bi/VertiPaq-Analyzer/issues thanks!
Marco great video!
So now we understand not to expect a blank row for an RI violation across a limited relationship. But why would Microsoft implement it like that? I have seen questions from auditors about this issue cropping up in a financial report. Why couldn't MS have added the blank row even for a limited relationship? Is the performance hit too severe?
Basically yes, the performance impact would be too large.
Hi Marco,
I have a primary key column that has 1.7 million distinct values and is the highest cardinality column in my model
This column is used to join my fact table
How to reduce the size of the model as this primary key column is consuming more space
You should reduce the cardinality of that dimension. But if you cannot, you have to pay that price (which is still smaller than a relational database).
Can I get this file?
It's complex to provide the sample file because it is connected to published Power BI file that wouldn't work if the file is opened outside of the tenant.