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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
maartenvanreek
Frequent Visitor

power bi report builder does not detect relations from Fabric Warehouse datasource

As a test with Fabric, I wanted to make a Paginated Reporting with Power BI Report Builder on a Warehouse (WH) in Fabric, where I have stored some tables of the AdventureWorks DWH DB.

In the Fabric WH, I defined the relations between the tables, like this:

 
ALTER TABLE [SalesOrderHeader]  ADD  CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES [Customer] ([CustomerID]) NOT ENFORCED;
GO
 
NB: that 'NOT ENFORCED' clause seems mandatory in Fabric.
 
When I wanted to create a paginated report with the (visual) query editor (so not with SQL), and chose some fields from 2 (related) tables, Power BI Report Builder did not autodetect the relation/make the join. When I did later the same test with a Azure SQL DB as a source, where I also did defined the relations, without the 'NOT ENFORCED' clause, it worked, so the PBI RB detected the relation/made the join.

So I guess the problem is that Power BI Report Builder can only detect relations (do automatic joins) if the relations are without the 'NOT ENFORCED' clause, and so a Fabric Warehouse is a problem then if you want to let business-users (without a good knowledge of the DB and the relations in it ) create their own paginated reports using the (visual) query editor. 
Or do I miss something ?
 
2 REPLIES 2
v-junyant-msft
Community Support
Community Support

Hi @maartenvanreek ,

I'm sorry I wasn't able to reproduce your situation. Can you provide some details or screenshots so that we can help you better?
And about "NOT ENFORCED", enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraints you must include the NOT ENFORCED as part of your statement.
I guess these documents may help you:

https://stackoverflow.com/questions/49941101/how-to-set-any-column-as-primary-key-in-azure-sql-data-warehouse
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-table-constraints


Best Regards,
Dino Tao

hi Dino Tao, thanks for your answer, I include 2 screenshots :

-1: Adv.Works (Azure) SQL DB with a relation between 2 tables

-2: Report that selects fields from these 2 tables, in which join is automatically detected. This does not happen if the DB tables are in Fabric (-warehouse) although the PK/FK-relations are defined (but not enforced)

 

--1

maartenvanreek_0-1709511002303.png

 

--2

maartenvanreek_1-1709511071537.png

 

--

I wonder if this issue would not appear if it was possible to create a enforced PK/FK-relation in a Fabric warehouse?

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors