Business Central and Power BI: Turning ERP Data Into Insight

Almost every Business Central customer arrives at the same realisation a few months after go-live. The ERP is running, the postings are clean, the transactions are flowing, and yet the questions that matter to the business are still being answered in spreadsheets. Someone exports a trial balance, someone else pivots a sales list, a third person stitches inventory figures together by hand every Monday morning. Business Central is doing its job as a system of record beautifully, but the system of record is not the same thing as an analytics layer, and the gap between the two is exactly where Power BI belongs. This guide is about closing that gap properly, from someone who has done it across real finance and operations teams rather than in a demo environment.

The message up front: connecting Power BI to Business Central is the easy part, and Microsoft has made it genuinely easy. The hard part, and the part that decides whether your analytics get used or abandoned, is the modelling, the governance and the discipline behind the connection. A trusted analytics layer is a design achievement, not a connector you switch on.

1. Built-in reporting versus Power BI: where each belongs

Business Central ships with a substantial amount of reporting on its own, and the first honest thing to say is that a lot of organisations reach for Power BI before they have exhausted what is already in front of them. Understanding where the built-in reporting ends is the only way to know where Power BI actually earns its licence cost.

Out of the box, Business Central gives you list pages with filtering, sorting and personalisation, account schedules and financial reporting for structured statements, the analysis views and analysis mode that let a user pivot a page without leaving the product, and a set of processing and document reports built on the underlying report objects. For a finance user who wants a balance sheet grouped by dimension, or an operations lead who wants to filter open sales orders by location and sort by due date, none of that needs Power BI at all. The system was designed so that day-to-day operational questions get answered inside the transactional screens where the person already works.

Power BI earns its place when the question outgrows a single page. There are a few clear signals. First, when you need to blend Business Central data with something else, a budget from a spreadsheet, actuals from a second company, web analytics, payroll, or data from another system entirely, the built-in reporting has no natural home for that and Power BI does. Second, when you need visual, interactive analysis that a static report cannot give: drill-down, cross-filtering, trends over years rather than a period snapshot. Third, when you need one curated version of a metric that many people consume identically, rather than each person building their own filtered view. Fourth, when the audience is executive or cross-functional and wants a dashboard rather than a ledger.

The practitioner's framing I use with clients: keep operational, row-level, action-oriented reporting inside Business Central where it belongs, and use Power BI for analytical, aggregated, cross-source and executive reporting. The two are complementary, not competing. Teams that try to rebuild every operational list in Power BI end up with a slow, expensive shadow of the ERP. Teams that try to force multi-year cross-company analysis into account schedules end up fighting the tool. Put each job where it fits. For the reporting foundations inside the ERP itself, the Business Central financial reporting pillar covers account schedules and financial reports in depth.

2. How Power BI connects to Business Central (the connector, APIs, and data feeds)

There is more than one way to move data from Business Central into Power BI, and choosing the right one is the first real decision in any implementation. They are not interchangeable, and picking the wrong path is a common source of performance pain later.

The most direct route is the dedicated Business Central connector in Power BI Desktop. When you get data and choose Business Central, Power BI authenticates against your environment and presents the standard API entities and any published web services as tables you can select and load. This is the path Microsoft steers most users toward, because it handles authentication, understands the online environment, and exposes a curated set of entities rather than raw table internals. For most finance and sales reporting, the connector against the standard APIs is the right starting point.

Underneath the connector sit the APIs. Business Central exposes a set of standard, versioned REST APIs covering common entities such as customers, vendors, items, sales and purchase documents, general ledger entries and dimensions. Beyond the standard set, a developer can define custom API pages or expose specific data as web services (OData and SOAP), which then become available to Power BI as additional feeds. This matters because the standard APIs are deliberately curated, and when you need a field or a combination the standard API does not surface, a custom API page or an OData web service is how you extend the reach without hacking around the platform. The Business Central APIs and integrations pillar goes deep on how those endpoints are structured and secured.

For heavier analytical needs there is a data-warehouse-style path. Rather than querying the live environment repeatedly, you can land Business Central data into an external store on a schedule and point Power BI at that store. In the Microsoft world this often means pushing data into a lakehouse or warehouse and letting Power BI read from there. This decouples your reporting load from the operational environment, which becomes important at volume, but it also adds a data-engineering layer to build and maintain. It is the right answer for large datasets and enterprise reporting, and overkill for a small company that just wants a sales dashboard.

