Integrating Salesforce, SharePoint, SQL Server, and Power BI

Joel Plaut

July 26, 2022

All Post

We recently had a client who wanted to integrate Salesforce, SharePoint, SQL Server, and Power BI.

We already had Flows created for the Salesforce objects to flow from Salesforce to SharePoint as well as metadata along with the objects in real-time.

The user wanted to generate a report for Power BI that would show the Objects created in SharePoint as well as the documents with metadata.

Problem
The problem was that there were thousands of libraries that needed to be connected to Power BI and for each new library created, a connection is required to be set up in Power BI for the library to show in the Power BI dashboard.

Whenever a new library was created, a connection must be made with Power BI for the object to appear in the dashboard.

Resolution
As we know, repeated creation of new connections in Power BI is a tedious task, and creating a Flow to connect each library to a Power BI dashboard would be difficult and time-consuming as there were thousands of libraries.

To address this challenge, we decided to use SQL Server as the database for Power BI which would get the data from several tables and display it in the dashboard. That was solved and we were able to get the data in the dashboard.

When we are creating the library and document sets inside them, we are also replicating and propagating the same information into the corresponding SQL tables.

But the main problem was to bind something with each library that would capture any event happening in it. So, when a document was created/updated/deleted in the library, we needed to save the same entry with its metadata in the SQL table.

For that problem, we decided to use an RER. This is a Remote Event Receiver.

To handle library and library file events, we created the Remote Event Receiver (RER), which are web services that run externally to the SharePoint farm or SharePoint Online. The URL of the RER service is registered for the events it handles.

So as when a library was created, RER gets bound with the same. So, when an event occurs in a library, we can get the same information and create/update/delete an entry in the SQL table accordingly.

The RER works as follows-

The user performs an action in SharePoint (for example, edits a file in the library).
SharePoint then talks to the registered web service. You could perform some operations (for example, update a list item property, or update a backend system).
The web service can also talk to the Access Control Service (ACS) to request its own signed token to do a call back to SharePoint. Using this token, we are then executing actions.
So, the information in our case goes from Salesforce to SharePoint and from SharePoint to SQL, and from SQL to Power BI.

The below image depicts the flow of information in our case:

Inside the Flows as well we are checking the created Clients from the SQL and decide whether they are new or already existing.

When a new Client is created or an existing one is updated in Salesforce, the Flow gets triggered.  Below are the actions we perform in the Flow-

• Check whether the Client is already added to the SQL table,
• If no, it goes ahead and creates the library and adds the document set content type, and adds an entry in the Client SQL table with the Library’s GUID which can be used for further update/delete when needed.  We could if desired also create views, or add any other value-added actions.
• If yes, we check the SQL table and get the GUID and make further changes accordingly. If the metadata is updated or the Client’s name is changed, it is done on both sides SQL table as well as on the library.

Whenever a new project is created or an existing one is updated in Salesforce, the Flow gets triggered.  Below are the actions we perform in the Flow-

• Check whether the Project is already added to the SQL table,
• If no, it goes ahead and creates the Document Set and adds an entry in the Projects SQL table with the Library’s GUID, and item’s ID which can be used for further update/delete when needed
• If yes, we check the SQL table and get the GUID and item’s ID and make any necessary further changes accordingly. If the metadata is updated or Project’s name is changed, it is done on both sides; both the SQL table as well as on the Document Set.

Adding SQL Server as the source for Power BI reports helped a lot, as the sync time is quite rapid compared to other data sources.

The Reports in Power BI show the metadata along with the Clients and Projects information. Also, we are saving links to the Clients, Projects, and Documents in the SQL table that points to the Library, Document Set, and Documents in the SharePoint site respectively, so when they are clicked, it redirects to that object.