Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
timatgca
New Member

Postgresql: same schema and user: FK relationships detected on v10 postgresql, not on v12 postgresql

I have two servers. They are deployed with ansible scripts, with an identical schema, identical postgresql user.

The only difference is that one is an older VM and uses postgresql v10 (a docker container) and one has the postgresql v12 docker container.

The schema has a carefully constructed set of foreign key relationships to help with automatic detection of relationships.

On connection and import to Power BI desktop, the data stored on the v10 postgres works wonderfully well: all table relationships are detected. On the v12 database, no relationships are detected. This happens regardless of import or Direct Query. 

 

The schema is managed by django's ORM, which means the foreign key column names have _id suffixed at the end, so the foreign key columns don't have the same name as the unique column in the parent table.

Example: on the order header, we have order_guid as the unique column. The foreign key is order_guid_id

This can not easily by changed. It shouldn't matter, because the foreign keys are defined. It seems that there is a bug with the postgresql driver in Power BI desktop. I can revert to postgresql v10, so it's not the end of the world.

 

 

This is version info for Power BI Desktop

Version: 2.90.782.0 64-bit (February 2021)

 

This is power bi desktop

2 REPLIES 2
timatgca
New Member

Update: it is a permission issue, I was wrong about the postgresql version. The user (role) I have created for access needs to have superuser permission before related tables are recognised. Initially I gave the user select access to all tables, and ALL privs on the public schema, and even ALL privs on the database. But only when I added superuser to the role were related tables recognised. This seeems crazy.

Hi @timatgca 

Did you use import connection to get data from postgresql?

You can try postgresql connection or ODBC to get data from postgresql.

For Reference: PostgreSQL To Power BI: 3 Best Ways To Connect

What does the role mean, roles in RLS or other?

Could you share a screenshot of your issue to me? 

And you can show me the result you want.

 

Best Regards,

Rico Zhou

 

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.