Error with Data Refresh in Online Service for Web API Data Source

[Robert_T]

When initating a data refresh for a particular dataset in the online Power BI service, the following error is returned after roughly 2 hours:

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"Timeout expired. The timeout period elapsed prior to completion of the operation."}}],"exceptionCulprit":1}}} Table: Table1.

The data refresh for this dataset consists of 6 queries:

• 5 Web API calls (including both GET and POST methods) that receive JSONs and transform them into tables
  • 4 that return smaller "reference" tables (100s of rows with a dozen columns)
  • 1 that returns a larger table (~6000 rows with 100s of columns) - "Table1"
• 1 merge query that is used to replace hexadecimal ID column headers in "Table1" with the matching names from one of the reference tables - "Table2"

The Web API query for "Table1" is set for a 3-hour timeout:

Source = Json.Document(Web.Contents(url & "ticket=" & apiTicket & "&project_id=" & projectID & "&step=filter&filter_id=" & filterID & "&details=some&include=custom_field_values",
[
Headers = [#"Content-Type" = "application/json"],
Content=Text.ToBinary(""),
Timeout=#duration(0,3,0,0)
])),

In the desktop version of Power BI, the data refresh takes roughly 45 minutes and completes successfully.

Using the same queries with a smaller data source (such that "Table1" would have ~250 rows instead of ~6000), the online service refreshes successfully.

Are there settings, either in the online service or in the desktop version, that could be adjusted to prevent this timeout error?

[v-deddai1-msft · Accepted solution]

Hi @Robert_T ,

You have touched the maximum refresh time of shared capacity. You can upgrade it to premium capacity. Or try to  optimize your data source to reduce data refresh.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Addy2020]

Hi @VDH ,

If it has reached "maximum refresh time of shared capacity" than why the error message doesn't clearly mentions that. Why it leaves the user with incomplete information?

Moreover, "Power BI limits datasets on shared capacity to 8 scheduled daily dataset refreshes." here user is facing issue even in the 1st attempt? It doesn't seems to be a License issue. Please explain.

BTW, I'm also facing same issue for the 1st time in past 4 years.

[DineiSilva]

Hi @Robert_T, 

Do you found some solution for your problem?
I have the same problem and I not find any solution.

The solution present here by Dedmon Dai, not solve in my case.
I Didn't find another solution.

Thank you for your attention.

[Robert_T]

Hey @DineiSilva, I ended up implementing a workaround whereby I broke up the dataset into multiple parts (~6) and performed separate parallel API calls for the different parts. I was not able to get the entire large dataset in a single call.

[DineiSilva]

Thank you @Robert_T for your answer.

I needed change my data set, mainly the ordem of the Incremental refresh in the steps (Power Query). After this action, my issue was solve.

Thank you again.

[amitchandak]

@Robert_T , I doubt it still did not connect to database properly

in case you are using the gateway

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot

https://guyinacube.com/2016/09/21/troubleshooting-connectivity-tips/