Monday, October 10, 2016

Salesforce + Power BI – A Dynamic Connection

Data / Technology / Platforms

As we work with our customers who use both Salesforce and Power BI, Rightpoint is increasingly requested to help integrate these two popular solutions in order to provide reports showing pipeline and actual revenue by customer. Since Rightpoint uses both Salesforce and Power BI internally, I thought it would be useful to describe how we went about the integration at high level, and the benefits it has provided.

At Rightpoint, Salesforce serves as the primary book of record for sales pipeline, prospective customers, win/loss analysis and sales forecasting. However, when it comes to actual revenue, Rightpoint relies on an ERP solution from OpenAir (Netsuite). On a nightly basis, data from OpenAir is extracted into a Data Warehouse in SQL Server. We have connected Power BI to a number of additional data sources but for the purposes of this blog post, Salesforce and the Data Warehouse are the two primary data sources.

Step 1 – Identify Required Data Entities in Salesforce

At Rightpoint, we use Salesforce as the book of record for the following major data entities:

  • Opportunity
  • Account
  • Customer Satisfaction Scores
  • Pipeline Goal
  • Closed Revenue Goal
  • Campaign
  • SalesforceUser

It is important to note that at Rightpoint we map the Salesforce Account (which is a unique identifier within Salesforce) to the Customer Account ID from our ERP system so that we can relate pipeline to actual revenue.

Step 2 – Load Data from Salesforce to Power BI

For each entity identified above, we load the data to Power BI. In order to accomplish we click on Get Data option in Power BI Desktop and select Salesforce Objects as shown below.

clip_image002

We connect to Salesforce Production and select the Salesforce objects. Once we select the Salesforce object, Power BI presents a preview window as shown in example below for the Account object:

clip_image004

We can load the Salesforce object data to Power BI or choose to Edit the data. Most of the time we will choose to Edit the data to ensure that data types are consistent (particularly for those columns that will be used to establish relationships to other entities within Power BI), remove columns that are not needed, or perform other data transformations as needed for the solution.

Step 3 – Load ERP Data from Data Warehouse

As I mentioned, at Rightpoint we perform a nightly load from our ERP solution (where actual revenue is recorded) to our Data Warehouse (SQL Server). The major entities that we load from our Data Warehouse to Power BI include:

  • Project
  • Project Billing
  • Employee
  • Backlog
  • Customer

In order to load data from our Data Warehouse, we click on Get Data in Power BI Desktop, choose SQL Server and enter the server as shown below (we have chosen to Import rather than use Direct Query), :

clip_image006

The next dialog will prompt you to access the server using either Windows credentials or SQL Server credentials. Once we enter those, you will be prompted to select the database, and entities you wish to load to Power BI:

clip_image008

Similar to the Salesforce data entities, we can click on Edit to ensure that data types are consistent (particularly for those columns that will be used to establish relationships to other entities within Power BI), remove columns that are not needed, or perform other data transformations as needed for the solution.

Step 4 – Manage Relationships

The next step is make sure that the data entities we loaded to Power BI include the proper relationships. The graphic below highlights several of the key relationships necessary to develop reports that show both customer pipeline and actual revenue.

image

Step 5 – Create Power BI Reports and Dashboards

Once we have finalized the appropriate relationships we are now ready to create a report in Power BI. The below graphic is a table showing both opportunity amount (from Salesforce) and Total Billing (from Data Warehouse) for customer accounts. We can certainly create much more sophisticated reports and visuals using Power BI including bar, column, scatter charts, etc.

clip_image018

So that’s it. I hope that this blog demonstrates that combining Salesforce data and actual revenue is greatly simplified with Power BI.