User Profile
guido_leenders
New Member
Joined 1 year ago
User Widgets
Contributions
Extract OData error from OData, even when HTTP Streaming is used by OData producer
Most OData-producers use HTTP Streaming to start shipping data to the OData-consumer as early as possible, allowing the consumer to process the data while more is being generated to reduce runtime duration as well as memory footprint across all stages. However, when the producer includes an OData error in the output stream, this only seems to be detected and handled meaningful when it is returned at start of the stream. OData errors included in the stream after the first character has been returned are not picked up by Power BI. We have several thousands of users across approximately the same number of companies and each one has to be trained what to do in that case. For now, we train them and send them an email when deemed possible to use a different user interface than Power BI to see the actual error instead of DataSource.Error: OData: Invalid JSON. A comma character ‘,’ was expected in scope ‘Array’. Every two elements in an array and properties of an object must be separated by commas. In fact, a related page on our forums is one of the most popular pages on Google for various related search terms. Our own OData-consumers have some mechanism in place that look at the JsonReaderException and using the LinePosition attribute search for a possibly present OData error and display it. However, we can not provide a PR for Power BI. It would reduce frustrations with our users tremendously when Power BI as OData-consumer could also extract the relevant OData-error even when HTTP Streaming is used. Sample JSON with OData-error in the stream: {"@odata.context":"https://bridge-online.cloud/acme-exact-online/odata4/$metadata#ExactOnlineREST.Incremental.GoodsDeliveryLinesIncremental@eol","value":[{"Timestamp":12745208078,"Created":"2022-12-14T07:21:28.32Z","Creator":"a230905c-541b-41dd-8455-229f21cc56d5","DeliveryDate":"2022-12-14T00:00:00Z","Description":"... 400 x 400 x 50","Division":338762,"EntryID":"5b4e4a14-b39f-4bae-b2f5-b23b317ecbc3","ID":"2147360b-aa4d-4896-8005-706fdb611922","Item":"a9d6923e-e119-4159-9b17-dbf44fabcaad","LineNumber":1,"Modified":"2022-12-14T07:21:28.32Z","Modifier":"a230905c-541b-41dd-8455-229f21cc56d5","QuantityDelivered":1.0,"QuantityOrdered":1.0,"SalesOrderLineID":"8b35d31a-8590-4519-8c8c-3f28ddf53097","SalesOrderNumber":1234567,"StorageLocation":"3ace26f6-d53d-4726-b7bb-367becfcf4f9","Unitcode":"-"},{"Timestamp":12745208107,"Created":"2022-12-14T07:21:28.867Z","Creator":"a230905c-541b-41dd-8455-229f21cc56d5","DeliveryDate":"2022-12-14T00:00:00Z","Skipped": "300.000 characters removed","Unitcode":"stuk"},{"Timestamp":12746454444,"Created":"2022-12-14T14:23:28.96Z","Creator":"6ab47e8d-e955-4c9d-8da9-9a7579f0c133","DeliveryDate":"2022-12-14T00:00:00Z","Description":"Ringtong ...","Division":338762,"EntryID":"080a2366-2156-420a-9a30-f43d874dc541","ID":"26bf29f2-8a11-4574-82fa-e0233983648d","Item":"09b172b7-10b2-44cc-a0a3-dcb4eee9ba77","LineNumber":1,"Modified":"2022-12-14T14:23:28.96Z","Modifier":"6ab47e8d-e955-4c9d-8da9-9a7579f0c133","QuantityDelivered":1.0,"QuantityOrdered":2.0,"SalesOrderLineID":"2400a5ec-9de5-41ef-9c6e-ec2b1bd249d8","SalesOrderNumber":123456,"StorageLocation":"2fef0dc6-b07f-435f-b4c6-edd2c44466ac","Unitcode":"stuk"}{"error":{"code":"itgeneor643","message":"Er is een interne fout opgetreden. Neem contact op met uw leverancier. (itgeneor643, aa08f467-2c73-4ff7-936b-57338e129230)","target":null,"details":[{"code":"aa08f467-2c73-4ff7-936b-57338e129230","target":null,"message":"Unique ID"}],"innererror":null}}239Views7likes1CommentIncrease scaleability by fixing OData "An existing connection was forcibly closed by the remote host."
For OData4-downloads, users experience great reliability when using Power BI Desktop. When the same load is moved to Azure Data Factory, it also works great. However, with larger datasets (starting at 10 MB), OData-downloads intermittently fail with the error: Data source error OData: Unable to read data from the transport connection: An existing connection was forcible closed by the remote host. DataSourceKind = OData DataSourcePath = https://bridge-online.cloud/naam/odata4/Twinfield.Twinfield.GeneralLedgerDetailsV3@tfd OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: GeneralLedger (Test). Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net Activity ID: f2282333-c403-4b78-9362-b10e081cd568 Request ID: 7b3d9747-b8d1-9c4f-1b67-1feb49d1779f Time: 2022-09-27 04:50:57Z It seems to relate strongly with the size of the dataset. Switching to Power BI Premium often helps, as does reducing the dataset. However, reduction is not always realistically possible. There are two known causes: PowerBI.com downloads the whole set from the OData4-producer, ultimately HTTP 200 status. Within one minute after that, it seems to process the data and then crash. And retry the download again. And again. And again. It reports this (vague) error above. PowerBI.com downloads the whole set from the OData4-producer, ultimately sends a RST_STREAM on HTTP/2. It does not try again and report the (vague) error above. The problem has been occurring for a number of years and limits the use of PowerBI.com in larger enterprise scenarios, as well as larger accounting firms. Other samples include: https://community.powerbi.com/t5/Service/Dataflow-refresh-fails-with-An-existing-connection-was-forcibly/td-p/970591 https://community.powerbi.com/t5/Service/Refresh-passed-in-the-desktop-but-failed-in-the-service-message/td-p/331543 https://community.powerbi.com/t5/Service/Unable-to-read-data-from-the-transport-connection-An-existing/td-p/1151009 https://forums.invantive.com/t/in-power-bi-robaws-connector-odata-unable-to-read-data-from-the-transport-connection-an-existing-connection-was-forcibly-closed-by-the-remote-host/439/2 (Dutch) I would welcome increasing the scaleability of the OData4 consumer to handle OData4-data sets above 100 million characters more gracefully.258Views12likes1CommentReduce download size by central setting omit null values in HTTP Prefer header
The OData4 standard allows null values to be omitted. However, this HTTP Header is not set by default. We must train every user (thousands of companies) to change the OData.Feed everywhere. It would be great when the HTTP Prefer header would be set always or when that could be configured centrally.215Views5likes2CommentsCentralize maintenance and higher default Timeout on OData
The default timeout of OData feeds is 10 minutes. However, large sites typically have downloads on our OData-producer that exceed that by large. The Power BI error due to a timeout is vague and does not guide the user to a solution by providing a hint or suggestion. Currently, we must train every user (thousands of companies) to change the OData.Feed everywhere. It would be very helpful when the default timeout was increased to (say) 2 hours and/or could be configured centrally.256Views20likes1CommentOptimize OData4 query removing duplicate OR-clauses, remove superfluous filters and using IN.
Power BI does not optimize the OData4 query when filtering on a number of values for a field. For example, it is not uncommon that identical filters are added multiple times with the OR-operator, required fields compared with null and an OR-operator used when IN could be used. This considerably reduces performance in several scenarios and exhausts the available URL length which is 1500 bytes on some intermediate platforms. See also https://community.powerbi.com/t5/Power-Query/Use-of-OData-IN-operator-instead-of-ORs/m-p/1966857#M57973173Views2likes1CommentRe: Differentiate error for OData 4.02 behavior: "We expected a property ‘...’, but the OData service omitted it from the response data."
The suggestion is to split this error in two errors in Power BI: When omit values is not used, the error remains the same. When omit values is used, use a different error, such as: The property ‘Document’ has no values in the response data. Omit null values has been requested, but the OData server must confirm skipping them using the preferences-applied HTTP header. Either the property value is missing for some reason, or the OData server forgets to set the preferences-applied HTTP header.76Views0likes0CommentsRe: Differentiate error for OData 4.02 behavior: "We expected a property ‘...’, but the OData service omitted it from the response data."
For more background see: https://github.com/microsoft/DataConnectors/issues/360#issuecomment-1015339087 https://forums.invantive.com/t/we-expected-a-property-document-but-the-odata-service-omitted-it-from-the-response-data-this-can-occur-for-a-number-of-reasons-and-does-not-necessarily-imply-that-the-data-does-not-exist-or-is-of-a-default-value/2062/3 (Dutch)76Views0likes0CommentsDifferentiate error for OData 4.02 behavior: "We expected a property ‘...’, but the OData service omitted it from the response data."
The error at the end can be returned when occur when the OData server leaves out the value assignment of null to fields defined in the metadata. However, starting OData 4.02, the same error can also occur when the OData.Feed specifies that the server may leave out null values using "ODataOmitValues" Error: We expected a property ‘Document’, but the OData service omitted it from the response data. This can occur for a number of reasons and does not necessarily imply that the data does not exist or is of a default value.76Views1like3CommentsRe: Honor ETag or Cache-Control to reduce OData network traffic by an order of magnitude, including developer experience
An English version of the further explanation is available at: https://forums.invantive.com/t/faster-data-processing-from-invantive-cloud-on-selected-odata-clients/1708286Views0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.