cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmanimendra Frequent Visitor
Frequent Visitor

How to limit number of times that PBI automatically retries a SQL query

Hello All

 

Is there a way to limit the number of times the PBI desktop automatically tries to re-connect to a SQL data source when the query is killed through a SQL job? We had a situation where one of our team members started a long running query to refresh a PBI desktop data model before leaving work. While this is running our overnight incremental data loaded has started. However we have a mechanism in place to kills all the other jobs that consumes the database before it starts the data load. This has killed the PBI query that tries to load the data to PBI desktop. However apparently it started to reconnect the database and tried again. This has happened until the morning and had a major issue with the data load. Apparently there are no limitation in terms of PBI ability to reconnect to a query when it was killed or disconnected.

 

Any help on this very much appreciatedks

Menaka M

Thanks

 

Menaka M

7 REPLIES 7
Super User
Super User

Re: How to limit number of times that PBI automatically retries a SQL query

Hi @mmanimendra

 

Could it possibly be that there was a scheduled refresh happening?

 

Also was the data model in Import or DirectQuery mode?


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
mmanimendra Frequent Visitor
Frequent Visitor

Re: How to limit number of times that PBI automatically retries a SQL query

Thanks Gilbert

 

it wasnt a scheduled query but a long running query started by someone new to powerBI. Apperently DirectQuery mode has been used.

 

Please let me know if you need any additional details

 

Appriciate your help on this

 

Kind Reagrds

 

Menaka M

Super User
Super User

Re: How to limit number of times that PBI automatically retries a SQL query

Hi @mmanimendra

 

To clarify, was it using DirectQuery?

 

Or was it import mode, where it was a long running query?


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
mmanimendra Frequent Visitor
Frequent Visitor

Re: How to limit number of times that PBI automatically retries a SQL query

Hi Gilbert

 

my aplogies, it was actually a import mode. Query is using complex joins and sorts. due to this reason query was consuming resources from the server hevily. automatic process has killed (stoped) this query from running. but PBI seems to reconnect automatically.

 

Thanks

 

Menaka M

Super User
Super User

Re: How to limit number of times that PBI automatically retries a SQL query

Hi @mmanimendra

 

No worries, glad you found out it was in Import mode.

 

And I honestly have never tried to kill a TSQL query that is going via the Gateway. I could suggest possibly putting a time on how long before it times out in order to limit it in the future?


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
v-jiascu-msft Super Contributor
Super Contributor

Re: How to limit number of times that PBI automatically retries a SQL query

Hi Menaka,

 

How did your automatic process kill the query? I tried it by kill-transact-sql. It stopped immediately. There was an error in the Desktop. Please refer to the snapshot below.

How-to-limit-number-of-times-that-PBI-automatically-retries-a-SQL-query

BTW, the sessions started by Desktop could be more than one. 

And also the complex joins and sorts isn't a good idea in Power BI. It's better for the right tool to do the right thing.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft Super Contributor
Super Contributor

Re: How to limit number of times that PBI automatically retries a SQL query

Hi @mmanimendra,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 111 members 1,563 guests
Please welcome our newest community members: