Power BI Dataflows and Salesforce Objects help needed.

[robarbie]

We have been using power bi dataflows to refresh about 30 objects from Saleforces for about 2 years successfully.  Our setup is an Enterprise grade reporting setup with many datasets and reports using those dataflows as sources.  We completely understand that Salesforce queries do not always query fold using pbi dataflows. We have read in some cases it folds and in some cases it will not.  We have also been running our refreshes non-incrementally and have decided to start switching the dataflows and the respective objects to incremental refresh.   We have had intermittent success, whereas, some of the Salesforce objects work fine and reliably and others do not.  We also understand that incrementaly refresh "may" require query folding between the two systems but do not know if this is true?  To add to this, we are also aware that Saleforce has a 10 query limit on the number of queries that run on any single service account at once.  We have worked around this problem by keeping the number of objects in each dataflow to 8 or less. 

So we have two questions that we would appreciate any help on or links to resources:

1. Has anyone had success with setting up many salesforce objects to incrementally refresh with power bi dataflows?  If so how do you do it? What is best practice? What are the secrets?
2. Has anyone has success getting salesforce objects to query fold when refreshing to the dataflwo? If so how did you do it?  What is best practice? What are the secrets?

@lbendlin can you offer assistance?

@v-kkf-msft 

@collinq 

[collinq]

Hey @robarbie ,

As @lbendlin  stated, you might be trying to push a rock uphill on this one and a third party tool might be the way to get the data more reliably. There are a number of third party tools that can extract the data in bulk, perhaps more efficiently, than can multiple data flows.  The issue here is the ability (and speed) with which you can get all of the data that you need that you eventually use within Power BI.  There are a number of tools (like Salesforce) that make exporting their data from the native tool difficult.

That said, with the limitations of number of concurrent queries the trick with Salesforce is to time out all of your dataflows.  You mentioned you keep them to 8 or so at a time.  By just the nature of the setup, you then have to have multiple dataflows that do NOT refresh at the same time as each other (since that would put you over 8 and closer to your maximum concurrent connections issue).  You have to be absolutely sure that the first dataflow with the 8 connections is totally done (and cleared from the Salesforce connection cache) before the next dataflow with connections begins.

I am not sure what incremental refresh issues that you might be having as you need to have a date field that you are using to do the incremental criteria.  If you are using a date field then you should be ok with the incremental.  BUT, again, you have the multiple simultaneous connections issue then that might be why your incremental is not working.

In the end, I think that the "secret" is to get the data out and I think that more often than not the best method is to get a full data dump on a regular schedule.  You might find a third party tool or create your own export using the built in Salesforce tools (like "Data Loader export").  

[lbendlin]

incremental refresh does not require query folding. It's prefered but not required.

What kinds of error message are you getting?  what's the cardinality of your objects?

In our environment we use Informatica to pull the SalesForce object data into our enterprise data lake on a schedule (multiple times a day).  It is quite a bit more robust than trying to do this from Power BI.

[robarbie]

We don't use Informatica, basically using data flows.  Trying to figure out if privacy settings are causing issues.

[lbendlin]

keep in mind that there is no love lost between SalesForce and Microsoft.  Better go with a neutral solution.