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

[coreyw]

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.

[Nicolandrea]

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]

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

[coreyw]

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.

[BIGuy]

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]

Is Import Relationships ON?

[coreyw]

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

[v-sihou-msft]

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,

[coreyw]

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.