Embedding a Power BI report into Salesforce

 ⚠ Note

This post is archived. Content may be outdated, and links may no longer function.  Please reach out to our community if you have questions about specific posts.

Introduction

Many people use Salesforce to manage their Accounts and keep track of their Opportunities. They often use Power BI to visualize Salesforce data and bring in additional data sources, but that means switching platforms often. This walkthrough addresses that and shows you how to embed a Power BI report in a Visualforce page inside Salesforce, allowing you to view all your data and reports in a single application.

All the Salesforce classes and pages used in this walkthrough are available for download. They can be uploaded to your Salesforce instance directly, with a couple of minor configuration changes once they’re in the target environment.

This is what an embedded report looks like if you follow the steps in this walkthrough:

What follows is an in-depth explanation of how all the different components work together.

Prerequisites

There are three main prerequisites for embedding a report into Salesforce:

1. A Power BI or Power BI pro subscription that contains the report to embed
2. A Salesforce instance
3. An Azure Active Directory (Azure AD) instance

Power BI embedding

The Power BI Embedded and the Power BI service are separate offerings. Power BI Embedded features a consumption-based billing model, is deployed through the Azure portal, and is designed to enable ISVs to embed data visualizations in applications for their customers to use. The Power BI service is billed and deployed through the O365 portal and is a standalone general purpose BI offering primarily targeted at enterprise internal use.

For this walkthrough, we will be using the Power BI service.

Logical flow

To embed a report into Salesforce, we follow this basic authentication flow:

1. Use OAuth 2.0 to authenticate against the Power BI service through Azure AD.
2. Once authenticated, and we have the access and refresh tokens, we make a request for the report through the Power BI JavaScript library.
3. The library returns the report and renders it in an iframe.
4. When the access token expires, we get a new one using the refresh token.

Two types of tokens are used throughout the flow – the access token and the refresh token. The refresh token eliminates the need for the user to log in every time they want to view a report.

Azure Active Directory registration

The authentication flow from Salesforce to Power BI is an authorization code flow. For this flow to work, you must register an application in Azure AD. This establishes an identity for your application and specifies permissions to Power BI REST resources. To register an application, follow this guide.

Integrating into Salesforce

We described the basic Power BI embedding flow above, but there are several Salesforce components that need to be created to allow report embedding:

• A Custom Setting, and an Object within it
  This will hold all information regarding our call to Power BI (URLs, client ids, secrets)
• The Power BI JavaScript library
• A basic OAuth controller
  This can be reused in other OAuth scenarios as well
• An OAuth controller for Power BI
  This extends the basic controller
• An entry in the Remote Site Settings for Azure AD
  This will allow the use of refresh tokens
• A Visualforce page that will host the report

Note: we used a DeadlyPenguin blogpost as the basis for the Salesforce part of the OAuth flow.

Creating the Custom Setting

Like the above blog post, we create a Custom Setting that we then populate with some data.

To create a Custom Setting in Salesforce follow these steps:

1. From your Salesforce home screen go to Setup (right hand upper corner).
2. On the left-hand side of the screen, expand the Develop tab.
3. Once expanded, there will be a Custom Settings option.
4. Click New, give the setting the name “OAuthApp_pbi” (the script uses this name) and save it.
   Ensure the Settings Type is ‘List’.
5. Now click the New button under Custom Fields and add the following fields.
   

   Field Label	Data Type
   Access Token URL	Text (255)
   Authorization URL	Text (255)
   Client Id	Text (255)
   Client Secret	Text (255)
   Resource URI	Text (255)
   Token Expires On	Text (255)

6. Once the fields have been added, go back to the custom setting definition. You should see a screen like this:
7. Click Manage. Now click New, and you will see a page that has all the fields we created. We now need to give them appropriate values. The field description tells you what needs to go in each field.

Field Label	Description
Name	‘PowerBI’
Access Token URL	The OAuth 2.0 URL used to retrieve the access token. For Azure AD, it’s usually: https://login.microsoftonline.com/common/oauth2/token
Authorization URL	The OAuth 2.0 URL of the authority that will provide the first token. For Azure AD, it’s usually: https://login.windows.net/common/oauth2/authorize
Client Id	The Azure AD application Client Id.
Client Secret	The Azure AD application Client secret.
Resource URI	The OAuth 2.0 URL of the resource we are trying to access. In this case, it is: https://analysis.windows.net/powerbi/api
Token Expires On	This field stores the value of the refresh token obtained each time a new access token is retrieved. It does not need to be filled in with the rest of the other fields, as it will be filled in automatically.

Remember the name of the entry in the Custom Setting table as we will need it later. (For this walkthrough we used ‘Power BI’; you can change the name but you will also have to change the application name on the Visualforce page later.)

Uploading the Power BI JavasScript library

Next, add the Power BI JavaScript library to the Salesforce environment as a static resource. The file can be found here. Once on the Static Resource page, right click and select ‘Save As’ on your local machine.

Upload the file to Salesforce by navigating from the main page to Setup -> Develop -> Static Resources. Select New, name it ‘PBIJS’ (the script uses this name) and click Save.

