Forum Discussion
Salesforce and large query
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.
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
4 Replies
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,
LydiaThanks, 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.
jdballard30,
It is possible to set schedule refresh in this case.
Regards,
Lydia
Hi jdballard30
For a dataset this size, the filter should be applied before the data reaches Power BI.
Power BI Connector for Salesforce lets you select only the required fields and filter Task records at the source using visual filters or SOQL. This avoids exporting the full object and then filtering it in Power Query.
If you use the Salesforce Reports connector suggested above, note that it is limited to 2,000 rows. The Metrica connector does not have that report row cap.
AppExchange and 30-day free trial: https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577
Documentation:
https://metricasoftware.com/docs/salesforce/Support and demo:
https://metricasoftware.com/docs/salesforce/contact-support/Cheers,
Metrica Team