The decision I walk clients through: start with the connector against standard APIs, extend with custom API pages or OData web services when a specific data gap appears, and only graduate to a staged data-warehouse pattern when data volume, refresh duration or the need to combine many sources makes direct querying impractical. Reaching for the warehouse pattern on day one, before you know your volumes, is a common way to over-engineer a problem you do not yet have.

3. The out-of-the-box Power BI reports and report packs

One of the genuinely helpful things Microsoft did was ship Power BI content with Business Central rather than leaving every customer to start from an empty canvas. It is worth knowing what you already have before you build anything, because a surprising number of teams rebuild reports that were sitting in the product all along.

Business Central includes embedded Power BI report parts that appear directly on role centres and list pages. When Power BI integration is enabled in the environment, a user with the right access sees relevant Power BI visuals inside Business Central itself, on the finance role centre, on customer and vendor cards, on item pages, contextual to what they are looking at. These are not something you build; they come as part of the finance and other application content and can be enabled and assigned per page.

Beyond the embedded parts, Microsoft provides Power BI report packs and apps aligned to the standard application, covering the common finance and sales analytics that most customers want first. These give you a working starting model connected to the standard entities, with report pages you can use as delivered or, more usefully, treat as a reference for how the entities relate and how the measures are built. Even when I do not deploy them as-is, I open them to see how Microsoft modelled the relationships, because it is a well-built example of the standard data.

The honest use of the out-of-the-box content is as a fast start and a teaching tool, not as a finished deliverable. The delivered reports are generic by design, built for the standard chart of accounts and standard dimensions, so they rarely match a real customer's specific structure without adjustment. My advice is to enable the embedded parts for the quick wins, deploy a report pack to a test workspace to learn the model, and then build your own curated model that reflects your actual dimensions, companies and business logic. Use the gift, do not mistake it for the destination.

4. Building your own Business Central data model in Power BI

This is where good analytics is won or lost, and it is the part demos skip. Connecting Power BI to Business Central takes minutes. Building a data model that is fast, correct and understandable takes real design thought, and it is the difference between a report that people trust and one they quietly stop opening.

The foundational principle is to build a proper star schema rather than dragging in wide, flat entities and hoping. In practice that means shaping your Business Central data into fact tables (the measurable events: general ledger entries, sales lines, item ledger entries, value entries) surrounded by dimension tables (date, customer, vendor, item, and, critically in Business Central, the analysis dimensions). The general ledger entry table and its associated entries are your primary finance fact; item and value entries are your inventory and cost facts; sales and purchase documents and their lines are your commercial facts. Model those cleanly and almost every question becomes a simple measure.

Business Central dimensions deserve special attention because they are the native mechanism the ERP uses to slice data, and they map naturally onto Power BI dimension tables. If your Business Central setup uses dimensions well, department, project, cost centre, region, then those dimensions should become first-class fields in your Power BI model, letting a viewer slice revenue or cost by exactly the categories the finance team already thinks in. If your dimension strategy in the ERP is weak, your analytics will inherit that weakness, which is one more reason to get dimensions right at source. The Business Central dimensions pillar explains how to design that dimension structure so it serves both the ledger and the analytics layer.

A dedicated date table is non-negotiable. Business Central posts everything with dates, and time intelligence, year on year, month to date, rolling twelve months, only works cleanly in Power BI when you have a proper marked date dimension rather than relying on the date columns embedded in the fact tables. This is a small piece of modelling that unlocks a large fraction of the analytical questions a business asks.

On the calculation side, the discipline is to build a small set of well-named, well-documented measures in DAX rather than letting every report author invent their own. Revenue, gross margin, days sales outstanding, inventory turns, defined once, named clearly, and reused everywhere. This is the single practice that most separates a trustworthy model from a confusing one, because it means the number called revenue means the same thing on every page, in every report, for every consumer.