The result should look like this:

Adding the Power BI OAuth Controllers

We use two Salesforce controllers: OAuthController and PowerBIController.

Add the controllers to Salesforce by following these steps:

1) From your home page go to Setup -> Develop -> Apex Classes

2) Click New and copy and paste the code from the OAuthController

3) Repeat steps 1 and 2 for the PowerBIController

The result looks like this:

The OAuth flow is described in the initial blog post.

As part of the OAuth flow, the controller crafts an authentication URL. When the user logs in against that URL, the controller appends to the URL the client ID, the secret, the resource for which the log in is intended, and the redirect URL (our report page). This is done in the OAuthController method getAuthUrl().

Azure AD asks the user to authenticate with their own credentials and it checks if the user has access to the presented resource (Power BI). The request is not done directly to Power BI, but to the web application that we have created and presented to Azure AD through the client ID and secret. In turn, the web application has access to Power BI as a service.

Once the authentication is successful, the server returns an authorization code to the redirect URL that is specified in the Azure AD web app configuration. The URL includes a ‘code’ parameter, which means the user is authorized to access the presented resource. Once we receive the redirect and check that the URL has the parameter, we validate it through the redirectOnCallback() method. This method makes another call to the Azure AD access token URL with an ‘authorization_code’ grant to which it appends the received code.

Once the call succeeds, the access and refresh tokens are retrieved from the response, as well as the ‘expires_on’ property.

Compared to the DeadlyPenguin blog post, the main addition to the flow is that we have a token refresh mechanism. This is so the user doesn’t have to log in every time they want to see the report:

• We added a new method to OAuthConroller that refreshes the access token, once the expiration date has passed. The flow is like that of getting the initial access token, although this time we’re using a ‘refresh_token’ grant type.
• The other slight difference is that the refresh method doesn’t receive a redirect, nor does it try to validate the token. It uses the same authentication URL with a different grant type.
• Once it receives the new tokens from Azure AD, it updates the cookies and the ‘Token Expires On’ property on the custom setting entry.

Remote Site Settings

This setting allows the controller to make a call against Azure AD from within the Salesforce environment. If this setting is not enabled, Salesforce will block any outgoing calls from the controllers and we will not be able to refresh the access token.

From the homepage, navigate to Setup -> (Administer Tab) -> Security Controls -> Remote Site Settings. Click New and add a new entry with a name of your choosing, and the root authentication URL. In the case of Azure AD this should be ‘https://login.microsoftonline.com’.

Embedding Power BI reports using a Visualforce page

To add the Visualforce page, follow these steps:

1. From the homepage, navigate to Setup.
2. On the left-hand side, expand the Develop tab.
3. Click on Visualforce pages.
4. Click New and copy and paste the code from the ReportPage file.
5. Add your report id in the embedConfiguration object.

The report ID can be accessed from the Power BI website. Navigate to the report (which you must own), and the ID will be in the URL.

The Visualforce page contains the logic to start the token refresh flow and the configuration required to embed the report.

The check for a refresh token is done on the OnLoad method. If it does not exist, the page redirects to the authentication URL for a first-time login.

If the refresh token exists, it checks the expiry date on the access token and if it’s less than the current date it will refresh it by calling the token refresh method on the Power BI controller.

The report embedding configuration is comprised of the type of entity that we want to embed, a report, its ID, the embed URL, and a settings object. The settings object allows further options to be displayed in the report itself, such as navigation and filtering.

View GitHub for a more detailed explanation of the configuration options.

Sharing a report with members of your organization

To share a report with other members of your organization there are two requirements.

1. The user needs to have a Power BI account
2. They need to be a member of the same Azure Active Directory where the AAD application has been created

Once those requirements are met there are two ways of making the report accessible to them.

1. Share the dashboard that contains the report in Power BI (the report GUID will stay the same)
   • Link: How to share a dashboard
   • Once shared, the user can log into Salesforce, navigate to the report page where they will be asked to log into Azure Active Directory, and view the embedded report
2. Create a Power BI group and publish reports there, all members of the group will be able to see the report in Salesforce
   • Link: How to create a group

Sharing a report from a group requires we modify the JavaScript in the embedConfiguration element in the Visualforce page.

We need to remove the id element and modify the embedUrl so it includes the id of the group we are trying to access and the id of report.

The group and report id can be found in the address bar of your browser.

In the above image: msit.powerbi.com/groups/GROUPID/reports/REPORTID/ReportSection

With the two identifiers, the embedUrl in the embedConfiguration element becomes:

embedUrl: ‘https://app.powerbi.com/reportEmbed?reportId=REPORTID&groupId=GROUPID’

Once modified it will look like this:

Conclusion

Once all the components are uploaded to Salesforce and configured correctly, navigate to the report page. You should be asked to log in, and once logged in, you should see the report you chose embedded within the Visualforce page.

With the embedding in place, the report can be used alongside the data in Salesforce, without having to change the context every time we want to visualize something. This also has the added benefit of bringing additional data sources straight into your Salesforce workspace, allowing you to have all data in one place!

For any queries or feedback, email us at: PBISolnTemplates@microsoft.com