Business Central and Power BI: Ways to Integrate and When to Use Each

Almost every Business Central rollout ends with the same request: "Can we see this in Power BI?" The answer is yes, but there are four quite different ways to get there, and the one you pick decides how fast reports refresh, how much they cost to maintain and how far they scale. This article compares them and explains when each one fits.

The four approaches at a glance

1. The built-in Business Central connector

Power BI Desktop includes a Dynamics 365 Business Central connector. You sign in with your organizational account, pick an environment and company, and choose from the standard API entities. Microsoft also publishes ready-made Power BI apps for finance, sales and purchasing areas, which are a reasonable starting point for a proof of concept.

This route is the fastest way to a first dashboard. Its limits show up as data grows. Each entity arrives as a flat table, so relationships between, say, sales lines, headers, items and dimensions are rebuilt in Power Query and the data model. Refreshes pull every row through the API, and Business Central paginates responses and can throttle heavy callers. For a company with a few hundred thousand ledger entries this is usually fine. With tens of millions of rows, it usually is not.

2. Custom API pages and API queries

When the standard entities do not have the fields you need, or when you want the server to do the joining and filtering, you publish your own. An API query object is particularly useful for reporting, because it can join tables and aggregate data inside Business Central, so Power BI receives a compact, report-shaped result instead of raw tables.

query 50140 "CE Sales Lines Report"
{
    QueryType = API;
    APIPublisher = 'centralera';
    APIGroup = 'reporting';
    APIVersion = 'v1.0';
    EntityName = 'salesLineReport';
    EntitySetName = 'salesLineReports';

    elements
    {
        dataitem(SalesInvoiceLine; "Sales Invoice Line")
        {
            column(postingDate; "Posting Date") { }
            column(customerNo; "Sell-to Customer No.") { }
            column(itemNo; "No.") { }
            column(shortcutDimension1Code; "Shortcut Dimension 1 Code") { }
            column(quantity; Quantity) { }
            column(amount; Amount) { }
        }
    }
}

Power BI can read this through the connector or directly from its OData URL. Two habits pay off. First, filter at the source, for example by posting date, so a refresh does not read ten years of history. Second, give the object a version in its route (v1.0 above) and treat it like a public contract: renaming a column breaks every report that uses it. Our article on API pages versus web services explains the underlying options in more detail.

3. Power BI inside Business Central

Business Central can show Power BI reports directly on role centres and selected list pages, so users see numbers where they already work. This is an excellent way to increase adoption, because nobody has to open another portal. A few practical points:

• Reports are shown to users under their own Power BI licence and permissions, so plan licensing and workspace access early.
• Design for small tiles. A report built as a full-page dashboard often reads poorly in a FactBox.
• A report should filter by the record the user is looking at (for example, a customer) when it is shown on a card page.

4. A staged data store

When you need long history, several source systems in one model, or data volumes the API handles poorly, copying Business Central data into a separate store is the right design. Microsoft provides an option to export tables to Azure Data Lake Storage, and there are open-source tools built around it. The data lake then feeds a lakehouse, warehouse or Power BI dataset that refreshes independently of Business Central.

The trade-off is operational: another system to secure, monitor and pay for, and a delay between an entry being posted and appearing in a report. Choose this route because the data volume or the analytics scope demands it, not because it sounds more professional. Check Microsoft's current documentation for the exact export options available in your Business Central version before committing to a design.

How to choose

1. Start with the question, not the tool. A CFO's month-end pack and an operations manager's live stock view need different refresh patterns.
2. Measure data volume. Count rows in the ledger and line tables you will need. That number often makes the decision.
3. Prefer server-side shaping. Move joins and filters into an API query where you can, and keep Power Query for light transformations.
4. Decide refresh frequency honestly. Many reports are perfectly good with a nightly refresh, and this keeps load off the ERP.
5. Plan for growth. A model that refreshes in two minutes today may take an hour in three years.

Data quality decides report quality

A dashboard is only as reliable as the data behind it. The most common reason a Power BI report disagrees with Business Central is not a technical fault. It is inconsistent dimensions, duplicate master data, or a definition (what counts as "revenue"?) that finance and sales interpret differently. Agree the definitions and clean the master data first. It is cheaper than debugging a mismatched number in front of a management team.

Security and permissions

Reports should not become a side door around Business Central's permission model. If a user must not see certain customers or cost figures in the ERP, make sure the same restriction applies in Power BI, using row-level security or a filtered source. Use a service account or a proper app registration for scheduled refresh, never a named employee's login.

Where to go next

If you are planning reporting on Business Central, our Business Central reporting and Power BI services cover data modelling, API queries and dashboard design. If you want your team to build these skills themselves, see our Power BI training program.