Salesforce and large query

[jdballard30]

I have Salesforce.com as a data source. It's querying a large table that in the last few days went from several thousand rows to almost 500,000 (which was expected.) Now, when I refresh Power BI it's taking forever and occasionally timing out on the query of that object. (The object is Task if you're curious.) I'm querying the table and immediately filtering to the last five days, but that's not helping because I'm assuming Power BI is returning everything and taking forever to do it, and then filtering to the last five days. Assuming that's the case, is there any way to force Power BI to make the API request include the filter, so Salesforce does the filtering. I really only need the last few days of data returned to me, and without that filter, it's taking forever.

[CoderZen08]

Hi @jdballard30 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 and lets you pre-filter the data on the tool UI by selecting the fields and date range you wish to export, I think this could solve your problem. 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. 

[v-yuezhe-msft · Accepted solution]

@jdballard30,

Using the built-in salesforce connector, it is only possible to filter data after importing the whole table in Power BI Desktop. There is a similar idea about adding salesforce query in Power BI , please vote it up.

In your scenario,  you can check the following options to work around this issue.

1. You can contact Salesforce support to check if it is possible to add filter in Salesforce Rest API. You can use Web connector in Power BI Desktop to connect to the API with filtered data.

2. Create a specific report in Salesforce and connect to the Salesforce report in Power BI.

Regards,
Lydia

[jdballard30]

Thanks, Lydia. I had gone ahead with the report idea. I'd considered using the web connector, but I thought I recalled reading somewhere that web connectors couldn't use a scheduled refresh. I'm not sure if that was for a prior version, and it's now possible, but being able to set up a scheduled refresh is required in this case.

[v-yuezhe-msft]

@jdballard30,

It is possible to set schedule refresh in this case.

Regards,
Lydia