How to resolve OLE DB or ODBC timeout error when pulling in a large object from Salesforce

[rharrison2641]

Hello,

I will preface this by saying that I am new to PowerBI, but experienced in Salesforce. I am trying to pull in several objects into a PowerBI report by going to Get Data>Salesforce Objects>Custom.

The problem I'm having is that there are hundreds of thousands of rows related to the specifc object I am trying to pull in. I understand that this is an error we get when (the DB or PowerBI? Not sure) times out (the timeout occurs after about 5-10 min of trying to pull in the object):

Does anyone know the best way to solve this problem, specifically related to Salesforce? I read that I should adjust the timeout settings in the DB I'm pulling from (in this case, Salesforce), but I'm not too sure how to do this.

Any help would be much appreciated!

[metrica]

Hi @rharrison2641,

The OLE DB/ODBC timeout on Salesforce Objects is almost always a volume issue, not a configuration one - Power Query tries to pull the entire object (hundreds of thousands of rows + all columns by default) before any filtering applies, and the default command timeout (10 minutes) isn't long enough.

Two native paths worth trying first:

1. Increase command timeout in the M code (Power Query Editor → Advanced Editor): add [CommandTimeout=#duration(0, 0, 30, 0)] to your Salesforce.Data(...) call to bump it to 30 minutes. Doesn't help with the underlying volume, but buys you time.
2. Use the Select Query advanced option when connecting - supply a custom SOQL with only the fields you need (SELECT Id, Name, CreatedDate, ... FROM YourObject WHERE LastModifiedDate >= LAST_N_DAYS:90). Shorter query, much smaller payload, no timeout.

If hand-writing SOQL isn't ideal, Power BI Connector for Salesforce on AppExchange handles this through a UI inside Salesforce:

Why it might fit:

• Filter conditions and field selection configured on the Salesforce side - only the rows you need are pulled, no timeout
• No 2,000-row limit on reports
• Supports custom fields, custom objects, and joined reports
• 30-day free trial
• Setup docs: https://metricasoftware.com/docs/salesforce/
• Responsive support if you get stuck: https://metricasoftware.com/docs/salesforce/contact-support/

Happy to set up a quick demo on your specific object. Just reach out via the support link.

Cheers,

Metrica Team.

[ReportGuru]

Hi @rharrison2641 were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the SF objects API. 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. 

[Anonymous]

Hi, @rharrison2641 

It is best to follow these rules when pulling data:

1. Clear the catche in the desktop file

2. Dont load entire data load only required amount of columns and rows from DB.

3. Dont load all the tables at once, load one by one tables

4. Turn off auto detect relationships in options this will also impact the time. 

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum