One of the longest running threads is timeout errors on Power BI when downloading business data using the OData feed from an Invantive Cloud source database. The timeouts on the OData connector happens both with Microsoft Power BI Desktop as well from Power BI Service (PowerBI.com). The Azure Data Factory Connector generates significantly less questions, but has less users and a more technically oriented audience.
The following video shows how to quickly increase timeout for timeout errors with Power BI and OData:
The length of the thread justifies a wiki encapsulating the knowledge gathered up to December 2022 regarding the error:
OData: Request failed: The operation has timed out
Help us getting a permanent solution
Support our idea in the PowerBI community with an upvote to solve this problem structurally: https://ideas.powerbi.com/ideas/idea/?ideaid=989055f7-6495-ec11-826d-501ac50aa35e.
An overview of all open Power BI ideas for Invantive Cloud can be found at Power BI suggestions for improvement.
What causes an OData timeout in Power BI?
The OData timeouts (also often written as “OData time-out”) indicates that the data consuming program (Microsoft Power BI Desktop and/or Power BI Service) had to wait longer than an established maximum timeout on the full data set to download.
An OData timeout can occur in Power BI Desktop and Power BI Service even when the data download was progressing steadily, but not fast enough to complete within the time limit.
What is the default timeout of an OData query on Power BI?
The default value for timeout on an OData query is 10 minutes using the standard Microsoft OData feed connector in Power BI.
How do I change the OData query timeout?
There is no centralized or report-central solution to increment the default value for timeout of 10 minutes to a higher value. In the Advanced Editor of Power BI the timeout value can be altered per OData query by appending , Timeout=#duration(days,hours,minutes,seconds) to the OData.Feed statement.
For example, to increment the timeout to 2 hours, the following query:
Bron = OData.Feed("https://bridge-online.cloud/3279/odata4/", null, [Implementation="2.0"]),
has to be changed into
Bron = OData.Feed("https://bridge-online.cloud/3279/odata4/", null, [Implementation="2.0", Timeout=#duration(0,4,0,0)]),
It also wise to directly include options to skip null values to improve download performance (as described in Faster OData downloads with smaller size with small adjustment in Power BI), leading to:
Bron = OData.Feed("https://bridge-online.cloud/3279/odata4/", null, [Implementation="2.0", ODataVersion=4, OmitValues=ODataOmitValues.Nulls, Headers=[Referer = "enter-your-chosen-id-for-the-source" ], Timeout=#duration(0,4,0,0)]),
The syntax of #duration is given in #duration - PowerQuery M | Microsoft Learn. M is the programming language used by Power Query, the engine transforming the data for display in Power BI. Power Query is also available as an extension to Excel, which relies on Excel for displaying and graphing the values.
It is recommended to add the new timeout value using #duration to all OData feeds in all data sets and Power BI reports.
The value for enter-your-chosen-id-for-the-source can be chosen freely. It is recommended to choose a value that enables you to relate individual reports or data sets to a download visible in Invantive Bridge Online Monitoring. The Power BI file name is often a good choice if it accurately conveys the meaning of the dashboard. Within Invantive Bridge Online Monitoring, the value of the Referer-header is visible both directly on the request as well as in the list of requests.
Please note that other headers can be altered too:
AcceptAccept-CharsetAccept-EncodingAccept-LanguageCache-ControlContent-TypeIf-Modified-SincePreferRangeReferer
What are the maximum timeout values for the OData download?
The maximum timeout value for an OData download that can be set varies with the product and (for Power BI) with the subscription type. For Power BI Pro the maximum OData timeout is 120 minutes. For Power BI Premium per User (PPU) and Power BI Premium it is 300 minutes (source).
For data flows and other OData consumers other maximum values can apply. For data flows it is 2 hours for a table, 3 hours for a data flow and 24 hours with Premium.
How can I check what Power BI queries failed due to an OData timeout?
The Invantive Bridge Online Monitoring displays per OData request whether the download was canceled due to a timeout. The typical errors are:
itgenpmr003:
The data download was cancelled by the user.
Query the data again to retrieve the data when deemed necessary. Optimize your query first in case of a time-out.
and
A task was canceled.
An OData timeout typically occurred when the “Duration” column displays a value close to 600 seconds, say plus/minus 10 seconds.
In a future release the error code might be differentiated when the timing indicates that not the user canceled the download, but the Power BI program did so based upon the timeout specified in the OData query in the Advanced Editor.
Why does the OData download take so long?
With Invantive Cloud, users often download data from hundreds or thousands of companies as one big stream, or a single very large company in one stream. We have seen downloads of an uncompressed size of over 100 GB. Collecting these volumes of data from cloud sources such as Twinfield or Exact Online can take considerably time, even when the cloud data source is fast (which definitely not always applies).
Moreover, Invantive Cloud is real-time: only when a question is asked the data is collected. Only when the exact same question is asked multiple times it will be answered from the cache. In the database settings the time (in seconds) during which the original answer will be returned is given. Usually this is 16 hours, but depending on the subscription, much lower values down to a few minutes are no exception. Technically, there is even no reason not to use cache, but it does slow down the operation. See Differentiate OData4 for Power BI Cache Behavior for more information on caches.
The Invantive Cloud is based upon a pull-principle to allow near real-time data retrieval: once a business event is registered in your cloud application it can be available in your Power BI reports. To avoid overloading cloud applications with continuous requests many techniques have been embedded, including extensive caching.
One of the methods to dramatically increase download performance is to let the pull-principle go, as well as the near real-time character of business reporting. With tools such as pre-loading you can download the data at network speed. Alternatively, by optimizing the reports as described below you can still acquire near real-time reporting combined with reliable reporting.
Where can I find this “Advanced Editor” in Power BI?
The “advanced editor” can be found in Power BI through the window that displays when you select “Gegevens transformeren” in the ribbon:
The selected “Advanced Editor” on a query:
How do I set the timeout already on the first download?
The “Advanced Editor” is also accessible from the Navigator before the table is fully loaded the first time. This can be used to greatly reduce the data volume immediately thanks to PowerBI’s query folding by adding a filter step directly after the download to allow PowerBI’s query folding to forward the filter to the server, and setting the timeout as described above:
Still can not download the data even at maximum OData feed timeout
When the data can not be downloaded completely at the maximum OData feed timeout, there are various alternatives available to extend the limits and/or make the download complete sooner:
- Optimize your downloads following the steps on Overview of Power BI Performance and Download Size Improvement Techniques and linked pages.
- Check the Power BI subscription plan and change to PPU (Power BI Premium per User) if necessary.
- Reduce download size for Power BI reports by selecting solely the needed columns over OData
- Pre-load data in OData-cache to avoid Power BI timeouts
- How do I get a free Power BI performance audit?
- Use a short 30 or 60 minute consultation to investigate the cause with a consultant.
- Involve an external consultant with knowledge of Exact Online and Invantive Cloud. Call our sales department and we are happy to refer you to a third party.
OData download completes, but still timeout in Power BI Service
Every now and then the Bridge Online Monitoring will display that a download completed successfully and sometimes even very fast. But the Power BI Service (or sometimes the Power BI Desktop) reports a timeout or an error.
This problem occurs when the data volume downloaded exceeds internal explicit or implicit limits on the data volume. Not uncommon, the Power BI Service might run the large OData download again just seconds later again and again.
In the future, continuously repeating downloads might be canceled and block, but currently they are manually detected and a manual block is applied on the account in that case.
The first step to resolve this item is to optimize the reports. Please upgrade to a more extensive Power BI subscription plan when solely optimization doesn’t help sufficiently to make the OData feed load the dataset correctly.