question

MarkMiddlemist-1774 avatar image
0 Votes"
MarkMiddlemist-1774 asked Lz-3068 commented

Need help understanding why an OData endpoint is not working in dynamics/powerbi/power query

Hi All



Our platform allows users to configure and publish custom data endpoints for external consumption. We recently added the ability to surface the data as an OData feed, specifically for consumption in Dynamics and PowerBI.



As it stands we have the feeds being consumed successfully in C# code (via the OData connected service extension in Visual Studio) but when we try and access the endpoint in Dynamics or PowerBI we get a generic error message. We have tried simulating the request in PowerQuery and receive the following message.

111923-odata.png

We’ve tried comparing the data visually to reference datasources but so far are drawing a blank.



If there is someone there who can help it would be very much appreciated. An example of the data source would be https://kkapi.azure-api.net/api/Data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees?subscription-key=0c04173f848d4d37a56ae54bd93a5118


power-query-not-supported
odata.png (10.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MarkMiddlemist-1774 avatar image
1 Vote"
MarkMiddlemist-1774 answered Lz-3068 commented

To let anyone who may come across this while trying to generate their own OData serialisation.

The problem turned out to be nothing to do with the textual format of my response. I had that right.

However, I had missed that the standard says that OData responses SHOULD include a "OData-Version" HTTP header (odata-v4.01-part1-protocol.html)

It appears the Mashup engine treats that as MUST (anyone who's ever had to deal with RFCs will know the importance of the phrasing)

Once the Http header is added and I checked the "Include open type columns" checkbox in the OData feed advanced options I was able to access the data in PowerQuery (still need to test in PowerBI and Dynamics, but feeling a lot more confident now)

If you've got a reference in your code to Microsoft OData nugets you should be able to handle it with the following code:

response.Headers.TryAddWithoutValidation( "OData-Version", ODataUtils.ODataVersionToString(ODataVersion.V4));

Still some more testing to do, but hopefully this helps others in the future

All the best

Mark


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MarkMiddlemist-1774. THANK YOU for what you do in term of case documentation

1 Vote 1 ·

Lets just say I spend a lot of time on the fringe of weird development. I spend far too much of my life reading questions on various forums that match what I'm doing, but have no answer, or a "It's OK. I fixed it." comment.

Thanks to you for digging in. It is very much appreciated

All the best

Mark

0 Votes 0 ·
Lz-3068 avatar image Lz-3068 MarkMiddlemist-1774 ·

I spend far too much of my life reading questions on various forums that match what I'm doing, but have no answer, or a "It's OK. I fixed it." comment
Oh yeah, I share the pain :((( And when "It's OK. I fixed it" even exist...

All the best to you too and THANKS again for what you've done. If I can ask one more thing: Accept as answer your most relevant post re. this issue

1 Vote 1 ·
Lz-3068 avatar image
1 Vote"
Lz-3068 answered MarkMiddlemist-1774 commented

Hi @MarkMiddlemist-1774

I won't be able to help with this and I feel a bit alone these days helping on Power Query issues. Couple of suggestions in the meantime

1/ Check the following threads that relate to the same error and seem to indicate this could be due to an Authentication issue
- https://social.technet.microsoft.com/Forums/en-US/b806a862-278c-46a7-9c33-2db90958b756/odata-feed-throws-error-odata-the-given-url-neither-points-to-an-odata-service-or-a-feed?forum=powerquery
- https://social.technet.microsoft.com/Forums/en-US/12e6e0d3-356a-4280-b0c1-532f4a5b85cb/connection-to-hosted-crm?forum=powerquery

2/ Search the former Power Query forum as I only picked the above 2
3/ Search the PowerBI Community (I did not)

If nothing helped feel free to raise your case on the PowerBI Community where there's obviously more helpers
Sorry that I can't do more. All the best...


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Firstly, many thanks for taking the time to respond @Lz-3068

As you can probably tell I'm much more a server-side dev so trying to figure out the client end is a bit of a challenge for me.

The consensus from other answers I've found does seem to back up this normally being an authentication/redirect issue

To verify this I've first off traced the comms using Fiddler while connecting. The responses are coming back 200 OK with data. I've also set up a local debug session of the service that doesn't require authentication. When connecting to his I can see it hitting the endpoint correctly and receiving data back (4 times before failing).

As the code is using custom serialisation due to the dynamic nature of the underlying data I'm assuming it is something to do with that I've got wrong and needs fixing. Mime types, string encoding etc all seem to align with other sources that work, so I'm at a bit of a loss. I've enabled tracing in PowerQuery but the attempted connections don't seem to be reaching the point where its logging anything.

I'm continuing to dig through myself, and have also posted on the PowerBI forum as you suggested. As I make progress I'll try and update this thread. I know what I'm doing is really not a common situation, but I HATE it when searching for past solutions on something and the only thing I can find is a dead thread with no solutions.

Thanks again for everything you do for the community

Mark

0 Votes 0 ·
MarkMiddlemist-1774 avatar image
0 Votes"
MarkMiddlemist-1774 answered

OK, first thing I've found, the tracing is working (contrary to what the docs I had been looking at said the files are in C:\Users\<Username>\AppData\Local\Microsoft\Office\16.0\PowerQuery\Traces)

In there I'm seeing messages like the following:

DataMashup.Trace Warning: 24579 : {"Start":"2021-07-05T15:29:26.5095988Z","Action":"SimpleDocumentEvaluator/GetResult<IPreviewValueSource>","HostProcessId":"21916","PartitionKey":"Section1/Query2/Source","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataFormat.Error] OData: The given URL neither points to an OData service or a feed: 'https://kkapi.azure-api.net/api/Data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees?subscription-key=0c04173f848d4d37a56ae54bd93a5118'.rnStackTrace:n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c_DisplayClass3_0.<BeginGetResult>b_0(EvaluationResult2`1 result)\r\n\r\nDetail: \"https://kkapi.azure-api.net/api/Data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees?subscription-key=0c04173f848d4d37a56ae54bd93a5118"rnrn","ProductVersion":"2.94.222.0 (21.06)","ActivityId":"f4689a20-9d57-448f-b446-64084aadf0e6","Process":"Microsoft.Mashup.Container.Loader","Pid":21188,"Tid":1,"Duration":"00:00:00.3646210"}

If anyone knows of a way to get more specific detail on validator failures that would be brilliant.

Thanks in advance

Mark

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered Lz-3068 commented

Hi @MarkMiddlemist-1774

After many searches on the Net, readings here and there, if you haven't read the below threads IMHO you should:
- https://stackoverflow.com/questions/50264739/how-to-specify-an-api-key-name-in-excel-a-web-api-key-can-only-be-specified-wh
- https://stackoverflow.com/questions/33225590/authenticating-with-excel-power-query-against-net-odata-web-api

Unfortunately nothing suggested in these threads work with the URL you shared (I tested a number of variationsss)
However, and even if I haven't found something saying this is forbidden/not recommended I wonder if the name of your API Key (subscription-key) isn't causing a problem somewhere due to the dash it includes. If you have a test environement that allows easily changing/creating an API Key name w/o a dash maybe you could give it a try

In which case, in Power Query, that would look something like:

 = OData.Feed("https://kkapi.azure-api.net/api/data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees", [ApiKeyName="MySubscriptionKey"])

Eventually:

 = OData.Feed("https://kkapi.azure-api.net/api/data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees", [Implementation="2.0", ApiKeyName="MySubscriptionKey"]) 

Then when prompted to provide the Credentials > Web API > 0c04173f848d4d37a56ae54bd93a5118

Something I didn't mention earlier and in case you're not aware: the Credentials are stored in the Data source settings. Before any test it's a good idea to clear the Permissions

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.