Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Invalid Query Locator ISSUE - Salesforce Dataset

Hello.

 

I've created a Power BI report pulling data from Salesforce objects. Performed a lot of data modeling, appended queries, merged queries etc etc...

 

The reports worked and refresh fine when in Power BI Desktop. When I published to the web, this error keeps popping up and the dataset does not refresh. Sometimes it does, sometimes it doesn t. I've already contact Power BI support directly and Salesforce support also, none of them were able to solve the problem. 

 

Here is the error message:

 

There was an error when processing the data in the dataset.

Please try again later or contact support. If you contact support, please provide these details.

Messageinvalid query locator Table: ROT Parceiro 3.
Cluster URIWABI-SOUTH-CENTRAL-US-redirect.analysis.windows.net
Activity ID5c929b5d-0e4d-41b5-9e6a-8d53a28e2b08
Request ID758ea88d-e61c-df6a-dc28-0525e4c7aa4b
Time2020-02-21 13:50:29Z


This is the error message when I try to update the dataset report into Power BI Service.

 

Does anyone has any ideia how to solve this? I saw some posts regarding the same issue, but none of them could help me.

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello. Thanks for your support.

       

      Do you know what a query locator cursos This article attest that salesforce user must have up to 10 query locator cursor, but I can t quite understand how can I manage that limit in Power BI Desktop.

       

      Thanks.

  • QueryWhiz's avatar
    QueryWhiz
    Post Patron
    2 years ago

    Hi, were you able to find a solution? I've used the Salesforce connector but I had some issues sometimes. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

     

     

    After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

     

     

    There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

     

  • metrica's avatar
    metrica
    Post Prodigy
    1 month ago

    Hi there,

    I would approach this as a Salesforce query cursor issue, not as a normal refresh or credentials issue.

    “Invalid Query Locator” usually means Salesforce created a cursor to paginate query results, but by the time Power BI tried to continue reading, that cursor was no longer valid. This can happen when the dataset opens several Salesforce queries in parallel, when append or merge steps keep multiple queries active, or when other tools are using the same Salesforce user at the same time.

     

    A few things I would test first:

    1. Use a dedicated Salesforce integration user for Power BI only.

    2. Check whether other API jobs are running under the same Salesforce user during refresh.

    3. Stagger scheduled refreshes so multiple Salesforce datasets do not hit the org at the same time.

    4. Reduce the number of Salesforce queries in the model.

    5. Avoid heavy append and merge logic directly on top of live Salesforce objects.

    6. Stage raw Salesforce tables first, then do joins and transformations in Power BI or dataflows.

    7. Select only the fields you need and apply filters as early as possible.

    8. In Advanced Editor, make sure the Salesforce connector uses a supported API version.

    If the model has a lot of Salesforce objects, joins, and transformations, another option is to move more of the extraction design to the Salesforce side.

     

    Power BI Connector for Salesforce by Metrica Software is available on Salesforce AgentExchange / AppExchange:
    https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577

    With this connector, you create a prepared Salesforce data source first, select only the objects and fields needed, apply filters, preview the output, and then connect Power BI to that cleaner source. SOQL mode is also available when you need more control over the Salesforce-side query.

     

    This will not magically remove Salesforce API behavior, but it can reduce the number of broad, concurrent, and transformation-heavy queries that Power BI sends to Salesforce during refresh.

     

    There is a 30-day free trial, and the docs are here: https://metricasoftware.com/docs/salesforce/

    Support: https://metricasoftware.com/docs/salesforce/contact-support/

     

    Happy to help review this kind of model and suggest how to split it into cleaner Salesforce data sources for refresh.

     

    Cheers,
    Metrica Team