MySQL foreign keys not automatically detected and related in powerbi

[william_analyst]

Hi, 

I've been stuck with this problem for the past few days where the foreign key exists as showed in the SQL table schema. However, when the tables are imported to powerbi, some relationships are not automatically formed. I initially thought that it was due to Many-to-Many relationships only, so I transformed these to 1 to many or 1 to 1. This did not solve the issue of powerbi not being able to interpret the relationships formed in SQL. 

I've gone through so many iterations with chatgpt, but none of the solutions works. As it stands, the foreign key relationships form correctly within MySQL.

GOAL in auto relationship: I want these to automatically form based on the relationship structure defined in the SQL schema because I want users that import this data to have as little work to do as possible in figuring out the relationships by themselves. The default schema should help users to only import this data and analyse it directly instead of having to sort the relationships out. 

Issue: audits to audittemplates 

ALTER TABLE audits
ADD CONSTRAINT fk_audits_template_to_audittemplates
FOREIGN KEY (template) REFERENCES audittemplates(couchdbid)
ON DELETE SET NULL;

Really appreciate if I can get some advice on how to solve this. 

[william_analyst · Accepted solution]

Solved: Used primary to connect and now it works

[parry2k]

@william_analyst I wouldn't rely on auto relationship, full stop. I rather spend time making users understand the relationships and how it work. If someone is working on Power BI models and don't want to spend time on looking at the relationship then something is wrong. This is the key to any successful Power BI project and rather spend time on looking these things are first place than relying on the auto relationship. Good luck!

[parry2k]

@william_analyst As a best practice you should manually set the relationship instead of auto-relationship. The wrong relationship can lead to unexpected results so it is better to do it manually and make sure the correct columns are used in the relationship and the relationship type is correct.

[william_analyst]

Hi @parry2k  , 

Thanks for the quick comment! 

So I want these to automatically form based on the relationship structure defined in the SQL schema because I want users that import this data to have as little work to do as possible in figuring out the relationships by themselves. The default schema should help users to only import this data and analyse it directly instead of having to sort the relationships out. 

[uzuntasgokberk]

Hello @william_analyst,

Relationshipt not automatically formed is better. Sometimes it creates relationship automatically which we would not like to(İf in the setting auto relationship is open). I don't understand what exactly you would like to do but shoudn't be thinking relationship is same as merge. Relationship is like a filter context.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[william_analyst]

Hi @uzuntasgokberk , 

Wow! Thanks for the quick comment! 

So I want these to automatically form based on the relationship structure defined in the SQL schema because I want users that import this data to have as little work to do as possible in figuring out the relationships by themselves. 

[uzuntasgokberk]

Hello @william_analyst,

İt is not recommend way. Even Power BI makes relationship automatically that can be wrong relationships sometimes. Therefore, this is the best way if you make relationship manually. Because you know where you made relationship before creating reports.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.