BUG Assume Referential Integrity

[mhotek]

Yes, I read the article on going to the support site and submitting a bug report.  I'm not a Pro user, so I don't get free support and I'm not about to charge my credit card to determine this is a bug and then have the charge refunded.

The Assume Referential Integrity option in the model does not work.  I have 2 tables OrderHeader and Payment.  They have a foreign key defined as OrderHeader.OrderID = Payment.OrderID.  This foreign key is active and enforcing the relationship, so it is impossible to have a value in Payment.OrderID that does not exist in the OrderHeader table.  However, when I select Assume Referential Integrity, I get an error back that there are rows in the Payment table that do not exist in the OrderHeader table.  This is false.

The code that is being used is invalid to perform this type of check.  It ran the following 3 queries:

SELECT

COUNT_BIG(*)

AS [a0]

FROM

(

(select [OrderID] as [OrderID],

 --additional columns deleted

from [dbo.][OrderHeader] as [$Table])

)

AS [t1]

SELECT

COUNT_BIG(DISTINCT [t12].[OrderID])

AS [a0]

FROM

(

(select [PaymentID] as [PaymentID],

[OrderID] as [OrderID],

 --additional columns deleted

from [dbo].[Payment] as [$Table])

)

AS [t12]

SELECT

COUNT_BIG(*)

AS [a0]

FROM

(

(select [OrderID] as [OrderID],

 --additional rows deleted

from [OnlineSales].[OrderHeader] as [$Table])

)

AS [t1]

It appears that instead of checking to see if there are actually values in the Payment table that do not exist in the OrderHeader table, it is instead doing a pair of counts, comparing the values, and if they do not match, saying that there is no enforced referential integrity.  This is invalid.  This set of queries is saying that there MUST be a payment for EVERY value of OrderID.  It does NOT say, there must be an OrderHeader.OrderID value for every Payment.OrderID.

[v-haibl-msft]

@mhotek

I try to create two tables with relationship in SQL 2016 as you describe. And I can enable the Assume Referential Integrity option successfully.

Please see my screenshots below and let me know I miss something here. I’m using the Mar 2017 version of Power BI Desktop.

 

Best Regards,

Herbert

[mhotek]

[mhotek]

I'm also using the latest version of PowerBI Desktop

[mhotek]

Based on the queries hitting my database when it attempts to validate the referential integrity, PowerBI is only allowing cases where every parent has a child which is not the same as every child has a parent.  What makes this even more baffling is that it is not consistent.

In the case of my OrderHeader to Payment failing, because we do have orders where there is no corresponding payment.  But, it has no problem passing validation between my Calendar and OrderHeader where there are definitely dates in my Calendar table which do not exist as OrderDate in my OrderHeader.  So, within my model, I wind up with three situations happening.

1. Where every parent does not have a child allowing me to specify Assume Referential Integrity
2. Where every parent does not have a child does not allow me to specify Assume Referential Integrity
3. Where every parent does have a child allowing me to specify Assume Referential Integrity

[mhotek]

[ismith23]

I'd like to report a BUG in the 'assume referential integrity' functionality as well.  Referring to one dimensional table and one fact table within a star schema, I selected the referential integrity option since it seems to be the sole equivalent to an INNER JOIN.  However, PowerBI throws an error suggesting there are values in the fact table not found in the dimensional table.  Looking into my data, this is not the case: all values populated within the fact table may be found within the dimensional table.  Therefore, I would like to report a BUG.

[CraigPBI]

Yes this is a bug and very easy to prove.  You just have to do a SELECT on your underlying tables to show there is no missing ID's.  Strange as it doesn't always throw an error.  Maybe is a case of PBI not ready the full table, but instead just reading a subset of data?

I'm new to Power BI so will have to review how to raise a bug.

[jellis007]

This seems to be a problem for me as well. I have two tables I have tried to link with both Single and Both cross filter directionality. Both have failed with the error that there are "rows in the 'TransactionBasket' table without a matching row in the 'TransactionTable' table". However, just like the OP, this is patently wrong. First, the key being used to link the tables 'TransactionId' is a FK. Second, the below query returns zero rows:

SELECT *
FROM Transactionbasket tb
WHERE NOT EXISTS (SELECT 1 FROM TransactionTable tt WHERE tt.TransactionId = tb.TransactionId)

It's been a year since this was originally reported, so I would be really happy if I'm being an idiot somehow and this was fixed. Has this been addressed?

[Anonymous]

I can confirm I am also getting this issue.

I have written queries to prove:

1. The "many" side (referred to as "from" side in Microsoft Documentation) has no null values in the join column
2. All values can be found in the "one" side ("to" side in documentation) that are being used to join to the "many" side