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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
coreyw
Helper I
Helper I

Why can't Desktop automatically see relations between OData v4 entities?

Hi All,

 

I have two OData entities 'Uom' and 'UomType' which have a relationship.  A 'Uom' has a navigation property to 'UomType'.  I can bring in both entities in the Navigator with no problem.  But when I look at them in the Relationships tab, they are disconnected.

 

This is what the metadata looks like:

 

<EntityType Name="Uom" BaseType="Global">
<Key><PropertyRef Name="Id"/></Key>
<Property Name="UomTypeId" Type="Edm.Int32"/>
<NavigationProperty Name="UomType" Type="UomType">
<ReferentialConstraint Property="UomTypeId" ReferencedProperty="Id"/>
</NavigationProperty>
</EntityType>
<EntityType Name="UomType" BaseType="Global">
<Key><PropertyRef Name="Id"/></Key>
<Property Name="Property1" Type="Edm.Boolean" Nullable="false"/>
<Property Name="Property2" Type="Edm.Int32" Nullable="false"/>
</EntityType>

But if I go into 'Manage Relationships' and click auto detect, it finds the relationships, but this is hardly productive, if it's a complex set of entities.  Can anyone tell me if Desktop recognizes relationships?

 

 Thanks for any advice.

 

8 REPLIES 8
Nicolandrea
New Member

Do we have a solution today?

 

I would use PowerBI to build reports against a complex OData model, leveraging the navigation Properties would be a msut for me.

curth
Power BI Team
Power BI Team

I'm afraid we haven't had the time to implement this feature yet.

Thanks for the comment, @curth.

 

The feature works with older OData sources with 'lookup relations'.  It must tweak on older, now-deprecated, metadata elements.  So, you are referring to OData V4 navigation properties not being supported for now, right?

 

A possible solution for me would be to downgrade to OData V2 or V3.  I can do that if someone can tell me what metadata elements are used to detect navigation properties.

This thread is pretty old, but I figured I would chime in with my findings as I'm building something similar using OData V4. 

 

I have found that if the primary and foreign key names are identical, then PowerBI should be able to automatically detect the relationships. For greatest accuracy I would recommend using descriptive and unique names as opposed to simply "id"

 

some_parent_table

parentId

 

some_child_table

childId

parentId

 

I am using navigation properties, but I am not able to tell if PowerBI cares about that or not.

Sean
Community Champion
Community Champion

Auto Detect1.png

Auto Detect.png

Is Import Relationships ON?

Double-checked, and yes 'Import Relationships from data sources' is enabled.

Hi @coreyw,

 

Power BI Desktop will auto-detect relationships during load. It will  automatically create the relationship on when it can determine with a high-level of confidence there is a match, like fully matching column names and data types. In your OData feed data source, it will have ambiguity present when resolving complex of entities which makes Power BI Deskop not confident enough to create the relationship automactically. Anyway, don't trust too much on Auto Detect during load (and auto-created relationships). Always create and edit your relationships in "Manage Relationships".

 

Reference:
Create and manage relationships in Power BI Desktop

Why Is My Relationship Inactive in Power BI Desktop?

 

Regards,

Thanks for the reply, @v-sihou-msft

 

I'm writing the OData V4 data provider in Visual Studio.  What I'd like to do is to give all the clues to Power BI in order for it to be able to infer all the relationships automatically.  What those clues are - I don't know.  Sometimes I'm bringing down 5 entities that have complicated relationships - it'll be a real pain to have users of my datasets to a) know what the relationships are, and b) have to patience to define them manually everytime.

 

All I have are 'lookup' relationships, involing two entities, which are essentially Many-To-One, which the docs say are common enough that should be found automatically.  I can understand if I'm bringing down 5 entities and Power BI can't determine with confidence, but it should detect a relationship between 2.

 

I'm following the conventions defined by Entity Framework to define the primary and foreign keys and similarly explicitly defining the relationships the in OData model. 

 

I've tested Power BI against some Internet-available OData sources and I do get relationships defined, so it is possible.  What would be really useful for me is to figure out what Power BI Desktop looks for and make sure I do the same thing on the OData side.

 

I have to admit, I'm stuck between a rock and hard place when it comes trying to develop a solution.  The OData V4 development framework under .Net has very poor documentation and with Power BI Desktop, it's still a work in progress.  When something doesn't work, it's near impossible to get any support since I'm in the grey area between two emerging technologies.

 

Any suggestions on the Power BI Desktop side would be appreciated.

 

Corey.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.