Optimizing Salesforce Data Integration with Power BI: Best Practices and Recommendations

[Lightsong90]

Hi all,

I've introduced Power BI to the executives at my company, and they're very happy with it so far. They want to expand Power BI's adoption for our reporting needs. I'm looking to identify the best practices within a Salesforce/Power BI ecosystem.

My current method:

- Use the built-in connector to query Salesforce Objects to get data

- Build my model in Power BI Desktop

- Build my report on top of the model

- Publish my report and model to the service using a Pro license

- Set up automatic refreshes

However, I'm wondering if there's a better approach. Is it possible to pull data directly into the Power BI Service from Salesforce, have that data automatically refresh throughout the day, and then use that repository for creating models and reports?

What are some best practices for the Power BI/Salesforce ecosystem? My company is open to switching to Premium Per User licenses if it improves the process.

Thanks for your insights!

[lbendlin · Accepted solution]

Neither Salesforce Objects nor Salesforce Reports connector offer real time (Direct Query) connectivity. Both are strictly import mode.  You cannot expect Power BI to do real time Salesforce reporting.  If you need real time reports, use Salesforce Reports, in Salesforce.

Remember that the Salesforce Objects connector officially does not support query folding.  This can be a problem if your objects have many millions of members. (Inofficially some simple transforms are actually converted into custom SOQL, even though the "View Native Query"  option says otherwise. However, you cannot submit your own custom SOQL, and child queries are not possible.)

There is a third option - export SFDC Reports to CSV, store that CSV on a Sharepoint, and then ingest in Power BI. This is suitable for large amounts of data from complex report types, where real time is not a requirement.