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.
There are three main prerequisites for embedding a report into Salesforce:
- A Power BI or Power BI pro subscription that contains the report to embed
- A Salesforce instance
- 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.
To embed a report into Salesforce, we follow this basic authentication flow:
- Use OAuth 2.0 to authenticate against the Power BI service through Azure AD.
- The library returns the report and renders it in an iframe.
- 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)
- 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:
- From your Salesforce home screen go to Setup (right hand upper corner).
- On the left-hand side of the screen, expand the Develop tab.
- Once expanded, there will be a Custom Settings option.
- Click New, give the setting the name “OAuthApp_pbi” (the script uses this name) and save it.
Ensure the Settings Type is ‘List’.
- Now click the New button under Custom Fields and add the following fields.
Access Token URL
Token Expires On
- Once the fields have been added, go back to the custom setting definition. You should see a screen like this:
- 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.
Access Token URL
The OAuth 2.0 URL used to retrieve the access token.
For Azure AD, it’s usually:
The OAuth 2.0 URL of the authority that will provide the first token.
For Azure AD, it’s usually:
The Azure AD application Client Id.
The Azure AD application Client secret.
The OAuth 2.0 URL of the resource we are trying to access.
In this case, it is:
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
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:
- From the homepage, navigate to Setup.
- On the left-hand side, expand the Develop tab.
- Click on Visualforce pages.
- Click New and copy and paste the code from the ReportPage file.
- 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.
- The user needs to have a Power BI account
- 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.
- 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
- 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
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:
Once modified it will look like this:
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