Refresh DTUs decline (Azure SQL DB) and reaches times out (120 minutes)

[Anonymous]

Hi guys,

I have following MAJOR issue, which I need to solve.

I have a dataset (about 350 MB) which I uploaded to the Power BI Portal. The source is an Azure SQL Database with 800 DTUs.
The goal is to update the dataset 1-2 times a day.
Unfortunately, the refresh period exceeds the maximum of 2 hours and the dataset runs into a Time Out. When I update the dataset locally, I need about 2 hours 15 minutes. 

The DTU curve below is similar for both - Power BI Desktop and Power BI Online. 

Looking at the DTU consumption, it becomes obvious hat, that neither Power BI Desktop nor Power BI Online uses the maximum DTU capacity. In that case, I would stay below the 2 hour time period and could successfully update the dataset online. It starts with 100% DTUs and then quickly drops between 30 and 70%. (And often it stays at about 45%).

It would be awersome, if the Power BI Team could extent (!!) the Time Out by 1-2 hours. more without having the need to purchase Power BI Premium. I suppose, this would create many, many many more happy customers and developers. 

I attached the refresh of one of the many tries. 

[v-shex-msft]

Hi @Anonymous,

Based on your description, it seems like if your DTU/CPU are full load, your refresh request will spend more time which caused to timeout issue, right?

If this is a case, I think this issue will more related to your server side. You need to increase your server performance or optimize system resource consumption.

BTW, you can also try to submit an idea about increase the refresh timeout limit.

Regards,

Xiaoxin Sheng

[Anonymous]

Yes, but the problem is - it does not matter whether I try to refresh in the Power BI Portal OR Power BI Desktop locally. Neither uses the FULL Ressources but more or less just the half of the available DTUs / CPU Power. In Power BI Desktop it's not a big deal but as Power BI Portal is limited to 120 minutes, I run into a time out. Another possibility would be an incremental load but that's not available. 

Surprisinlgy, always the first 30 minutes are running at maximum DTUs but then it declines. 

[v-shex-msft]

Hi @Anonymous,

Based on your description, it sounds like the priority related issue. Have you try to increase the priority level about power bi refresh service?

In addition, you can also create a support ticket with trace log and local gateway refresh log as detailed information to power bi team.

Reference:

Tools for troubleshooting

Collecting logs from the gateway configurator

There are several logs you can collect for the gateway, and you should always start with the logs. The simplest way to collect logs after installing the gateway is through the user interface. In the On-premises data gateway user interface, select Diagnostics and then select the Export logs link near the bottom of the page, as shown in the following image.

Installer logs

%localappdata%\Temp\On-premises_data_gateway_*.log

Configuration logs

%localappdata%\Microsoft\on-premises data gateway\GatewayConfigurator*.log

On-premises data gateway service logs

C:\Users\PBIEgwService\AppData\Local\Microsoft\on-premises data gateway\Gateway*.log

Event Logs

The On-premises data gateway service event logs are present under Application and Services Logs.

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic.  You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

Troubleshooting the On-Premises Data Gateway

Regards,

Xiaoxin Sheng