Best way to create an interactive power bi app with salesforce data?

[Anonymous]

Hello together

I would like to embed a Power Bi report in a Salesforce page (Visualforce). Among other things, I use data from Salesforce such as the Contact object in the Power Bi report to generate a stakeholder map for a group of accounts. We have over 300k contacts in our Salesforce organisation and actually only need  approximately a hundred for each account group view (we filter the report view based on url filter parameters and generate around 300+ account group views). What would be the best way to efficiently import contacts into Power Bi from Salesforce? Also, a user can edit a contact in Salesforce. Ideally, this change should be reflected in the Power Bi report as soon as possible (user should notice change when editing).

How could I solve this problem? What reference architecture should I use?

Any help would be appreciated!

Cheers

Kevin

[Anonymous]

Hey rbriga 

So my best two options are an incremental\direct refresh or a DB like SQL Server in the middle. Direct query probably doesn't make much sense because the performance gets bad. The thing is with direct refresh the Salesforce Object Connector does not allow to filter down the dataset (300k Contacts) to an acceptable level. You have to load the whole Dataset into Power Bi and then query hence a direct refresh will be slow. I never used an incremental refresh but this may be an option.

We actually have a SQL server so it would be okey to use it for this purpose. The only thing I am a bit worried is that it would create an unnecessary load on the salesforce DB when I query every minute for changes in the Contact table.

Could you send me in the right direction regarding documentation so I can look at both patterns?

[rbriga]

I don't have the knowledge to do so.

However, working with a View in SQL Server which performs all the filtering and transformations should be much faster than the Salesforce connector- at least in my experience.

I don't have any experience with incremental refresh or at such frequencies (minutes), however.

[rbriga · Accepted solution]

About contacts, SalesForce has a contacts table with an Account ID. 

You should be able to either join it or create a relationship (depending on the model) so a user can filter contacts by contact name, account name and more.

As for instant changes, I believe you'll need to either work with a DirectQuery or a hybrid incramental\direct refresh, incrementing over LastModifiedDate. Another option though is some database in the middle- SQL server, Snowflake, etc.- which will be updated at small intervals using LastModifiedDate. That's a DBA decision, though.

Usually for BI purposes, users understand that the data isn't necessarily live, but updates X times a day. I would avoid direct query unless it's necessary, as it disable some useful DAX and may reduce performance.