Power BI refresh error : Dedicated SQL Pool: Execution Timeout Expired.

[Sivanesan]

Hi Everyone

I am getting below error while refreshing one of my dashboard(For a single view) in both service and Dekstop. Datasource is Dedicated SQL Pool.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: Microsoft SQL: [ccon]Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.[/ccon]. DataSourceKind = [ccon]SQL[/ccon]. DataSourcePath = [ccon]XXXXXXXXX[/ccon]. Message = [ccon]Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.[/ccon]. ErrorCode = [ccon]-2146232060[/ccon]. Number = [ccon]-2[/ccon]. Class = [ccon]11[/ccon]. State = [ccon]0[/ccon]. [/ccon]. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. 

When I tried to execute the select query directly in the database using SSMS it runs for more than 20 mins and throws below error.

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

How should I approach this. As this is a Azure managed service I can't get deep into troubleshooting.

Thanks

[ibarrau]

Hi. By default the SQL Server or SQL sources have a timeout of 10 minutes. So if your query is taking longer, you need to increase that value or check if you can optimize the response from the BD.

In order to increase the timeout you can read the power query source step function like: https://learn.microsoft.com/en-us/powerquery-m/sql-database

The commandTimeout is the key parameter. If I'm correct, it's something like this:

Sql.Database(server as text, database as text, [CommandTimeout = #duration(0,0,30,0)] )

 Duration it's configured for 30 minutes in that example.

I hope that helps,