Database Relationship - Refresh

[pgree]

Hello, I'm having problems with PBI recognising relationships that exist in a database I'm connected to.

I initially followed these steps.

1. Launched Power PI and clicked on the Get Data dropdown, then clicked More…
2. Selected Azure from the left hand panel, then Azure SQL Database, then Connect
3. Copy and pasted the relevant credentials into the Server and Database fields, and selected Import for the Data Connectivity mode, then click OK
4. Selected Database from the left hand panel and enter the username and password from my credentials, then hit Connect
5. The database tables appeared in a list and I chose them all and clicked Load.

Unfortunately the relationships between the tables did not flow through, and PBI could not autodetect them, so I tried my best to manually build them using API information provided by SyncHub the data warehouse vendor.  While this has partially worked, the data model has 32 tables and some of the relationships are complex (for me), and as a result, I am getting error messages on some relationship attempts say "you can't create a direct active relationship . . .  ", so I'm obviously doing something wrong!

I went back to SyncHub to ask for help.  I was told that while the relationships have always existed, they would create actual foreign keys in my data warehouse which they felt PBI could then read.  I then refreshed my PBI model, ran autodetect relationships and nothing chnaged. I then ran through the initial setup from scratch as outlined above, and again the relationships have not been detected.

The warehouse provider subsequently generated a data model using SQL Server Management Studio which definately shows the relationships exist.  Their best guess is that perhaps PBI is caching the data structure. The other thing that springs to mind is that SyncHub do not enforce foreign keys, but we can't imagine PBI ignores them on that basis.

Finally, in PBI I went into File > Options and Settings > Options > Current File > Data Load > Relationships, and ticked on all three options.  I refreshed the model, chose autodetect relationships again and nothing changed.

SyncHub have suggested that I now create a new warehouse and start from the very beginning:

• deactivate the current connection
• add a new one
• before opening PBI, they will recreate the keys
• then connect in PBI.

The new warehouse will be the same database, but a different schema, so they are hopeful PBI will run a fresh check.

Is there a better, potentially easier way?

Thanks for any guidance or assistance.

[Anonymous · Accepted solution]

Hi @pgree 

Please correct me if I wrongly understood your question .

There are three settings related to relationship in the system settings .

• Import relationships from data sources on first load .
• Update or delete relationships when refreshing data
• Autodetect new relationships after data is loaded .

If the data you import from the database already has a relationship, you mainly check the first parameter, and then import the data, you can see the relationship between the tables .

If you import the data first and do not check the first parameter, then the relationship between the tables will not be synchronized to the power bi. At this time, you can automatically create the relationship through the Autodetect in the Manage relationships .

Therefore, before importing relational data, you should confirm whether the first parameter is enabled. In this way, the relationship from the database can be synchronized.

More info about relationships can refer to the link

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Best Regards

Community Support Team _ Ailsa Tao

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