OData issue in Power BI and SAP Commerce

[alopezlop]

Hi everyone!

Related to "OData issue in PowerBI and Excel 2016", I am trying to connect from Power BI Desktop to SAP Commerce, through an OData interface that exposes orders:

https://my-sap-commerce.com/odata2webservices/oDataTest/Orders

By default, the query retrieves 10 records but I get the following error on the eleventh row:

DataSource.Error. Unable to parse OData response result. Error: A type named 'HybrisCommerceOData.Order' could not be resolved by the model. When a model is available, each type name must resolve to a valid type.
Detalles:
DataSourceKind=OData
DataSourcePath=https://my-sap-commerce.com/odata2webservices/oDataTest/Orders

Is it possible to define this type (or any other related to SAP Commerce) in Power BI Desktop or is it a matter of installing an external driver which knows about these SAP types?

Thanks in advance.

[alopezlop]

Hi AmiraBedh, thanks for your quick response. 

I have followed similar indications to the ones in Connecting SAP Data Warehouse Cloud OData API with PowerBI via a Blank Query. 

Unfortunately, neither do I know the Order metadata nor how to construct that type in Power BI. Is it possible to translate the metadata, if it is available, into Power BI in any way? E.g. Importing an "Order" table with the same structure using an empty file?

[AmiraBedh]

Yes, it might be possible to work around this issue by creating a manual structure in Power BI that matches the expected schema of the 'Order' type from the OData service.

If possible, retrieve the metadata for the 'Order' type from the OData service. This might be accessible at a URL like `https://my-sap-commerce.com/odata2webservices/oDataTest/$metadata`. If you cannot access it directly, you may need assistance from a SAP Commerce administrator or developer.

Once you have the metadata, you could manually create a table in Power BI that matches the structure of the 'Order' type. This might involve using Power Query to build a table with the correct columns and data types.

If you have access to an example of an 'Order' record (perhaps from the first 10 rows that are successfully retrieved), you could use this as a basis to create a sample file (e.g., in Excel) that matches the structure. You could then import this file into Power BI, ensuring the data types and structure align with what the OData service expects.

You may then be able to use this manually-created structure in Power BI to guide the import of the actual 'Order' data from the OData service. This might involve custom M queries or transformations within Power BI to translate the raw OData response into the manually-created structure.

[alopezlop]

Thanks Amira. I have done something similar to what you propose.

1) $metadata is retrieved

<?xml version='1.0' encoding='UTF-8'?>
<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" Version="1.0">
<edmx:DataServices m:DataServiceVersion="1.0" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<Schema Namespace="HybrisCommerceOData" xmlns="http://schemas.microsoft.com/ado/2008/09/edm" s:schema-version="1" xmlns:s="http://schemas.sap.com/commerce">
<EntityType Name="Order">
<Key>
<PropertyRef Name="integrationKey"/>
</Key>
<Property Name="code" Type="Edm.String" Nullable="true" s:IsUnique="true"/>
<Property Name="statusDisplay" Type="Edm.String" Nullable="true"/>
<Property Name="totalPrice" Type="Edm.Double" Nullable="true"/>
<Property Name="integrationKey" Type="Edm.String" Nullable="false" s:Alias="Order_code"/>
</EntityType>
<EntityContainer Name="Container" m:IsDefaultEntityContainer="true">
<EntitySet Name="Orders" EntityType="HybrisCommerceOData.Order"/>
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>

2) Create CSV with the same structure and import into Power BI. It is not possible to use the exact name because of the dot within the name (i.e. in Power BI no query can be named as "HybrisCommerceOData.Order").

 

Do you know any other way of creating these "custom types" inside Power BI? Thanks in advance.

[alopezlop]

After some investigation I have found that, although it seems to be possible in some version of Power Query, unfortunately, it is not my case. Neither my current Excel version nor my Power BI Desktop 2023 allows to create a new data type in the Transform section (see link).

My only option at the moment seems to investigate how to code in M language such data type creation. As far as I have seen something like the code in Custom Data Type in PowerBI? (Transform - Structured Column - Create Data Type).

However, after trying this, it seems that the new data time isn't recognized at all and the OData download still fails with the same issue. 

Please, let me know if there is any other alternative. Thanks.

[AmiraBedh]

The error message you're encountering, "Unable to parse OData response result. Error: A type named 'HybrisCommerceOData.Order' could not be resolved by the model," suggests that Power BI is having trouble understanding a specific type defined in the OData response from SAP Commerce. This issue typically stems from a mismatch between the data model expected by Power BI and the one exposed by the SAP Commerce OData service. It's likely that the type 'HybrisCommerceOData.Order' is not defined or has a different structure in the SAP Commerce OData metadata than what Power BI expects. To resolve this, you would need to examine the metadata exposed by SAP Commerce and ensure that it aligns with the expected data structure in Power BI, potentially involving collaboration with SAP Commerce administrators or developers, or utilizing custom queries or connectors in Power BI to handle the specific data structure.

[alopezlop]

Hi Amira,

Finally, no source with the same structure was needed. I tested as follows:

1) SAP Dev Team created an OData interface with simple types (Edm.String and Edm.Double).

2) In Power BI, OData API download was tested without an option which was causing all the trouble:

Options_Odata = [ Query = [#"debug-mode" = "true"] ]   //was here because of some previous tests
//OData.Feed(url, Headers_Odata, Options_Odata)
OData.Feed(url1, Headers_Odata)

Then, a full download from the Orders source was completed.

3) SAP Dev Team included complex/navigable fields in the interface  (<Association/>, <Navigation />, <End /> nodes among others).

4) In Power BI, the same download test was carried out. Unfortunately, it seems that the driver does not work with certain 'Nullable' types:

Error: The metadata document could not be read from the message content.
UnexpectedXmlAttribute : The attribute 'Nullable' was not expected in the given context. : (1, 669)

DataSource.Error: OData: El documento de metadatos de la fuente no parece válido. Error: The metadata document could not be read from the message content.
UnexpectedXmlAttribute : The attribute 'Nullable' was not expected in the given context. : (1, 669)

These 'Nullable' properties cannot be removed from the SAP side and seems to be related with the Microsoft.Data.OData.ODataMessageReader implementation.

As this does not be to be easily solved, I tested the download in JSON format with:

Source = Json.Document(Web.Contents(url2, Headers_Odata)),

In this case, the API is limited to 1,000 results, which seems to be a small amount and I guess something on code should be developed for paginating them (don't know how).

Any idea about how to continue? Thanks in advance.

[crln-blue]

Hello, a bit unrelated to your issue but how were you able to get SAP odata feed to power bi? I can't get past the authentication. Thank you.

[alopezlop]

Hi @crln-blue,

After some tests, my conclusions are as follows:

• Power BI OData API (v7?) seems to be incompatible with SAP Commerce API (older and not sure which one it has in my case) regarding Nullable objects / attributes in the SAP entity. Therefore, these fields cannot be read.
• As a workaround, I guess it is possible to create simple interfaces, withouth such objects or in a simplified form, that the Power BI API can read without issues.
• Another alternative is downloading information with JSON format, but there is a limit of 1000 records using this approach.

HTH,

Álvaro López