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 Desktop Issue -
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.
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
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
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.
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.
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.
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?
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?
You are able to enter 250 minutes. Or you can directly change duration value in Advanced Editor.
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?
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.
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?
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.
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.
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!