Salesforce Security Sharing Rules in Microsoft Power BI
Executive Summary
The project was executed by creating a security layer in Microsoft Power BI on Salesforce Opportunities. Salesforce Opportunities is highly sensitive and should only be accessed by authorised users. Since Salesforce would not disclose their sharing rules table with any third party, in order to correctly develop the solution, it was essential to replicate their rules in Microsoft Power BI using a few of the existing Salesforce Objects. It was also vital to improve the Salesforce security systems specifically for Opportunities. Salesforce, Microsoft Power BI and SharePoint were employed to converge and replicate the Salesforce security sharing rules.
Problem Statement
The requirement of this project was to bring the Salesforce Opportunity data into a Microsoft Power BI dashboard. The problem with visualising this data was that the Opportunity data were highly sensitive and required a security layer.
Salesforce has its own security in a sharing table, which is not available to the public. In Microsoft Power BI there is a Salesforce report connector which has a limit of 2000 rows. There is also the Salesforce Object connector which does not have this limit; thus we used this connector. Without adding this security layer, unauthorised people would be able to view the Opportunities, however, access should be prevented. Such an omission would have a disastrous effect on the markets. Without such a solution, it would not be possible to use this data, which would be an extremely high risk to the bank and its clients.
Background
The entire bank works off Salesforce in order to load Opportunities for specific authorised members of each department sector to view. This has been working well for several years, however, as outlined above, security issues arose and thus a solution in Microsoft Power BI was required.
Purpose
The purpose of this project was to develop a user-safe access solution for Salesforce Opportunities by creating a security layer in Microsoft Power BI. Salesforce Opportunities is very sensitive and should only be accessed by authorised users. As Salesforce Opportunities would not disclose their sharing rules table with any third party, in order to correctly develop the solution, it was necessary to replicate their rules in Microsoft Power BI using a few of the existing Salesforce Objects.
Scope
The scope of the project was to improve and govern Salesforce security systems specifically for Opportunities. Here, Salesforce, Microsoft Power BI and SharePoint were employed to converge and replicate the Salesforce security sharing rules.
Solution
In order to proceed with the project, a number of investigations and tests needed to be simulated, which started with determining if we could pull the security by testing an individual user in Row Level Security (RLS) in Microsoft Power BI. This proved to be successful.
The next step was to use the Salesforce Objects connector, which was needed to create the Salesforce sharing rules. It was then necessary to add the Role ID’s within the bank manually in a SharePoint file and then create the sharing rules and tests. Again, the test was successful.
A refresh was not possible in Microsoft Power BI Service as a result of the dataset being too large for the cloud. Furthermore, with every new Opportunity that came into Salesforce, the dataset grew larger. After many optimizations the data set was reduced and cloud refreshes worked perfectly.
Benefits
Adopting the above security solution for a company that already deploys Salesforce results in enhanced security within Microsoft Power BI. When using these tools, no unauthorised personnel is able to view an Opportunity; for any reason whatsoever. In order to implement and run the above solution, the in-house Salesforce team will need to be notified and a developer contacted in order to have the model built into Microsoft Power BI. Consideration must be given to the dataset size in relation to the number of users in the organisation. Microsoft Power BI Premium is needed to run this type of integration.
Conclusion
In summary, the aim of the project was to bring the Salesforce Opportunity data into a Microsoft Power BI dashboard. The problem with visualising this data was that the Opportunity data were highly sensitive and required a security layer. Salesforce, Microsoft Power BI and SharePoint were employed to converge and replicate the Salesforce security sharing rules.
About the Author
Name: Vikash Maharaj
Email: [email protected]
LinkedIn: https://www.linkedin.com/in/maharajvikash
I am a Business Intelligence Analyst. I have completed a B-Tech in Information Technology, a MCSA in SQL Server 2012/2014 and a Microsoft certification in Microsoft Power BI. I have worked on several projects as a consultant within the finance sector and I am currently employed as a Business Intelligence Developer and Microsoft Power BI Developer on the project discussed above.
Pulling in the Salesforce sharing rules within Microsoft Power BI has never been completed before and I have managed to accomplish this with the assistance of the in-house team whom I worked with on this project. If you would like the technical details of what was conducted, please contact me via email.