How to extend Power BI service timeout limit

[katherine_stz]

I have a report using DirectQuery on an extremely large dataset in SQL (over 30 GB). I'm running into a timeout issue on Power BI service because some queries take up to 10 minutes to load and the limit seems to be a little over 200 seconds. I have access to Premium/Pro. I've found some similar questions on the forum but no clear answer. How can I extend this limit?

[lbendlin]

As a side note - in Direct query mode any response over five seconds will result in unhappy users. Make sure your data source is fine tuned to the queries generated by Power BI. Indexes, statistics, the whole thing.

[v-janeyg-msft]

Hi, @katherine_stz 

Have you tried entering timeout minutes when connecting?

Like this:

Best Regards,

Community Support Team _Janey

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

[DaxPadawan]

Hello @v-janeyg-msft ! Can I ask if this means that this setting will be carried over to Power BI Service? We are connecting to an on-prem SQL Server using a Gateway.

[lbendlin]

The answer is a definite maybe.  If you specify it in Power Query then it will most likely be honored unless your data source enforces its own (lower)  limits.

[DaxPadawan]

Got this, thank you.

[lbendlin]

In your data source definition you can add both Connection Timeout and Command Timeout option modifiers.  Note that there is a hard 600 minute limit on the service.

Sql.Database - PowerQuery M | Microsoft Docs

[katherine_stz]

This doesn't seem to be working for me. I'm using direct query and have this line of code: Source = sql.database('server name', 'database name', [Query = dbquery]) where dbquery is defined in a previous line so as to not import the entire dataset in (very large, would take forever). It gives me errors saying that I am passing in unacceptable parameters when I add the commandtimeout parameter. Any suggestions on how to work around this?

[lbendlin]

please show what you have tried to add there.

[katherine_stz]

The following is what my query code looks like:

let
//Pull in a values from the parameter table

//Create the query
dbQuery = "Select * FROM table WHERE var1='" & Parameter1 & "' and var2 in ('" & #"Parameter2" & " ','" & #"Parameter3" & " ','" & #"Parameter4" & " ')",

//Get the data
Source = Sql.Database("server","database",[Query=dbQuery])
in
Source

I structured it like this so the query doesn't try to load all 30 GB.

When I try to add any timeout options I get errors and I cannot access the SQL server database pop up as janey has commented.

[v-janeyg-msft]

Hi, @katherine_stz 

This is the code after I use DQ mode to connect SQL and add 10min timeout when connecting, you can reference:

If it doesn't work, Please feel free to ask me.

Best Regards,

Community Support Team _Janey

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

[DigestDataHumbl]

When I try to do that, I also get errors just as lbendlin. Mine says, 
'CommandTimeout' are not valid options. Valid options are: 'CreateNavigationProperties.'
How are you avoiding the error or how would you use this valid option?

[lbendlin]

"When I try to add any timeout options I get errors"

How are you trying to do that and what errors do you get?