‘Microsoft Dynamics 365 for Finance and Operations’ (“F&O” to us mere mortals) has some nice features to do reporting with PowerBI.
However, I’ve found the options, requirements and possible deployments extremely confusing.
This article tries to summarize the whole ‘ecosystem’ (always wanted to use that word) – and explain what is what.
Special thanks go to Tom Higginbotham at Microsoft for answering my relentless stream of questions and providing extremely useful guidance.
The info might be a bit haphazard, and I might revise it several times, but we have to start somewhere, right ?
Here we go:
There was a lot of confusion (on my part) to determine if a tier 1 (‘OneBox’) or tier 2 (Test/Sandbox) environment was required.
This document explains the tier-1 / tier-2 issue:
Some specifics from the doc:
The Power BI Embedded service is automatically deployed and configured for all cloud-hosted, multi-box deployments. Because the service relies on Microsoft Azure services, application analytical workspaces and reports are unavailable in one-box environments.
The Microsoft Dynamics 365 team is working on a solution that will enable analytical workspaces in one-box environments without requiring that customers host their own instance of the Power BI Embedded service RvB: they are referring to the setup with a separate Azure DB to host the Entity Store (AXDW)
Can I customize the Power BI embedded reports?
Yes. To customize the Power BI embedded reports, just install Power BI Desktop in a one-box environment, and follow the steps in Author and distribute Power BI reports with Entity store.
There are 2 ways to use PowerBI with F&O – you can mix and match these 2 methods:
PowerBI integration :
This is where you link F&O to your PowerBI subscription. This is setup through System Admin -> Setup -> PowerBI.com Integration.
This allows you to add reports/tiles/dashboards from PowerBI.com to Analytical workspaces.
When an existing report on PowerBI.com is “integrated” into D365F&O, it is done so from one of the 3 options on a Workspace:
These reports can contain both F&O data and other data sources (EntityStore / DataLake)
Note: DataSource on report will need to be manually changed when deployed to TEST or PROD environmentThe ‘Open Report Catalog’ and ‘Open Dashboard Catalog’ will add the respective items as Links to the Links list on the right-hand side of the Workspace. Example:
"Open Tile Catalog" will show the list of tiles from Dashboards on PowerBI.com and add them to the designated pane on the Workspace.
- Embedded PowerBI : https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/add-analytics-tab-workspaces
Snippet from the above article:
An analytical report (.pbix file) that was created by using Microsoft Power BI Desktop, and that has a data model that is sourced from the Entity store database.
This will require you (the developer) to add an ‘analytics’ tab to an existing (or new) workspace – much like the standard analytics tabs that contain PowerBI reports.
Since a prerequisite for PowerBI Embedded reports is to use the Entity Store, these can only contain F&O data.
A Tier 2 (or Higher) environment may not necessarily have the embedded Power BI components activated by default. To have it activated requires the submission of a Service Request in LCS as an “Other” request type. This is noted in the following: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/lifecycle-services/submit-request-dynamics-service-engineering-team?toc=/fin-and-ops/toc.json#create-service-requests
Scroll down in the article a bit and you will see as the last point under Other request:
Microsoft has rolled out the ability for a user to edit an embedded Power BI report within the F&O browser client. When the user has an embedded Power BI report displayed, there’s the following option:
When this is selected, the Power BI editing experience is turned on:
Again, this is only for the embedded Power BI reports. While these are PBIX resource files in Visual Studio, once deployed they are sourced on the Power BI Embedded Service (within the F&O subscription).
An embedded Power BI report can either be a Tab on a Workspace, such as this one in the Fixed Asset Management workspace:
Or, it can be an entire Workspace, such as CFO Overview/Financial Insights:
Or, it is a Form that is loaded via a menu item, such as Procurement and Sourcing > Inquiries and Reports > Purchase Performance > Purchase and Spend Analysis:
These would be the UI options should a new/custom Analytical Report be created, in a Tier 1 Sandbox.
- No additional license needed
- Can only contain D365FO data (Entity Store)
- Ability to drill down to D365FO screens
- Requires F&O development / code deployment (but can be edited from UI in future release)
PowerBI via link or ‘Report Catalog’
- Pulls from PowerBI service
- Requires additional PowerBi license
- Cannot drill down to F&O screens
- Can contain D365FO and external data
In order to develop the “PowerBi Embedded“ reports, you need a tier 1 environment (probably 1 per developer, like any other F&O development task)
in order to deploy and run/test the reports (by an end-user) – you will need a Tier 2 environment. (so you could use any tier 2 TEST/UAT environment for this).