How to connect Power bi to Salesforce [The Complete 2022 Guide with Free Dashboard templates]

  • Gert K 
Salesforce Report

Introduction to power bi salesforce template

Lately, more and more marketing specialists, Business intelligence professionals, and skillful sales management teams have been focusing their attention on Business Analytics. “What is Business Analytics”, one may ask.

 

Business Analytics is nothing but taking in historical data and processing it to find different patterns, and trends and finding root causes of issues if any.

 

Any person who is familiar with Salesforce might wonder if the goal is just Business Analytics, “Wouldn’t Salesforce alone suffice? Why is there such a big hype about Power BI?”

This article answers that exact question. In this article, we will discuss how Salesforce can be integrated into Power BI, enabling Sales, marketing, and business professionals to visualize Salesforce data in the Power BI interface. For this, one will need to integrate SalesForce with Power BI. Let us see how.

 

In this This Tutorial, we will go through connecting Salesforce to Power Bi which includes  Salesforce Overview Report template to visualize your

 

Salesforce Contacts in a few clicks. You’ll have access to all Salesforce data and the whole process should not take you more than 5 minutes.

 

As a prerequisite to getting started you’ll need access to Salesforce. Please make sure that you have the necessary user rights to install an external app.

 

Introduction to Power BI

Power BI is a software that is a part of Microsoft Power Platform, which helps professionals create interactive, immersive visualizations with a focus on Business Intelligence.

 

It is a collection of software services, apps, and connectors that work together and convert unrelated sources of data into orderly, visually engaging, and interactive insights.

 

The data can be anything. It can be a simple excel sheet, cloud-based data, and even on-premise hybrid data warehouses.

 

Power BI can be incorporated into any platform. It helps connect data sources to visualize and bring to light income-producing information and distribute them consistently with internal teams and external business connections.

 

Introduction to SalesForce

SalesForce is a service as a software(SaaS) that is cloud-based. It is a popular CRM tool for support, sales, and marketing teams worldwide. Before knowing what SalesForce is, one needs to understand what CRM is.

 

CRM stands for Customer Relationship Management. Back in time, employees used to manually inscribe data and information. With the emergence of computers, excel-like software, the companies took advantage of this software.

 

But as the amount of data increased and resulted in vast amounts of data, it became increasingly difficult and time-consuming to handle such large amounts of data.

 

There emerged a requirement to effectively handle such an immense amount of user data. This meant a need to incorporate methods to improve customer satisfaction.

 

This was when CRM was introduced which handled and resolved all these existing issues.

 

To reduce the cost, keeping in mind an affordable solution, SalesForce was introduced as a service entirely online. Salesforce services allow businesses to use cloud technology to better connect with partners, customers, and potential customers.

 

Companies can track customer activity, market to customers, and many more services using the Salesforce CRM. 

 

 

Table of content

  1. Relation Between Salesforce & Power Bi

  2. How to connect Power Bi to Salesforce

  3. Connecting your Salesforce DATA to Windsor.ai

  4. Benifits of Power bi-Salesforce integration

  5. Power Bi Salesforce Template

 

 

 

Relation between Salesforce and Power BI

Rather than using Salesforce and Power BI separately, one can use these two software (platforms) together to create an explosive, all-rounder result.

 

Salesforce is more or less like the platform where customer data of one’s organization is stored. Power BI is where you visualize your collected data. 

 

Power Bi can be used to analyze business data and share insights with rich visualizations. It has both a desktop version and a cloud version. With the desktop version, one can avail of extensive data exploration and other advanced functionalities.

 

With the cloud version, there is a wide variety of interactive dashboards present at the user’s disposal. Salesforce is known as the foundation for an organization’s sales efforts.

 

With everything found on the Salesforce API, one will be able to investigate data. Gathering data from standard Salesforce objects like Opportunities,

 

Leads, Accounts, and Contacts accompanying the custom objects help us transfer data from the Salesforce API to Power BI.

 

Transferring such data into Power BI has many advantages as it combines sales data with different sources like billing or support or checks specific sales KPIs which are relevant and are making sense for an organization.

 

How to connect Power BI to Salesforce

To retrieve Salesforce data on Power BI, Power gives the users the capability to connect with their own Salesforce account with relative ease. To help grow one’s business in no time, visualizing the data and sales from the business will be of great help.

 

Exploring business and sales data on Salesforce KPIs will make it easier for one to direct their focus on better opportunities, understanding the pipeline health, and campaigns or related channels with a specific number.

 

One may also need insightful metrics to understand certain aspects of business using this data. 

 

All this will help one to understand what area needs to be focussed on and this, in turn, will help one make better business decisions. It helps one identify trends by highlighting various previously unnoticed aspects of the sales process and hence gives one an idea of which customers are profitable to the organization’s business.

 

