Power BI Exchange

Expand all | Collapse all

Error handling with OData.Feed

  • 1.  Error handling with OData.Feed

    Posted 13 days ago
    Have been using OData.Feed, which works very well; we now use it in a Data Connector; but am having difficulty working out how to introduce error handling; the particular scenario I'm on is that a query might request data, and perhaps some of the data might be inaccessible, due to say permissions and I want the query to detect that, and return the data it can, and not fail completely. The documentation gives no clue and I could find no useful blogs. Any ideas anyone?

    ------------------------------
    John Dawson
    Software Engineer
    876899077
    ------------------------------


  • 2.  RE: Error handling with OData.Feed

    Posted 12 days ago
    John -

    A few questions to consider:
    1. Which OData version does the endpoint implement?
    2. Are you using Fiddler to monitor the traffic?
      1. If so, what's the error response code returned by the endpoint for your use case?
      2. Are you capturing these error response codes in an Error.Record?
    3. What headers are you passing to the endpoint?
    4. Are you using the IncludeAnnotations and IncludeMetadataAnnotations options in the request?
      1. If so, what annotations terms are you providing?

    If your OData endpoint uses v4, the specification states that error codes in the 4xx range indicate a client error, such as a malformed request, and that error codes in the 5xx range indicate service errors. My guess is that if a query requests data that is inaccessible due to permissions, then that would return error codes in the 5xx range.

    Also, can you show the M functions you use to query the OData endpoint?

    Lastly, there is a known issue with Power BI's implementation of OData v4 endpoints, which may not have anything to do with your question:
    No Query Support for OData URLs




    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    emdata Inc.
    ------------------------------



  • 3.  RE: Error handling with OData.Feed

    Posted 9 days ago
    John - Thanks for introducing me to the 'ManualStatusHandling' parameter within Web.Contents. I've never used it before but a quick test has me convinced that it should work for your purposes. (It's also convinced me to start incorporating this into my custom connectors in the future!)

    In the following test, I'm requesting data from the U.S. Census Bureau's public API and I'm purposefully introducing a malformed URL. The API should respond with a 404 error code. If I add "404" to the list of values for the 'ManualStatusHandling' parameter, the request should fail silently, allowing me to catch the 404 error code in a subsequent step using the Value.Metadata function. Once caught, I can then throw a custom error message to the user.

    Here's the test:

    let
    /* Errors */
        // define possible errors
        errors.badRequest = Record.AddField(Error.Record("Bad Request", "this dataset does not exist. It's most likely a misspelling in the value for 'vintage' or 'dataset'. Double check these values and make sure they're spelled correctly."), "Status", 404),
        errors.serviceUnavailable = Record.AddField(Error.Record("Service Unavailable", "the server is too busy right now and needs a short break. Don't worry, you can try again in a bit."), "Status", 503),
        // build an error table
        errors.table = Table.FromRecords({errors.badRequest, errors.serviceUnavailable}) meta [Reason = type text],
    
    /* Variables */
        variables.url = "https://api.census.gov/data/2015/BADURL/variables.json",
        variables.response = 
            Web.Contents(
                variables.url,
                [
                    ManualStatusHandling={404}
                ]
            ),
        variables.responseMetadata = Value.Metadata(variables.response),
        variables.responseCode = variables.responseMetadata[Response.Status],
        variables.responseHeaders = variables.responseMetadata[Headers],
        variables.json = if variables.responseCode <> 200 then error errors.table{List.PositionOf(errors.table[Status],variables.responseCode)} else Json.Document(variables.response)
    in
        variables.json​


    I hope this helps.



    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    Emdata Inc.
    ------------------------------



  • 4.  RE: Error handling with OData.Feed

    Posted 9 days ago
    Hi Tony, moving back to group discussion, as might be useful to others ...

    Yes, I have used Web.Contents in other applications, but - as mentioned - would much prefer to stick with OData.Feed for current development, which indeed embeds a lot of this sort of error handing - as I understand it ...

    So I'm still hoping I might be able to apply manual handling, of certain error conditions, to OData calls (like use of ManualStatusHandling in Web.Contents) but had little luck looking for help on this, hence this chat … can you perhaps suggest where I might go looking? Thanks

    ------------------------------
    John Dawson
    Software Engineer
    876899077
    ------------------------------



  • 5.  RE: Error handling with OData.Feed

    Posted 9 days ago
    Quick question: what benefits do you see OData.Feed having over Web.Contents?

    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    Emdata Inc.
    ------------------------------



  • 6.  RE: Error handling with OData.Feed

    Posted 6 days ago

    Well that's a good question Tony; my understanding is that OData is recommended practice; a quick Google shows that, although praise is not unqualified ...

     

    But this issue of error handling, with OData, has me stumped (so far), so I may have to revert to Web.Contents, along the lines you shared (thanks)

     

    I've even thought of using Web.Contents to detect an error, and if none found, then use OData.Feed to fetch the data – but that seems clunky

     

    I'm using Graph (are you?); this page is useful: https://docs.microsoft.com/en-us/graph/best-practices-concept; it suggests we should handle errors, but does not say how

     

    Regards - John

     






  • 7.  RE: Error handling with OData.Feed

    Posted 6 days ago
    John -

    I've only played around with the Graph API; no production apps yet.

    Three big benefits come to mind working in OData.Feed with resources behind OData endpoints:
    1. automatic query folding
    2. schema detection
    3. automatic data typing
    I think most appreciate automatic query folding and schema detection aspects of OData.Feed. But I think automatic data typing is really crucial too and I don't see many folks talking about this. I tend to write a lot of code trying that automatically assigns data types from RESTful APIs based on the metadata. Automatic data typing would save a ton of work and time.

    I'm currently building a custom connector to an endpoint that supports both OData and RESTful APIs. I'll continue playing with both and report back anything I find. But I have to say, the more I play around with Odata.Feed, the less I like it. Forget about reading response headers, I still can't figure out how to set custom headers in a request!

    By the way, this thread inspired me to write a blog post on error handling with Web.Contents. Though it may not help you with your current problem, I hope it helps in the future.

    - Tony

    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    Emdata Inc.
    ------------------------------



  • 8.  RE: Error handling with OData.Feed

    Posted 5 days ago
    Thanks for that Tony; good of you to share that stuff. If I find out anything useful about OData.Feed I'll come back to you also

    ------------------------------
    John Dawson
    Software Engineer
    876899077
    ------------------------------



  • 9.  RE: Error handling with OData.Feed

    Posted 2 days ago

    Hi again Tony,

     

    I don't know if this helps you, but using OData with MSGraph, documented here, shows that you can include headers, as you can in Web.Contents

     

    I guess it depends on the service you're using and how OData is supported

     

    I don't have much experience outside MS Graph; both being MS services, I guess they are designed to work well together

     

    Certainly OData.Feed is easy to use, returning a table directly, or nearly, as OData.Feed[Value], conditional on OData.Feed[HasError]

     

    But my issue is that good practice requires more i.e. some error handling is needed – and the above [HasError] doesn't flag http errors

     

    In terms of coding, I'm using a wrapper function, which returns the data, as a table, and also a status / status message

     

    For the moment, as I indicated, my wrapper function calls Web.Contents, with ManualStatusHandling, to detect certain errors, and then OData.Feed – if no errors found; obviously, it would be better if OData could tell me directly if it encountered any http errors – which must surely be possible

     

    Regards - John