Table expansion in a direct query source happens only if "assume referential integrity" is enabled?

Roxana Crisan 0 Reputation points
2024-06-06T15:00:36.9366667+00:00

I was going through the learning material, and this was mentioned in the "Create Power BI model relationships" module, Unit 5:

for DirectQuery tables it’s done in the native query that’s sent to the source database (as long as the Assume referential integrity property isn't enabled)

it is mentioned isn't, but I would have expected assume referential integrity to have been enabled, so the join is more performant.

This question is related to the following Learning Module

Microsoft Power Platform Training
Microsoft Power Platform Training
Microsoft Power Platform: An integrated set of Microsoft business intelligence services.Training: Instruction to develop new skills.
196 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Syed Saleem Peera 1,795 Reputation points Microsoft Vendor
    2024-06-06T16:24:03.1033333+00:00

    Hi Roxana Crisan,

    Thanks for reaching out to us on the Microsoft Q&A forum.

    Effect of Assume Referential Integrity Property

    • Enabled (Assume Referential Integrity): If this property is enabled, Power BI assumes that the referential integrity is maintained in the source database. This allows Power BI to use inner joins instead of outer joins, which can lead to more efficient queries and better performance.
    • Disabled (Do Not Assume Referential Integrity): If this property is not enabled, Power BI does not assume that referential integrity is maintained. Consequently, it uses outer joins to ensure that all records are included in the results, which can be less efficient but ensures data completeness.

    If you are still facing any issue, please let us know in the comments. We are glad to help you.

    If the information is helpful, please Accept Answer so that it would be helpful to community members.

    Thank you.