Introduction
The CRM can store a huge amount of data. A Business Intelligence tool such as Power BI allows to retrieve meaningful information from this data and helps companies to improve their business.
In consequence, more and more people show their interest about the association of Power BI with Dynamics 365. So, this Chronicle will expose different ways to embed Power BI in Dynamics 365.
Note about Security roles:
The Security roles in Power BI are different from the Security roles in Dynamics 365.
So, when embedding a Power BI report in Dynamics 365, the access to data must be carefully managed and implemented.
Integration in Dynamics 365 Dashboards
Presentation
Power BI dashboards can be used out-of-box in the Dashboards of Dynamics 365. To do so, one setting must be activated first: go to System Settings -> Report -> Allow Power BI visualization embedding.
Then, in the Dashboard page, click new Power BI dashboard and select the Dashboard you want to embed. To make it visible to other users of the CRM, it must be shared in Power BI and in the CRM. Sharing it in Power BI requires that the users have a Power BI Pro License (10 CHF/month).
The screenshot below shows an example of this:
Pros & Cons
Pros:
- This method is quick and simple to use
Cons:
- It is not supported to add Power BI visualizations to system dashboards of Dynamics 365. So, this method is only possible for personal dashboards.
- The user needs a Power BI account with a Pro License.
- To share a personal dashboard that contains Power BI visualizations, it must be shared in both Dynamics 365 and Power BI, and the user or group must have the same credentials and appropriate level of access in both services.
- The Power BI Dashboard can’t be displayed in the form of an entity
The next parts will present methods that solve some drawbacks mentioned above.
Publish Power BI report to Web
Presentation
The method presented here allows to embed a Power BI report in any iframe. To do this, go to the report to embed in Power BI service. Then, click File -> Publish to Web. An URL is provided. This URL can be used in an iframe, anywhere.
Pros & Cons
Pros:
- This method is also simple to use. It allows to embed a Power BI report anywhere in a few minutes. For instance, the report can be embedded in the Form of an entity. The data in the report can also be filtered on the context of the record.
- This method can be used with a single free version of Power BI service for all users.
Cons:
- When clicking on “Publish to Web”, the report is published on the Web. Anyone on the Internet can view the report. This requires no authentication. So, this method is only conceivable if the data can be made public.
Secure embedding
Presentation
Having data made publicly available may be an issue for most companies. To keep data secure, another method exists. From the report to embed in Power BI service, click File -> Securely embed this report.
Just as in the previous method, an URL is provided to embed the report in an iframe. This time, the user needs to authenticate with a Power BI account to access to the report.
Signing in - SSO
When the report is embedded in Dynamics 365 with this method, the user is asked to log in to access to the report:
Then, the user must log in twice: once for Dynamics 365 and once for Power BI. This is probably not appropriate for most companies.
So, this Chronicle will expose how a Single-Sign-On (SSO) can be put in place.
There are 2 methods to manage authentication and SSO. Here is a short presentation:
- “User owns data”: each user of Dynamics 365 owns a Power BI account, and has access to the report that is embedded
- “App owns data”: The users of Dynamics 365 don’t need a Power BI account. Instead, only one account is used for the integration. Dynamics 365 retrieves the credentials of this account to get the Power BI report. The account can be a “Master Account” or a “Service Principal”. The Master Account is like any other Power BI account. It should be used as a technical account and not be dependent on a user. The Service Principal requires no Power BI account. It is only registered in Azure, with no cost.
User owns data
Presentation
For this method, each user of Dynamics 365 must have a Power BI account, associated to a Power BI Premium license.
To perform the SSO, the XML file of the solution must be modified manually. A <Section> tag must be added in the Form, with information inside to access the report like this:
<section id="{d411658c-7450-e1e3-bc80-07021a04bcc2}" locklevel="0" showlabel="true" IsUserDefined="0" name="tab_4_section_1" labelwidth="115" columns="1" layout="varwidth" showbar="false">
<labels>
<label languagecode="1033" description="Filtered Power BI embedding demo"/>
</labels>
<rows>
<row>
<cell id="{7d18b61c-c588-136c-aee7-03e5e74a09a1}" showlabel="true" rowspan="20" colspan="1" auto="false">
<labels>
<label languagecode="1033" description="Accounts (Parent Account)"/>
</labels>
<control id="filteredreport" classid="{8C54228C-1B25-4909-A12A-F2B968BB0D62}">
<parameters>
<PowerBIGroupId>00000000-0000-0000-0000-000000000000</PowerBIGroupId>
<PowerBIReportId>544c4162-6773-4944-900c-abfd075f6081</PowerBIReportId>
<TileUrl>https://xyz.powerbi.com/reportEmbed?reportId=544c4162-6773-4944-900c-abfd075f6081</TileUrl>
<PowerBIFilter>{"Filter": "[{\"$schema\":\"basic\",\"target\":{\"table\":\"My Active Accounts\",\"column\":\"Account Name\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]", "Alias": {"$a": "name"}}</PowerBIFilter>
</parameters>
</control>
</cell>
</row>
<row/>
</rows>
</section>
This contains the ID of the group and of the report, and the URL of the embedded report. Other information can be added, such as filters to apply to the embedded report.
By construction, Power BI will automatically try to authenticate the user with his Dynamics 365 credentials. If a Power BI account with matching credentials doesn’t exist, a sign-in prompt will be displayed. The user will be able to sign in with other credentials, but then the SSO won’t work.
Note that the “FormXml Manager” of the XrmToolBox can be used to simplify the editing of the XML file.
Pros & Cons
Pros:
- The embedding is secured
- The implementation of the embedding doesn’t require to manage authentication (contrary to the next method, "App owns Data")
- It is possible to filter the report on the context of the record
- Only the data filtered is sent to the Client
Cons:
- This embedding is only available in the Unified User Interface
- It requires a Power BI Premium license
- The IDs of the Power BI Workspace and report are hardcoded in the XML. So, if a different Power BI is needed in each CRM environment (dev vs prod for instance), it can be tricky to handle
App owns data
Presentation
With this method, the users of the CRM don’t need to have any Power BI account. This method is largely encouraged for Independent Software Vendor (ISV) who want to embed a Power BI report into their software. For a provider of CRM such as Elca, this may also be appropriate in many cases.
“App owns data” for Power BI - Dynamics 365
Today, there seems to be nothing implemented in Dynamics 365 to help the integration of Power BI with the “App owns data” method.
There doesn’t seem to be plans to implement this method out of box in a near future.
Architecture
1. First tested architecture: Use of Azure function
Hereafter is detailed an architecture to securely embed the Power BI report in a “App owns data” way.
When the user of Dynamics 365 opens the form of an Entity that contains an embedded Power BI report:
- A JavaScript function is executed when the page is loaded. This function makes an HTTP request to an Azure function.
- The Azure function is registered in the Azure Active Directory of the Power BI resource. So, it can interact with the AAD using an authentication ID. When it receives the HTTP request, the Azure function identifies in the AAD using this ID. Then, it provides the credentials of the Master Account of Power BI to the AAD.
The Azure function asks the AAD to be provided a token to interact with the Power BI account. - The AAD provides this token to the Azure function.
- The Azure function connects to the Power BI account using this token.
- The Azure function then asks Power BI to have access to a specific report.
- Power BI returns a new token to the Azure function. When possessing this token, it is possible to interact with the Power BI report.
- The Azure function returns this token in the HTTP request made by the JavaScript function.
- The JavaScript function can connect to the Power BI report. It makes a request to embed the report in a Web resource contained in the Entity form. This Web Resource simply contains a <div> element.
- The Power BI report is sent to Dynamics 365 and is embedded in the <div> element
2. Second tested Architecture
A better architecture is to code all the logic in an Action of Dynamics 365 and to call the action from the JavaScript. This simplifies the architecture (no use of Azure function). This also allows to include all the custom code in a solution of Dynamics 365. Here are details on it:
- When opening the Form in Dynamics 365, a JavaScript code is run. It makes an HTTP request to an Action of Dynamics 365. The Action is a code that runs on the Server of Dynamics 365.
- The Action retrieves the credentials of the Power BI account in a Configuration table of Dynamics 365. Using the credentials, it makes an HTTP request to the Azure Active Directory to ask for a Token to authenticate to Power BI.
- The Azure Active Directory checks that the credentials provided are exact. If yes, it returns the Authentication Token.
- The Action of Dynamics 365 uses this Authentication Token to authenticate in Power BI. It makes an HTTP request to Power BI to ask for another Token. This second Token can be used to Authenticate in Power BI but only with restricted permissions.
- This Permission Token is returned to the Action of Dynamics 365.
- The Permission Token is returned to the JavaScript code running on the Browser.
- The Browser uses the Permission Token to authenticate in Power BI (with restricted permissions).
- Power BI returns the report to the Browser.
- Finally the JavaScript code embeds this report as an iFrame in the opened Form.
Permissions for the integration
This part details the permissions that need to be configured to make this “App owns data” integration work.
1. Register an Application with minimum permissions
The first step to set the permissions is to register an application in the the Azure Active Directory to be able to access to the Power BI API.
The application needs the following permissions:
Microsoft Graph (API for the AAD):
- User.Read (granted by default when registering an App from the Portal)
- Power BI Service:
- Workspace.Read.All
- Report.Read.All
- Dataset.Read.All
2. Use the Application with a Master Account
By default, the application can be accessed using the credentials of any account in the AAD tenant.
In Power BI service, permissions must be given to this account to access the report.
3. Use the Application as Service Principal
To use a Service Principal instead of a Master Account, a Client Secret must be created for the Application. It allows to authenticate as the Application, without the need for a user account.
With the Service Principal, the Power BI Integration doesn’t depend on the account of a user.
The duration of the Client Secret duration can be chosen. It is possible to set it to “Never expires”.
4. Allow Service Principal to use Power BI APIs
By default, Service Principals can’t use the Power BI APIs. In Power BI Service, the configuration “Allow service principal to use Power BI APIs” must be enabled:
5. Restrict Access to Service Principals
The permission to use the Power BI APIs can be enabled only for some Security Group, which contains the Service Principal.
That way, only the Service Principal of this Application can use the Power BI API.
6. Give access to the Service Principal to the Power BI Workspace
Finally, an access must be given to the Service Principal in the Power BI Workspace. The Member Permission must be given (Right to share an item is needed for the integration).
Pros & Cons
Pros:
- Only one account (“Master Account” or “Service Principal”) is required for all the users of Dynamics 365
- The embedding is secured
- Users have no concern about Power BI (no need to have an access to Power BI and the reports)
- Only filtered data can be sent to the Client, no more. This is a must-have for performance of reports with large datasets.
- The developer of the integration has the control on the code. So he can for instance use the API of Power BI to add more functionalities in the embedding of Power BI in the CRM (not possible with "User owns Data" integration)
- Cons:
- Requires more time and development to implement than other methods
- Requires a Power BI Embedded license
Pricing
To securely embed a Power BI report, a Managed Capacity in Power BI is needed. Managed Capacities are handled in a resource of Azure called “Power BI Embedded”. Then, Managed Capacities can be associated to a Power BI Workspace.
The integration “User owns data” requires the license Power BI Premium (4,915.60CHF: “Monthly price per dedicated cloud compute and storage resource with annual subscription”). A Managed Capacity is provided with this license.
The integration “App owns data” requires buying a Managed Capacity in Power BI Embedded in Azure. There are 7 seven types of Managed Capacity, depending on the computation needs. The prices go from 700CHF/month to 23,000CHF/month.
Filter report on record context
When a Power BI report is embedded in a Form, it is possible to filter it on the context of the record.
Below is an example of a Power BI report displayed in the entity account. The report displays the related opportunities with a few measures, and it shows the sum of the actual values of these related opportunities.
Here is another screenshot of this Power BI report example. This time, the report is displayed for another account.
For “Publish to web” and “App owns data” integration, the report can directly be modified in client side, using JavaScript. This allows to use all the API functions of Power BI. The filters are used using this API.
Once the report is embedded in Dynamics 365, it is possible to fully interact with the report using the JavaScript SDK of Power BI.
The SDK can be used for instance to filter the Power BI report with data only related the record opened in Dynamics 365.
Technically: the JavaScript resource mentioned above retrieves the ID of the opened record using: window.parent.Xrm.Page.data.entity.getId(). Then, this ID is used as input in the filter of the JavaScript SDK of Power BI.
Security Roles to filter data server-side
Filters are simple to use but they have a significant drawback in the application seen before. Indeed, all the data in the report is sent to the client and only then the filter is applied to keep just the data related to the opened record.
This can be a security issue. And for companies with large datasets, the loading of the report can be very long. The useless data should be removed not from the report in the Client side but right from the Server side.
This can be done using Security Roles in the case “App owns data”. In Client side, the JavaScript sends the ID of the record that is opened. In Server side (in Power BI), security rules are defined such that only the data related to the record with this ID can be seen.
Then, only data related to the opened record is loaded in the Client Side.
Conclusion
There are several ways to embed Power BI reports or dashboards in Dynamics 365.
- Dashboards can easily be used within Dynamics 365 Dashboards. It requires a Power BI Pro license per user
- Reports can be embedded in forms of Dynamics 365 and filtered on the record of the form
- With “Publish to Web”, the embedding is free and very easy to set up. But data is made public
- With a license Power BI Premium, it is possible out-of-box to embed the report securely. This is not yet very user-friendly as it requires to modify the XML of a solution
- With a license Power BI Embedded, it is possible to embed the report securely, but this is not an out-of-box functionality. It is a quite complex integration to put in place. With a Service Principal, the integration can be made independently of any Power BI account.
Add new comment