Skip to main content

Power BI

BI Endpoints can be consumed directly in Power BI. The quickest start is the Power BI template: a sample finance data model on the G/L entries and chart of accounts that you can publish as-is or extend.

Use the template​

  1. In Business Central, open BI Endpoints, expand the factbox pane, and download the Power BI template under Support Material. Unzip it.

  2. Open the template file in Power BI Desktop and give it a minute to load.

  3. Fill in the parameters:

    ParameterValue
    TenantIdThe Azure tenant ID
    ClientIdThe app registration's client ID
    ClientSecretThe app registration's client secret
    BCEnvironmentThe Business Central environment, for example Production
    BCCompanyIdThe Business Central company ID — see Find your company
    FromDateG/L entry filter: posting date from
    ToDateG/L entry filter: posting date to

  4. Click Load. When Power BI asks how to access the web content, choose Anonymous: the parameters are used to authenticate.

  5. At Privacy levels, tick Ignore Privacy Levels checks for testing, or set both sources to Organizational.

Your data now loads. Publish the data model to the Power BI service, or add visuals to the report directly. The model contains:

  • GL Entries with the measures Balance Actual, Balance Change Actual, Finance Actual and PL Actual.
  • Chart Of Accounts with a Category - Sub - Account hierarchy.
  • Date with a Year - Month - Date hierarchy.
  • GL Entries details with document, entry, source and dimension fields.

The app registration needs the permissions described in App registration and permissions.

Building your own model​

  • Point Power Query at an endpoint URL; see Construction of the URL. Every response has the rows in value and, when there are more, an @odata.nextLink to follow.
  • Use $filter to keep refreshes small, for example a posting-date window on glEntries. See Querying the endpoints.
  • Join tables on the relationships listed for each endpoint — on the endpoint's page in Business Central, or through the relationships metadata. See Metadata discovery.
  • If refreshes time out on large tables, send the header Prefer: odata.maxpagesize=5000 to get smaller pages.

:::tip Loading many tables on a schedule?

Cloud Connector loads BI Endpoints incrementally into SQL Server or Fabric, deletions included, and refreshes your semantic model when the load is done.

:::