Connection Power BI to Salesforce can be summed up into a three-step process. First, one needs to pull Salesforce data. Next, place the aforementioned data in a centralized space which makes it easy for Power BI to access it.

 

Now, the Salesforce Power BI dashboard can be created easily and data can be analyzed in minutes.

 

As a prerequisite to connecting Salesforce to Power BI, make sure that one has Salesforce access. Also, confirm that necessary user rights have been provided to install external apps.

 

Although there are numerous ways to aid a connection between Salesforce and Power BI, here are the two most efficient ways of achieving the same;

 

  • Enabling Salesforce APIs: Salesforce Bulk API is one of the lists of most active APIs which supports the purpose of working around the voluminous data and running on a completely batch mode simultaneously. 

 

  • These APIs are highly responsive to queries undertaking operations such as updating, deleting, and insertion of records. 

 

  • Assigning the Power BI’s in-built connector: By utilizing this method, a user just needs to deploy Power BI’s standalone connector. This is then followed by establishing a data source to visualize the data through it.

 

  • Moreover, while importing the data from Salesforce one could also fiddle with the purpose of reports that are to be generated through Power BI

 

In this article, we are going to speak of a much easier way to connect Power BI to Salesforce with the help of WIndsor.ai. TO make this connection, follow the steps given below:

Connect your Salesforce data to Windsor.ai

  1. Register or login if you already have an account
Windsor.ai Registration

Create an account for a free trial

  1. From the dropdown (as shown in the image below), select your Salesforce account. Remember to grant access.
Choose Data source

Choose between +40 connectors

  1. Next, select your destination. You can also use the search or add fields as shown in the image below:
Choose fields & metrics

Choose between +200 fields and metrics for Salesforce

  1. Copy your API Key from the “Select Destination” tab
Copy API URL

Get API URL

For the next step, there are three options. One can either use the Power BI web template or the Power BI Desktop Template and as a third option, use Power BI Desktop and uploading data from Web.

 

Add data directly from Web

 

  1. Go to Power Desktop and Select ‘Get Data’ option.
Power bi desktop

Get data from web in Power bi

 

  1. Add API URL in the popup
Add API URL

In pop up box add API URL to fetch data

 

 

  1. If using Power BI Web App, Install it using this link.   

         (Skip to step 4, if using the Desktop app.)

 

   4. Click on Connect your data link on the yellow background at the top of the     page.

 

Power bi Salesforce Service/Web app   5. Enter your API Key

 

Add APi Key

Add API URL in the correct box                                                                                      On the next screen click on Sign In

 

Data Loading in Power BI Salesforce

The data will be now loading. The time taken will vary according to the size of the data and can take up to a minute or two.

 

If you would like to explore the template on the Power BI desktop you can also download the latest version of the dashboard template in.PBIX   format.

  • Download the Power BI Desktop Template by clicking here.
  • In the template in the first screen, enter the API key from the previous step.

salesforce overview

 

Benefits of Power BI – Salesforce Integration

Microsoft Power BI is a service that helps lift an organization’s data handling capability. It is advantageous as it can be integrated easily into any existing structure and also can be used as a complete revamp of an existing system.

 

So what makes Power BI- Salesforce integration such a practical choice:

  • Fast Setup: Integration of Power BI with Salesforce can be effortlessly set up. It can be put in place with the organization’s data insights model at record downtime.

 

  • Cost-Effective: Combining Power BI with Salesforce comes with the major benefit of cost-effectiveness in mind. This will help average users as well as the huge organization by eliminating the dependence on expensive on-premises solutions.

 

  • Connecting Power BI with Salesforce can help economize and allot huge amounts of funds in the long run for various other enterprises by the organizations. 

 

  • In executing queries against Salesforce Objects, there are no ‘Excessive usage charges’ from Salesforce neither because of the number of queries per day nor the number of rows returned.

 

  • Real-time data: Power BI is the most accomplished data analysis service made for our current fast-paced world of business. Being able to refer to and make judgments based on real-time data is a great boon to the organizations. 

 

  • It helps them to transmogrify their course of action and fine-tunes their course of action liable to be subjected to the latest developments. Connecting Power BI to Salesforce means one gets to analyze and visualize all real-time data from Salesforce.

 

  • Collaboration: Power BI connects the entire workforce on Salesforce using a universal platform. This is designed to promote a synergic work culture.

 

  • Cross-Platform: By combining Microsoft Power BI with Salesforce, all the employees in an organization will be able to access data and insight from various platforms.

 

  • This enables the organization’s workforce to stay connected using their device from remote locations.

 

  • No Performance Degradation: No performance degradation has ever been reported against the Salesforce performance irrespective of the number of Power Queries run against it or simply due to the integration of Power BI and Salesforce.

 

To most, Power BI is one of the most promising tools for upscaling business intelligence to stay relevant in rapidly developing industries and markets while Salesforce provides the CRM for the same.

 

