Enabling Power BI Integration With Business Central

Note

Azure Active Directory is now Microsoft Entra ID. Learn more

This article describes how to get Business Central ready for integration with Power BI. Business Central online is already enabled for integration, although there's some information about licensing that you might want to read. For Business Central on-premises, you'll have set up your environment to connect to Power BI before users can work with it.

Power BI Licensing

With Business Central, users get a free Power BI licence that provides access to the most common features in Business Central and Power BI. You can also purchase a Power BI Pro licence that provides access to additional features. The following table provides an overview of the features available with each licence.

Power Licence View reports Create reports Share reports Refresh reports Business Central Apps
Power BI free a checkmark. another checkmark (limited) (limited)
Power BI Pro yet another checkmark. it's a checkmark again a checkmark (extensive) last checkmark

For more information, see Licensing the Power BI service for users in your organisation or Sign up for the Power BI service as an individual.

Expose data through API or OData web services

Business Central offers two ways to expose data that can be consumed by Power BI reports: API pages or queries, and Open Data Protocol (OData) web services.

API pages and queries

APPLIES TO: Business Central online only

Developers can define page objects and query objects that are of the type API. This way, they can expose data from database tables through a webhook-supported, OData v4-enabled, REST service. This type of data can't be displayed in the user interface, but is intended for building reliable integration services.

Business Central online comes available with a set of built-in APIs, which you can use to get data for the most common business entities, like customers, items, sales orders, and more. No extra work or setup is required to use these APIs as a data source for Power BI reports. For more information about these APIs, see Business Central API V2.0.

Business Central online also supports custom APIs. Application developers of Business Central solutions can create their own API pages and queries and package them into apps. You then install the apps on your tenant. Once installed, you use the API pages for your Power BI reports, like you'd do with the built-in APIs (v2.0). For more information about how to create an API by exposing pages or queries, see Developing a Custom API.

Important

Starting in February 2022, Power BI reports for Business Central online are sourced from a secondary, read-only database replica for performance reasons. As a consequence, AL developers should avoid designing API pages that make database modifications while the pages are opening or loading records. In particular, consider the code on the AL triggers: OnInit, OnOpenPage, OnFindRecord, OnNextRecord, OnAfterGetRecord, and OnAfterGetCurrRecord. These database modifications, in some cases, may cause performance problems and prevent the report from refreshing data. For more information, see Performance Articles For Developers in the Business Central development content.

In rare cases, the behaviour will cause an error when a user tries get data from the API for a report in Power BI Desktop. However, if database modifications are necessary in the custom API, Power BI Desktop users can force the behaviour. For more information, see Building Power BI Reports to Display Business Central Data.

OData web services

You can publish Business Central application objects, like codeunits, page, and queries, as OData web services. With Business Central online, there are many web services published by default. An easy way to find the web services is to search for web services in Business Central. In the Web Services page, make sure the Publish field is selected for the web services listed above. For more information about publishing web services, see Publish a Web Service.

To learn about what you can do to ensure the best performance of web services, as seen from the Business Central server (the endpoint) and from the consumer (the client), read Writing efficient Web Services.

Choosing whether to use API pages or OData web services

Whenever possible, you're encouraged to use API pages instead of OData web service. API pages are generally faster at loading data in Power BI reports than OData web services. Plus, they're more flexible because they let you get data from table fields that aren't defined in a page object.

Set up Business Central on-premises for Power BI integration

This section explains the requirements for a Business Central on-premises deployment to integrate with Power BI.

  1. Configure either NavUserPassword or Microsoft Entra ID as the authentication method for the deployment.

    Note

    Power BI integration doesn't support Windows authentication and is not supported on Windows Client.

  2. Enable OData web services and the ODataV4 endpoint.

    OData web service must be enabled on the Business Central Server, and OData port opened in firewall. For more information, see Configuring Business Central Server - OData Web Services.

    The local server must be accessible from the Internet.

  3. Give Business Central user accounts a web service access key.

    A web service access key is only needed to view Business Central data in Power BI. You can assign a web service access key to each user account. Or instead, create a specific account with a web service access key for use by all users. For more information, see Web Services Authentication.

  4. Create an application registration for Business Central in Microsoft Azure.

    To view Power BI reports embedded in Business Central pages, an application must be registered for Business Central in Microsoft Azure. The registered application needs permission to Power BI services. At a minimum, the app requires User.ReadWrite.All permission. For users to view reports from shared Power BI workspaces, the app requires Workspace.Read.All permission. For more information, see Registering Business Central On-Premises in Microsoft Entra ID for Integrating with Other Services.

    Note

    If your deployment uses NavUserPassword authentication, Business Central connects to the same Power BI service for all users. You'll specify this service account as part of registering the application. With Microsoft Entra authentication, Business Central connects to the Power BI service associated with the individual user accounts.

  5. Make the initial connection from Business Central to Power BI.

    Before end-users can use Power BI in Business Central, an Azure application administrator will have to give consent to the Power BI service.

    To make the initial connection, open Business Central, and run Get Started with Power BI from the Home page. This action will lead you through the consent process, and check your Power BI licence. When prompted sign in using an Microsoft Entra admin account. For more information, see Connect to Power BI - one time only.

Setting up Dataflows

Dataflows enable you to ingest, transform, and load data into a Power BI workspace and then use the data as the basis for your reports. These dataflows can in some cases experience transient errors while doing a scheduled refresh. The error message looks like this: DataSource.Error: OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

Using PowerAutomate, you can set up retries for this situation. For more information, see Automatically retry a dataflow on failure.

See also

Business Central and Power BI
Power BI Integration Component and Architecture Overview for Business Central
Power BI for consumers
The 'new look' of the Power BI service
Quickstart: Connect to data in Power BI Desktop
Power BI documentation
Business Intelligence
Getting Ready for Doing Business
Importing Business Data from Other Finance Systems
Setting Up Business Central
Use Business Central as a Power BI Data Source
Use Business Central as a Power Apps Data Source
Use Business Central in Power Automate

Find free e-learning modules for Business Central here