Forum Discussion
Receiving intermittent "invalid query locator" when refreshing Salesforce Object
Hi All,
I have come across this error as well. After weeks of 24 refreshes a day, with only the odd error, my refresh times ballooned and quickly began to fail (going from ~15 mins to 60 mins). Here are all the potential causes I've come across:
1) You have initiated more than 10 Saleforce queries at once
- See: https://help.salesforce.com/articleView?id=000323582&type=1&mode=1
- Based on my experience, this can be caused if you have more than one refresh referencing Salesforce objects at the same time. To trouble shoot, only have 1 refresh going.
2) Your cache in your desktop file is somehow interfering with the refresh
- Unsure how this would impact everything, but I did clear my cache and republished the data model
- I do not believe this changed anything with my refresh success
3) Same Salesforce object interacting
- This turned out to be my error.
- Here's what happened: I queried a Salesforce object (lets call in DimBase) then referenced DimBase in two subsequent tables (DimA and DimB).
- I then merged DimB to DimA in a way that permited query folding
- I'm definitely not an SQL expert but this query being sent back to Salesforce (via folding) very likely was the cause. Someone with more experience in SQL could likely explain the cause of this better.
- Once I changed this merge, the error no longer occurred and my refreshes resumes pre-error speeds / success rates.
The head scratcher for me was why is started occurring now, and not previously. It might have something to do with load on my the Power BI service and the query locators (see #1) expiring, but I'm unsure.
Hopefully this helps some users with this very frustrating issue.
Cheers
K
PS - on a side note, is Microsoft ever going to sort out pulling picklist values rather than Saleforce api's? Asking for a friend...
Hi All,
After I posted the above, I am still receiving intemittent errors, although not at the same frequency as before. I do believe that #3 of the above post was the primary reason for the errors; however, I did notice another issue that might have contributed.
As I was working through the queries and trouble shooting, I did notice that in the query editor I was getting an error of "Invalid Query Locator. Details: [List]". When I refreshed the preview, this went away so I didn't give it much attention. After significantly clean up my query, I was still getting this and I have traced it back to a object which is being merged with the object of issue. Within the object being merged, I was using a find and replace for some blank fields. When I removed this, the invalid query locator error no longer occurred.
I believe it has something to do with how Power BI constructs the SQL query for replace values, where completing this action will significatly impact performance.
This post somewhat explains why: https://stackoverflow.com/questions/22476662/sql-replace-statement-too-slow
Hopefully this helps some of you.
Cheers