Limitations of Power BI Salesforce connector

 

Having no cons while having something as powerful as power BI Salesforce integration would be a great plus. But that is not the case while embedding Power BI in Salesforce. Let us look into the limitations:

 

  • Salesforce Limits: Since Salesforce was not devised to behave like a real-time depository for other tools and systems, it will not be able to handle the traffic and bandwidth that an approach as such would deem indispensable. So, Salesforce places a limit by itself on how much one can use it externally. For example:

 

  • Rows of data: One is not allowed to pull down more than 2000 rows of data when trying to pull data from Salesforce with the help of API. Unless one needs a run-down workaround, this will pretty much cancel using a large data set. This approach may work but it’s more likely to fail.

 

 

  • API calls: It is a limit in place put up by Salesforce on how much API can be hit Since it is not a very high limit, without realizing it, one might impact other users negatively. Imagine a situation when one mistakenly blew through the limit on API calls on Salesforce. 

 

 

  • Then, Salesforce will stop routing all and any inbound API calls to the Sales team Since it is not easily noticeable when the API calls reach their limit on Salesforce, it might take a while before the concerned team realizes that the leads or calls are not being routed to them. By then, a lot will be lost.

 

  • Authentication tokens: There will be a limit of five authentication tokens per application on Salesforce. So if one needs to have more than five Salesforce data sets to import, one may need to postpone the import or will need to find a temporary workaround

 

  • The complication in incorporating multiple data sources: Most of the time,  BI tools do a pretty reasonable job of combining Salesforce with another data source. 

 

  •  The tool will be even able to handle two different sources without causing  many adverse situations. But when the need arises to integrate more than one or two data sources, which is most likely to happen if the goal is to level up how one’s organization uses data, doing it with the help of a BI tool is more trouble than it’s worth.

 

  • No Single Source of Truth (SSOT): Even if one can successfully work out multiple data sources in the BL salesforce Integration, by doing so one is efficiently constructing a data warehouse on a really small scale. 

 

 

  • This is not just by a single employee in an organization but by each and everyone who is trying to manage multiple data sources on the BI tool. What makes this worse is each and everyone has their version of the same data set, their version of how terms are defined and data is and will be interpreted. 

 

 

  • TO resolve this issue, the commonly used resolution is Single Source of Truth (SSOT) SSOT means having one data repository which is built around a single set of data definitions. This will result in everyone in the organization working on a single version of the dataset.

 

 

Power BI Salesforce template Walkthrough

salesforce overview report template

Lead Table

What is a Lead in Salesforce?

Lead is a default object that gets created when a piece of new contact information is received in the database. Leads are usually imported. But that is not the only way leads are created. They can also be created automatically with the help of external marketing automation platforms.

 

The data relating to this individual entity such as name, title, contact information, company name, etc are stored. This information is then used to personalize any marketing directed towards them.

 

Creating Leads is an important and helpful feature as it prevents unwanted data from entering the system and clogging the marketing and sales initiative. Also, leads make reporting easier thereby making it easier to assess the effectiveness of marketing campaigns.

Account Table

What is an Account in Salesforce?

Account in Salesforce is a business entity or organization that one intends to sell to whose information is in the database. There might be multiple contacts stored which are all part of the same account.

 

The account represents an individual account, which is an organization or person involved with your business (such as customers, competitors, and partners).

 

Contact Table

What is a Contact in Salesforce?

Contact represents a person who is associated with an account. Specifically, a contact is an individual whose contact information is in the database and has been qualified.

 

Typically, they are part of a business or organization one is attempting to sell to, but could also be a referral partner or even someone one knows personally.

 

Opportunity Table

What is an Opportunity in Salesforce?

In Salesforce, an opportunity is a deal or a sale in progress. The lead has been qualified and vetted, and the AEs might be working to solidify contracts.

 

 Campaign Table

 What is a Campaign in Salesforce?

A campaign object is employed for storing information about a campaign organized specifically for lead generation or brand building. Lead generation campaigns usually involve direct mail, email blasts, web seminars, conferences, trade shows, and brand-building campaigns consisting of different types of advertisements.

 

Important Fields:

  • Active: It indicates whether the campaign is Active or Inactive. If a campaign is inactive then it is usually unrelated to other objects’ records.

 

  • Expected Revenue: Amount of money calculated to generate from the campaign.

 

  • Expected Response: Expected percentage of responses to be received for the campaign.

 

  • Num Sent: Number of individuals targeted by the campaign.

 

  • Parent Campaign: This allows multiple individual campaigns to be associated with a larger initiative.

Event Table

What is an Event in Salesforce?

