Importing relationship from data sources

[Anonymous]

Hi,

I was asked a way to import relationship directly from data sources, without having users manually defining relationships.

In Options >> Current file >> Data Load 

I can choose autodetect and import, while the first one seems to be based only on column names and data type, is there any documentation about how the latter works at low level?

My data source supports foreign keys constraints (though not enforced) yet I wasn't able to import relationship

automatically; now I am wondering if it depends on power query connector.

Indeed checking Power Query connectors documentation, I noted that all RDBMS connectors support as advanced features

• relationship columns
• navigate full hierarchy

For instance 
Power Query Azure SQL database connector - Power Query | Microsoft Learn

Power Query PostgreSQL connector - Power Query | Microsoft Learn

Power Query Oracle database connector - Power Query | Microsoft Learn

As MPP Snowflake supports relationship columns.

Power Query Snowflake connector - Power Query | Microsoft Learn

My data source doesn't support these features instead, can it be the reason why I am not able to see relationship?

[Anonymous]

Hi @Anonymous ,

This is usually determined by the connection mode. Only reports that connect to the data source through Live Connection, relationships are obtained from the data source, because it is connected to a complete data model, you can't data model it in Power BI Desktop. Import mode and DirectQuery mode are just fetching data, and you need to establish relationships in Power BI Desktop.

 Power BI Connection Types: DirectQuery, Live, or Import? Tough Decision! - RADACAD

Best Regards,

Stephen Tao

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

[Anonymous]

Hi @Anonymous,

thanks for your answer. My data source (Azure Databricks) supports import and direct query mode, and using Unity catalog now supports informational foreign keys.

As for linked article, in the difference between live and direct query section I see:

• "With DirectQuery you can still configure relationships in some cases. With Live Connection, you have no relationship tab."

• "No Power Query in Live Connection"

---

Now, the second one would be a problem for my use case. 
As for number one, it says you can't edit relationship with live but it doesn't state that you can't import relationship
using direct query.
Indeed if my source is let's say Azure Sql, choosing direct query and selecting relationship columns, relationship are available at load time without me doing anything. Then I can choose to get rid of it, but that would be my choice.
With Databricks instead, such relationships aren't available at load time and so the only option is to start modeling manually.