The honest limitation: Power BI will happily let you build a fast, wrong model. You can load millions of ledger entries, join them clumsily, and produce a dashboard that renders and looks convincing while quietly double-counting a reversal or missing a posting group. Nothing in the tool checks your logic against the ERP's accounting. That reconciliation, proving the Power BI number ties back to the Business Central report it claims to represent, is manual, unglamorous, and the one step teams under pressure skip. Skip it and you ship confident numbers that are wrong, which is worse than no dashboard at all.

5. Embedding Power BI inside Business Central pages

Analytics that lives in a separate application gets consulted less than analytics that appears where people already work, and Business Central was built with that in mind. The embedding capability lets your curated Power BI reports appear inside the ERP rather than only in the Power BI service, and using it well noticeably lifts adoption.

Business Central supports Power BI report parts on role centres and pages. Once the environment is connected to Power BI and a report is published to a workspace the user can access, that report can be surfaced as a part on the relevant role centre, so a finance manager opening Business Central sees the finance dashboard immediately, in context, without switching tools. Reports can also be made contextual, filtering to the specific record the user is viewing, so opening a customer card can show that customer's Power BI analytics rather than the whole company's.

The design thinking here is about closing the distance between insight and action. A sales manager who sees a slow-moving customer highlighted on their role centre is one click from the customer record and the action it implies. That proximity is exactly what a standalone Power BI workspace loses. When I plan a Business Central and Power BI deployment, I treat the embedded experience as the primary consumption surface for operational users and the Power BI service as the surface for analysts and executives who want to explore. Different audiences, different surfaces, same underlying model.

A word of realism: embedding depends on licensing and access being right on both sides. The user needs appropriate Power BI access to the underlying report as well as their Business Central access, and the report has to be published to a workspace they can reach. Getting a report to appear inside Business Central is straightforward technically; getting it to appear for every user who should see it is a licensing and workspace-permissions exercise that is worth planning up front rather than discovering per user. For how these pieces fit together across the wider Microsoft stack, the Business Central in the Microsoft ecosystem pillar maps the surrounding services.

6. Refresh, performance and data volume considerations

Every Power BI on Business Central project that goes wrong at scale goes wrong here, and it is almost always because refresh and volume were treated as an afterthought rather than a design input. The connection worked on day one with a few thousand rows, then twelve months of postings later the refresh takes an hour and someone is asking why the dashboard is stale.

The first decision is import versus DirectQuery. Import mode pulls the data into Power BI's in-memory engine on a schedule; it is fast to query and supports the full modelling and DAX experience, but the data is only as current as the last refresh, and the dataset has to fit and refresh within your capacity limits. DirectQuery leaves the data in the source and queries it live; it keeps data current and avoids large in-memory datasets, but every visual interaction hits the source, which against a live Business Central environment can be slow and adds load exactly where you do not want it. For most Business Central analytics, scheduled import is the right default, because financial and operational analysis rarely needs to-the-second freshness and benefits from the speed and richness of import mode.

Scheduled refresh is where the volume question bites. A refresh that reloads every general ledger entry from the beginning of time, every night, is wasteful and eventually slow. The mature pattern is incremental refresh: partition the fact tables by date so that only recent periods reload while historical partitions stay put. This keeps refresh times bounded as history accumulates, and it is the single most important performance technique for a Business Central model that will run for years. Design it in early, because retrofitting incremental refresh onto a naive model is more painful than building it correctly from the start.

Volume also drives the architecture decision from the connector section. Direct connector queries against the standard APIs are fine at small to moderate volume. As the ledger grows into the millions of rows and refresh windows stretch, the case for staging data into a warehouse or lakehouse and reporting from there gets stronger, because you can shape and index the data for analytics rather than repeatedly extracting it through the operational API. The trigger to move is measured, not guessed: when refresh duration or query performance stops meeting the business need, that is the signal, not a round number of rows.

The practitioner's habit worth adopting: only bring in the columns and the history you actually report on. Teams routinely load every field of every entity because it is easy, then wonder why the model is bloated and slow. A lean model, with only the facts, dimensions, columns and date range that answer real questions, refreshes faster, queries faster and is easier to reason about. Discipline about what you leave out is as important as care about what you put in.

7. Governance: one version of the truth, security and row-level access

The technical connection determines whether your reports work. Governance determines whether people believe them, and belief is the entire point of an analytics layer. A dashboard nobody trusts is worse than no dashboard, because it invites argument instead of settling it.

