Power BI provides a way to integrate or embed your reports or dashboards into your website or app. Microsoft Power BI is the primary Business Intelligence tool for Microsoft Dynamics 365 for Finance and Operations. Dynamics Finance and Operations apps now deliver rich, interactive reports seamlessly integrated into application workspaces. By using graphics and visuals supported by Power BI, workspaces can provide a highly-visual, yet interactive experiences for users.
Before we begin, First we have to understand the prerequisites.
- You must be a system administrator in the application. This option is available on the System administration menu.
- You must have a PowerBI.com account. You can create a trial account if you don’t have an account. (A Pro license isn’t required for this configuration step.)
- You must have at least one dashboard and one report in your Power BI account. Although the dashboard and report aren’t required for this configuration step, you might not be able to validate the configuration if you don’t have any content in your PowerBI.com account.
- You must be an administrator for your Microsoft Azure Active Directory (Azure AD) account. If you aren’t the administrator, an administrative user must perform this configuration step for you.
- The Azure AD domain that is configured must be the same domain that you used for your PowerBI.com account. For example, if you provisioned the application in the Contoso.com domain, you must have Power BI accounts in that domain, such as Tim@ContosoAX7.onmicrosoft.com.
- Power BI report can be deploy only by LCS Admin account otherwise It will give deploy error
- When embedding report within Dynamics 365 F&O then It should use Direct Query mode.
- You cannot use mix mode query when you are developing Power BI embedded report.
- We need Tier 2 environment to view report inside the workspace. Dev environment have few limitation to make this happen
Power BI configuration
The configuration is already covered in Microsoft docs. Here is the link and follow the process.
Power BI components
We need few components to understand how to create Power BI report.
Perspectives are a first-class citizen in the data access framework.
They can be consumed via X++ or C# code, and also in a model-driven way.
Power BI desktop
To create Power BI report using AxDW database. Power BI desktop is already available on Microsoft hosted Devbox(Continuous Service). Dynamics 365 F&O does not support latest Power BI update. It is always check the Power BI version compatible with Dynamics 365 F&O.
SQL Server Database
To connect with AxDW database and check the measurement definition/data.
An aggregate measurement is a model that contains a collection of measures together with their corresponding dimensions. Measures are aggregate numbers, such as Total Sales or Number of Orders
Dimensions are slicers, such as Product, Vendor, or Customer, that help you analyze the measure. For example, the measure of Total Sales isn’t useful unless it can be sliced by Product, Region, and Customer
Aggregate data entities
By using the model-driven approach, you can create data entities by directly referencing aggregate measurements and aggregate dimensions. These are known as aggregate data entities. Aggregate data entities are read-only data entities that are used for reporting purposes. To consume aggregate data when you build charts and other client controls, add the aggregate data to a form as a data source. You can also consume aggregate data entities programmatically in C# or X++ code.
In-memory, near-real-time aggregate measurements
Aggregate measurements are deployed to Microsoft SQL Server non-clustered column store indexes (NCCI). Therefore, they can take advantage of the in-memory computing (IMC) engine that is built into Microsoft SQL Server 2016 as Azure DB. Aggregate measurements that have the IMC engine as their destination are referred to as in-memory, near-real-time (IM-NRT) aggregate measurements. These aggregate measurements don’t require that a Microsoft SQL Server Analysis Services (SSAS) server be used. Because these models don’t involve data updates, the queries that are sent to them reflect the latest state of data in the operational database. That is why it’s referred to as near-real-time.
Power BI DirectQuery mode
you could create Power BI reports by using OData endpoints that are exposed via data entities (both aggregate data entities and detailed or regular data entities). Although this approach is still supported, Entity store also lets power users create Power BI DirectQuery reports.
As the preceding illustration shows, DirectQuery is a reporting mode that runs reports directly on Entity store. In this reporting mode, data isn’t staged in Power BI caches. This mode provides two immediate benefits:
- You can create Power BI reports over large data volumes.
- Reports don’t have to be updated by using Power BI. When Entity store is updated, reports reflect the latest data.
Additionally, data doesn’t leave your environment, because no data is cached in the Power BI service.
Advantages of Power BI Embedded
- Deliver Power BI workspaces and reports in the application. If you’re a power user or a business analyst, you can tweak ready-made reports or create new reports by using Power BI tools. As a developer, you can use the reports that your users create to provide rich navigation experiences in the product through workspaces. If you’re in the partner and ISV community, you can build rich workspaces that include Power BI experiences, and then release those workspaces as part of your solution.
- The Power BI Embedded service license is bundled with the application. If you’re an ISV or a systems integrator, you can package workspaces that are enabled for Power BI (and navigational experiences that those workspaces provide) as part of a Microsoft Dynamics Lifecycle Services (LCS) solution. Your customers get the same experience without having to have a PowerBI.com subscription. The workspaces just work with Finance and Operations applications.
- Enable drill-down into detailed pages from Power BI. The visuals are the starting point for action. Your users can drill down to business processes and pages to act immediately on issues that they uncover. The visuals let users filter data and uncover trends. Action pages reflect just the set of data that requires attention.
- Help secure access to Power BI reports by using menu items. As a developer, you can use familiar programming concepts that are available in Finance and Operations apps, because we have extended the same concepts to workspaces that are based on Power BI. You can create new workspaces or extend existing workspaces by adding an overview page that is driven by Power BI. Developers can associate menu items with Power BI reports and include them as links in workspaces. The role-based and task-based security in Finance and Operations apps can be used to help secure these menu items.
- Filter reports based on application context. You can build navigation experiences by passing one or more filters to Power BI reports. For example, depending on a user’s actions or context, you can filter the Power BI report to reflect data from one business unit or a specific product. The user doesn’t have to filter the data. You can define drill-through links to Finance and Operations pages, so that users can go directly to the transactional details pages.
Now we understand what things needed to create a Power BI report. In next article we will see how to create Power BI report.