Microsoft Power BI can provide a flexible alternative to building reports for Business Central. It can help you create visualizations, drill down into the data, and even merge data from different entities in Business Central. In this article, we’ll explore how to integrate Power BI reports with Business Central, focusing on connecting Power BI Desktop with Business Central data, and building reports that can be incorporated into the Business Central interface.
Business Central Data Sources
Business Central comes with a set of built-in application programming interfaces (APIs), which you can use to get data for common business entities like customer lists, sales orders, dimensions, and your chart of accounts. However, if your reporting needs extend beyond the standard setup, such as incorporating add-ons or customizations, the built-in APIs may not be sufficient.
In such cases, Business Central offers two ways to expose data so it can be used in Power BI reports: custom (or advanced) APIs and Open Data Protocol (OData) web services. These methods allow for the presentation of specific information through pages or queries, like SQL queries in on-premise systems.
Starting with the first option, Business Central developers can create custom API pages and queries and package them into apps. Then, the apps can be installed on your tenant. Once installed, you can use them for Power BI reports.
With the second option, you can publish Business Central application objects—like codeunits, pages, and queries—as OData web services. In the cloud version of Business Central, many web services published by default. An easy way to find web services is to search for “web services” in the Business Central search bar. In the Web Services page, make sure the “Publish” field is selected for any objects you may want to use.
Web services can play a vital role in connecting Business Central with external systems. The list of web services can include the default ones designed to support Power BI. Notably, if your chart of accounts is included in the web services list, it’s likely due to the impact of add-ons or customizations on its appearance or functionality. Web services allow modifications to be exposed, while the standard API remains unaffected.
Lastly, Microsoft encourages using standard (or built-in) API pages instead of web services when possible. Standard API pages generally load data in Power BI reports faster than web services. However, since web services allow for modifications, they can provide greater flexibility and efficiency overall because you can get data from table fields that may not be defined in a page object.
Creating Power BI Reports
With a clear understanding of where the data resides and its structure, the focus can shift to creating reports by leveraging Power BI and Business Central. Power BI Desktop can serve as the primary tool for report creation, offering more convenience and efficiency compared to building reports in the online Power BI service.
One important thing to note before getting started: It’s crucial that the source data for a report be readily accessible. It also is preferable to perform any necessary data manipulation at the source whenever possible.
Basic Steps
- Sign up for a Power BI account
- Download Power BI Desktop
- Make sure the data is available/exposed (see above)
- Get the Business Central data source information (more below)
- Use the Business Central report theme (optional)
Report Creation Steps
- Open Power BI Desktop
- Get Data
- Edit Queries, if necessary
- Create visualizations
- Apply filters
Once your Business Central data is exposed to Power BI, the report-building process can begin. In Power BI Desktop, select Get Data. On the Get Data page, select Online Services.
According to Microsoft, in the Online Services pane, complete the appropriate step:
- To connect to Business Central online, select Dynamics 365 Business Central and then Connect.
- To connect to Business Central on-premises, select Dynamics 365 Business Central (on-premises) and then Connect.
If you haven’t logged into Business Central from Power BI Desktop before, you’ll be prompted to sign in.
- For Business Central online, select Sign in, choose the relevant account, and then select Connect.
- For Business Central on-premises, enter the OData URL for Business Central. When prompted, enter the username and password for the Business Central account. When done, select Connect.
Once connected, Power BI can access Business Central data. The Navigator window should appear and display available data sources for building reports. Select a folder to expand it and see the available data sources.
These data sources represent all the APIs and web services that are published in Business Central. The data sources are grouped by the Business Central environments and companies.
This step is followed by editing queries, creating visualizations, and applying filters to refine a report’s content.
Watch On-Demand
To illustrate the process, we examine an example report called “BC Report,” which will be displayed within Business Central. Watch here from 26:45–40:00 or catch it on YouTube.
With a better understanding of how to integrate Power BI reports in Business Central, you can effectively create and display critical business reports. With the information front and center in a pivotal business tool, your organization can gain visibility into key performance metrics.
If you would like more information about Power BI and Business Central, use the Contact Us form below to get in touch. The Business Technology Solutions team at Forvis Mazars provides analysis, design, implementation, upgrade, training, and support services for business technology applications. We are a Microsoft Dynamics Gold Partner and 2022–23 recipient of the Microsoft Inner Circle award.