The foundation of trust is one curated, certified model rather than a proliferation of personal reports each defining revenue slightly differently. This is where the shared dataset discipline matters. You build one governed model, connected to Business Central, with agreed measures and clean dimensions, publish it to a workspace, and have report authors build on top of that shared model instead of each pulling their own extract. When finance, sales and the board are all reading from the same certified dataset, the meeting is about what the numbers mean, not about whose numbers are right. Getting to that single version of the truth is a governance decision, not a technical one, and it is the highest-value thing a Power BI programme on Business Central can achieve.

Security has two layers that people often conflate. The first is access to the report and workspace: who can even open this content, governed by Power BI licensing and workspace roles. The second is row-level security within the report: given that a user can open it, which rows of data should they see. In a multi-company or multi-region Business Central deployment this second layer is essential, because a regional manager should see their region's figures and not the whole group's. Row-level security in Power BI lets you define roles that filter the model by dimension, company, region or business unit, so the same report shows each viewer only their slice.

The insight worth internalising: the Business Central permission model and the Power BI security model are separate systems that do not automatically agree. A user restricted from certain data inside Business Central is not, by that fact alone, restricted from seeing it once it lands in a Power BI dataset. If your reporting extracts everything and you rely on Power BI row-level security to re-impose the boundaries, you must design that row-level security deliberately to match the entitlements users have in the ERP. Assume the two align and you can quietly expose figures in a dashboard that the same person could never open in the ledger.

Governance also means ownership and lifecycle. Someone owns the certified model. Changes to shared measures go through that owner rather than being edited ad hoc. New reports are reviewed before they are certified and shared widely. This sounds heavy for a small team, and it can be scaled to fit, but the principle holds at every size: without a named owner and a light change process, a shared analytics model drifts into the same spreadsheet chaos it was meant to replace, just in a more expensive tool.

8. Common analytics use cases (finance, sales, inventory, operations)

It helps to ground all of this in the questions businesses actually bring to a Business Central and Power BI project, because the use cases are remarkably consistent across customers and they tell you what to model first.

• Finance: profit and loss and balance sheet trended over time and sliced by dimension, cash position and working-capital analysis, budget versus actual by department and period, and receivables and payables ageing. The general ledger entries plus dimensions plus a date table answer most of these, and this is usually the first model built because finance feels the spreadsheet pain most acutely. The Business Central financial management pillar covers the underlying ledger structure these reports sit on.
• Sales: revenue by customer, item, salesperson and region, trend and seasonality, top and bottom performers, margin analysis where cost data is available, and pipeline or order-book views from open documents. Cross-filtering, the ability to click a region and see its customers and their items, is exactly the interactive analysis Power BI does that a list page cannot.
• Inventory: stock valuation, inventory turns, slow-moving and dead-stock identification, and stock cover against demand. Item and value entries are the facts here, and this is where blending Business Central data with forecast or sales-velocity data adds real value beyond what the ERP screens show.
• Operations and purchasing: vendor spend analysis, purchase-order status and lead-time tracking, project or job profitability where jobs are used, and cross-company or cross-site consolidation. Consolidation in particular, one dashboard spanning multiple Business Central companies, is a classic case where Power BI does something the individual company views cannot.

The pattern across all four is the same: the value comes from aggregation, trending, cross-filtering and cross-source blending, precisely the things that outgrow a transactional page. When a client asks whether a particular report belongs in Power BI, I ask whether it needs any of those four. If it does, Power BI is the right home. If it is a filtered list a user acts on row by row, it belongs in Business Central.

9. Where teams go wrong with BC and Power BI (honest pitfalls)

Across implementations the failure patterns repeat, and every one of them is avoidable with foresight. Knowing them in advance is worth more than any connector tutorial.