Salesforce can be utilized to track meetings with customers, prospects, and colleagues. One can drill down from events to related records, feeds, files, contacts, etc. One can also track events in reports.

 

 

 

  • Event Fields: Event objects have event fields for tracking and recording event details.

 

 

  • Manage Events: In Salesforce, an event is a powerful tool for staying in the know of customer relationships and closing deals. One can relate events to opportunities, leads, accounts, and contacts and track them on those records, in the calendar, and in reports.

 

 

 

 

  • Calendar Sharing: One is allowed to give coworkers access to view or edit one’s calendar in Salesforce Classic and Lightning Experience.

 

 

 

 

  • Considerations for Using Events and Calendars: Salesforce always displays an event’s date and time in the time zone chosen in My Settings. Lightning Experience and Salesforce Classic have different workarounds for meetings and scheduling.

 

 

  • Scheduling and Invitations: Reps can invite people to meetings and see meeting responses in Lightning Experience, Salesforce Classic, and the Salesforce mobile app. Reps can also accept meeting invites and create an MS Outlook appointment for an event in Salesforce Classic.

 

User Table

 

What is a User in Salesforce?

Each user is uniquely identified with a username, password, and profile in Salesforce. Together with other settings, the profile determines which tasks a user can perform, what data the user can see, and what the user can do with the data. 

 

  • User Management Administration: As a Salesforce administrator, users in an organization can be managed. Besides creating and assigning users, user management includes working with permissions and licenses, delegating users, and more.

 

 

 

  • View and Manage Users:
    In the user list, you can view and manage all users in your org, partner portal, and Salesforce Customer Portal.

 

  • Licenses Overview: To enable specific Salesforce functionality for your users, you must choose one user license for each user. To enable more functionality, you can assign permission set licenses and feature licenses to your users or purchase usage-based entitlements for your organization.

 

  • Delegate Administrative Duties: Use delegated administration to assign limited admin privileges to users in your org who aren’t administrators. For example, let’s say you want the Customer Support team manager to manage users in the Support Manager role and all subordinate roles. Create a delegated admin for this purpose so that you can focus on other administration tasks.

 

  • Topics and Tags Settings: Topics on objects allow users to add topics to records so they can organize them by common themes. With Chatter enabled, users can also see related posts and comments. Enabling topics for an object disables public tags on records of that object type. Personal tags aren’t affected.

How Windsor.ai can help you create better reports.

Windsor.ai is a multi-channel attribution dashboard where one can connect their analytics data and display it on a single platform.Windsor.ai is ideal for combining data extracted from Salesforce in Power BI which is fairly straightforward and easy to understand when compared with its contemporaries.

 

As a user, one is provided all the control and access to work around all the aspects of the tool with relative ease. Even though one should be familiar with some basics of digital marketing reporting, Windsor.ai is easy to use. The platform is straightforward to use as one has control over the dashboard and can customize it to one’s needs.

 

As a marketing specialist, whatever the client wishes for in an automated marketing reporting tool, Windsor.ai provides it.

 

 

The template is available both as

This Power BI template has 3 report pages, Report, Overview, and Funnel, and can be customized with different fields, which you can find in available Salesforce data on an object level.

It queries the data through our connector and makes it available in real-time. Follow the steps outlined below to get your data connection.

This reports pulls the following objects and fields:

 

  • Account
    • account_id
    • account_industry
  • Lead
    • lead_id
    • lead_is_converted
  • Contact
    • contact_firstname
    • contact_lastname
    • contact_email
    • contact_department
    • contact_phone
    • contact_mailingcountry
  • Task
    • task_id
  • Opportunity
    • opportunity_account_id
    • opportunity_amount
    • opportunity_stage_name
    • opportunity_is_won
    • opportunity_last_activity_date
    • opportunity_close_date
    • opportunity_created_date,
  • Campaign
    • campaign_id
    • campaign_budgeted_cost
    • campaign_expected_revenue
    • campaign_name
    • campaign_number_of_converted_leads
    • campaign_number_of_leads
    • campaign_end_date

 

Choose your option:

Power BI Web Template

2a. Install Power BI Web App: Salesforce Overview Report

  • Click on Connect your data link on the yellow background on the top of the page.

Salesforce App

salesforce api key page

  • On the next screen click on Sign In

salesforce signin page

Your data should be loading now. Depending on your data size this may take a minute or two.

Power BI Desktop

 

If you would like to explore how the template on the Power BI desktop you can also download the latest version of the dashboard template in .PBIT format.

  • After completing the process of registering and connecting your data from Step 1

2b. Download Power BI Desktop Template: Salesforce Overview Report

  • In the template on the start screen, paste your API Key from Step 1 like here

Salesforce Template

Important: You will need to change the fields to their types (e.g. date, decimals, whole numbers, …) to use them in aggregations (sums, averages, …) or date filters. For users in Europe and other countries where a comma is used for decimals please change the locale of the data to English.

 

 

You migh also like to read:

 


Other Power BI Templates