cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
piyushj
Frequent Visitor

Power Bi Service Refresh - timeout expiration issue

Hi PBI Team,

 

We are facing an issue in power bi service as well as in desktop app. We have huge amount of data available in tables (>10GB). We are importing data using SQL Server database. Previously, we have only 50k records but now it has more than 500millions rows. Now when we are trying to refresh the data, we are getting below error -

Power Bi Service (Screenshot Attached)Power Bi Service Refresh Issue.JPG

Power Bi Desktop Issue -

Desktop -Server Not Responding.JPG

Error Description -

Microsoft SQL: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=217; handshake=664; [Login] initialization=0; authentication=0; [Post-Login] complete=217;

 

 

Appreciate your quick help on this.

 

 

 

 

 

25 REPLIES 25
Roberto456
Resolver I
Resolver I

 

I am getting the same error's, no idea why this is happening.

 

I only have around 4million rows too...

 

Is it an internet connection issue or a microsoft issue

 

loading issue.JPG

Hi there


The error is from your SQL Server database and it appears that it took too long for the query to return any rows.

 

I would suggest working with your DBA to fine tune the query





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

michaelmof
Regular Visitor

We have a PowerBI Premium account but are getting consitent timeout after 120 minutes when using Excel as a datasource. The Excel is hosted in a SharePoint library. I do not see anywhere to put the advnaced timeout setting for this datasource.

v-yuezhe-msft
Microsoft
Microsoft

Hi @piyushj,

Firstly, based on this blog, in Power BI Service, refreshing the dataset will give an error message letting you know your dataset is too large, and it will fail to refresh until you filter the data model back down under 1 GB.

Secondly, optimize your data model following the guide in this blog in Power BI Desktop, the blog also applies to Power BI Desktop though it is about PowerPivot.

And specify appropriate value in “Command timeout in minutes (optional)" following steps below, then check if you can  refresh successfully in Power BI Desktop.

1. Click on the arrow for "Edit Queries" in your current Power BI Desktop file, select "Data Source Settings” in the dropdown.
2. Click on "Change Source..." in the Data source settings pop-up window.
3. Click on Advanced Options in the pop-up window, enter 60 minutes in the "Command timeout in minutes (optional)" textbox, then click OK.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

I'm using power bi pro trial version. I have huge amount of data more than 2 million rows. I have also used direct query option but still direct query is not enough for us. 

Hi @piyushj,

The 1GB limitation of data model during refresh in Power BI Service also applies to users who have pro license, this limitation is different from the 10GB data capacity limit for pro users. The 1GB limitation is for the data model within each PBIX file you upload to Power BI Service.

You can rename your current PBIX file to .zip and check the size of the data model within it. For more details, please review this blog. If the data model size is too large to refresh in Power BI Service, please break up your dataset into smaller datasets and refresh them in Power BI Service.

Besides, have you specified value in "Command timeout in minutes" in Power BI Desktop?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

So basically the sets the other users are trying to refresh are just too large for PowerBI to handle it then? So would you then say that is not the appropriate tool for handling data sets with hundreds of millions to billions of rows?

 

If so wat would you suggest as an alternative? 

Yes , I specified command time out to 90 mins but after that it throws the same error. Is there any limitations if I give 250 mins duration in command time out

Hi @piyushj,

You are able to enter 250 minutes. Or you can directly change duration value in Advanced Editor.
1.PNG


Regards,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm having this issue in the Power BI Service (not desktop) when attempting to configure a new datasource. There are no options to manually set a timeout, and the default seems to error out after about 60 seconds. 

 

How do we get around this? 

Hi there I have a dataset that takes over 30mins to refresh, but it is sending data the entire time.

Just ensure that you have configured the timeout settings on the data source. As well as that data starts getting returned as quickly as possible




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ 

I am looking for some help here based on some issues I have been facing.

 

My dataset refresh fails once it goes beyond 5 hours. So does this mean that no refresh can run longer than 5 hours in Power BI service? Or does have to do with individual queries in Power BI?

 

So, hypothetically, lets say I have 50 queries in my dataset and each will run for exact 10 mins which makes it 8+ hours. Will this run fine or will it fail because total time taken is more than 5 hours?

 

FYI, of course I am talking about premium workspaces as I mentioned 5 hours.

 

Thanks for your help.

Regards

Hi there

What could possibly be happening is when using Power Premium there could be a lot of factors causing it to fail.
Such as running out of memory, other reports being loaded which could possibly stop the refresh and other things.

What I would suggest doing is to rather see how you can get the refresh time down by using incremental refreshing. That is where I would focus my effort.

When refreshing data there are quite a few moving parts which could affect the refresh failing.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ 

I am in process of configuring Incremental for my report.

But my concern is - how will the first full refresh work considering my dataset takes 5+ hours for the full refresh?

 

Regards

 

 

Hi there

What if you had to initially change your incremental refresh to not go back with all your data, but a smaller dataset to see if that would help?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ 

I can try with smaller dataset by using only few months of data.

But once I use all the months again, my dataset refresh time will increase and it will hit the timeout issue again.

 

Regards

Hi there

That would indicate that due to the data size something is causing an issue.

Have you had a look at the Power BI Premium Capacity App to see if you run out of allocated memory during the dataset refresh?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Also, I have got this confirmation from Microsoft team that there is a limit on query timeout of 5 hours. Since I was using SAP HANA DB and there is a query which runs for 10+ hours. It runs fine on Desktop, but fails on Service due to this timeout issue. Regards

Hi there

That is correct, you have to make sure it finishes within the 5 hours to get it refreshing.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi All, 

 

I am experiencing this time out issue now.  its a import dataset and i dont see any duration parameter coded in the advance editor.  do we need to add it to the code.  also this issue is occuring suddenly. 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!