• Skipping the model, going straight to visuals. Teams drag flat entities onto a canvas, build charts, and ship. It works until the questions get harder, then the flat model cannot answer them and the whole thing gets rebuilt. Model the star schema first; the visuals are the easy last mile.
• Never reconciling to Business Central. A Power BI revenue figure that does not tie to the Business Central financial report is a liability. The reconciliation step is boring and it is the one that earns trust. Do it, document it, and repeat it after every model change.
• Ignoring refresh and volume until it hurts. The model that flew with three months of data crawls with three years of it. Incremental refresh and a lean model are design decisions, not later fixes.
• Letting everyone build their own version. Without a certified shared model, revenue quietly means five different things across five reports, and the analytics layer becomes an argument generator instead of a decision tool.
• Assuming ERP permissions carry into Power BI. They do not. Row-level security has to be designed to match entitlements, or you expose data a person could never open in the ledger.
• Rebuilding operational lists in Power BI. Trying to recreate every Business Central list page as a Power BI report produces a slow, stale, expensive copy of something the ERP already does better live.
• Treating go-live as done. An analytics model is a product with a lifecycle. Dimensions change, the business reorganises, new questions arrive. A model nobody maintains decays into distrust within a year.

None of these is a technology failure. They are design, discipline and ownership failures, which is genuinely good news, because it means the fix is within the control of the team rather than dependent on a product roadmap. The organisations that get lasting value from Business Central and Power BI are not the ones with the fanciest visuals; they are the ones with a well-modelled, reconciled, governed, owned analytics layer that everyone reads from.

10. A practical path to a trusted analytics layer

If you are standing up Business Central and Power BI, the sequence matters far more than the tooling choices, and the order below is the one I would advise almost any organisation to follow.

• Step 1: exhaust the built-in reporting first. Before building anything in Power BI, confirm the question genuinely outgrows account schedules, list pages and analysis views. Half the requests that arrive as Power BI projects are answered inside Business Central for free.
• Step 2: get dimensions right at source. Your analytics inherit your dimension discipline. If department, cost centre and region are not posted cleanly in the ERP, fix that before you model, because Power BI cannot slice by a dimension that was never captured.
• Step 3: connect with the standard connector and standard APIs. Start simple. Use the Business Central connector against the standard entities, and extend with a custom API page or OData web service only where a real data gap appears.
• Step 4: build one governed star-schema model. Fact tables, dimension tables, a marked date table, and a small set of clearly named DAX measures. This is the asset everything else builds on, so invest here.
• Step 5: reconcile to the ledger. Prove the model's key numbers tie back to Business Central financial reports before anyone consumes them. Document the tie-out. Trust starts here.
• Step 6: design refresh and security deliberately. Scheduled import with incremental refresh for volume, and row-level security that matches ERP entitlements. Both are cheaper to build in than to retrofit.
• Step 7: surface it where people work. Embed the certified reports into Business Central role centres for operational users, and publish to the service for analysts and executives.
• Step 8: name an owner and a change process. The model is a product. Someone owns it, changes go through review, and it is maintained as the business evolves. Without this, everything above decays.

Notice that the first two steps involve no Power BI at all, and the highest-value steps, modelling, reconciliation and governance, are the ones with no visual payoff. That is the pattern in every analytics programme that lasts: the invisible work carries the trust, and the trust is what makes the visible work worth doing. Before you commit to a Business Central and Power BI build, it is worth confirming the ERP foundations are solid, which the is Business Central right for your organisation pillar works through.

Final thoughts

Business Central and Power BI are a genuinely strong pairing, and Microsoft has removed almost all of the friction from the connection itself. The connector works, the standard APIs are there, the report packs give you a running start, and embedding puts insight where people work. None of that is where projects succeed or fail. They succeed or fail on the modelling that turns raw entities into a clean star schema, on the reconciliation that ties the dashboard back to the ledger, on the refresh and security design that keeps it fast and safe at volume, and on the governance that makes one certified version of the truth the thing everyone reads from.

The teams that get this right treat the analytics layer as a designed product with an owner and a lifecycle, not as a connector they switched on. The teams that struggle reached for the visuals first and skipped the unglamorous foundations, then wondered why nobody trusted the numbers. Business Central holds the data, and Power BI can absolutely make it talk. Whether it says anything worth acting on depends entirely on the discipline you put behind the connection. Get the model, the reconciliation and the governance right, and you build an analytics layer people actually use. Skip them, and you build another set of charts that end up back in a spreadsheet.

Related reading: Business Central financial management, Business Central in the Microsoft ecosystem, Business Central financial reporting, Business Central dimensions, Business Central APIs and integrations, Is Business Central right for your organisation?.