PowerBI Service refresh failed (more than 120 minutes to refresh)

[Anonymous]

Hi all,

I have a few pbix files that take 20 minutes to refresh in the PowerBI Desktop.

Those same reports fail to refresh (automated) in the PowerBI Service due to a connection time-out (more than 120 minutes).

I gave it a few days but it is already 5 days in a row now.

The pbix files are between 140 and 200 MB.

What can be the issue?

Thanks in advance,

Kind regards,

Yanni

[v-lionel-msft]

Hi @Anonymous ,

This may be the reason, that is, the service can not allocate resources for the current refresh plan.

So how much time does it take if you use manual refresh in PBI Service?

You can refer to the link.

Refresh method 

Best regards,
Lionel Chen

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

[Anonymous]

@AnonymousThe error indicates that it cannot log into the data source within the specified timeout.

One option is to increase the login timeout settings.

The other option is to chat to your data source owner and see if there are any issues with the data source. Or if the network is congested and it cannot make the connection quickly?

[Anonymous · Accepted solution]

Hi,

It is solved and would like to share the solution here.

We use an Azure SQL cloud database, azure databricks and azure datafactory.

The azure datafactory starts the ETL every day at a certain time.

We programmed that at that time, the Azure SQL cloud database gets scaled from S2 to S7.

The refresh takes 3 minutes now.

[Anonymous]

Hi @Anonymous 

Thanks for your reply.

Are you sure when I add the error message (see below)? 

The data source is a SQL database. I have acccess to the database. What could I do or check in there?

Thank you

Last refresh failed: Wed Oct 07 2020 08:15:52 GMT+0200 (Midden-Europese zomertijd)
The refresh operation failed because it took more than 120 minutes to complete. Consider reducing the size of your dataset or breaking it up into smaller datasets.

[Anonymous]

@Anonymous From the below error i understand that your dataset is huge which is causing this issue
Firstly have you imported only the required columns in a table from SQL?as shown in belowscreenshot? Are you importing SQL views or tables?

Have you disable tabels which are not required from power query ??

[Anonymous]

Hi @Anonymous

No actually not. We import the complete table from SQL and in the query editor we deleted all unnecessary columns.

Do you think this is the reason? Isn't this the same?

[Anonymous]

@Anonymous  Without seeing the .pbix it is very hard to guess what is causing the failure....however here are some tips to reduce the dataset size

• Uncheck Auto Date/Time in your pbix by navigating through File Options & Settings àOptions àCurrent FileàData LoadàAuto Data/Time
• Instead of having many slicers in your report I would suggest you make use of the New Filter functionality option pane  which is present is right hand side prior visulization pane

• I would suggest you to Disable  the tables  in Power query editor  which are no longer needed after appending is complete.Click on the Enable Load to disable it. Make sure you are not using the respective table data for any visualization which will not work after this change

• Its always better to perform all the calculation measures in source (Ex:SQL)and load the data in power bi
  
  If this helped you kindly mark this as solution so that it can help others too