Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi @mmanimendra,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @mmanimendra
Could it possibly be that there was a scheduled refresh happening?
Also was the data model in Import or DirectQuery mode?
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
Hi @mmanimendra
To clarify, was it using DirectQuery?
Or was it import mode, where it was a long running 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
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.
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
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?