Getting Power BI connected to Business Central feels like a win—most teams have a dashboard up before their coffee cools. Stakeholders are happy. But fast-forward a few months: now you’ve got nine PBIX files from four different people, three versions of gross margin, and a refresh that drags on for twenty minutes when you need it done in two. Sound familiar?
Here’s the real problem: connecting Power BI is easy. What you connect it to—and whether anyone’s actually governing the data—decides if you get analytics you can trust or a mess of reports nobody believes. The plumbing is solved. The architecture? Not so much. That gap is where the real cost to your business hides.
The Four Ways to Connect Business Central to Power BI
You’ve got four real options for getting Business Central data into Power BI. All of them work—but not all of them scale, and some open the door to serious sprawl.
| Connection Pathway | How Data is Sourced | Best For | Drawbacks |
|---|---|---|---|
| Native BC connector (OData / API pages) | Power BI Desktop’s built-in Business Central connector pulls tables and API pages over OData | A few reports against a single company; fast to stand up | Multi-company, large tables, and shared KPI definitions |
| Excel as intermediate | Data pulled into Excel, then imported into Power BI | One-off analysis on small, familiar datasets | Anything recurring; manual steps break when BC changes |
| Dataverse / Fabric / Data Lake | BC data synced to a central Microsoft store that Power BI reads | Large data volumes and enterprise-scale analytics | You still model and govern the semantic layer yourself |
| Dedicated governed semantic layer | BC data extracted and reshaped into a purpose-built BC reporting model; Power BI reports against it | Multi-entity teams that need one trusted set of numbers | Requires choosing or standing up that layer |
One option people ask about doesn’t exist: Power BI does not connect directly to the Business Central online SQL database. BC exposes data through OData web services and API pages, and direct SQL would bypass the service tier, its permission model, and FlowField logic — Microsoft doesn’t support it. Business Central online also routes new Power BI reports to a read-only replica by default, so analytics queries don’t fight transactional work for the same resources.
Almost everyone starts with the native connector. For a few reports in one company, it’s the right move. Microsoft also ships prebuilt Power BI apps for Business Central—APIs, semantic models, and reports covering core functional areas—so a native start doesn’t always mean a blank page. But those apps are set up per company, and they won’t stretch to cross-company reporting, custom extensions, or your own KPI definitions. Either way, the pain comes later—and it’s baked into the structure.
Why the Default Path Sets You Up for Sprawl
The native BC connector gives Power BI access to Business Central data through standard APIs and OData endpoints. Some common data, such as G/L entries, is available through Microsoft’s standard APIs, while other transactional tables may first need to be exposed through custom API pages or web services. Either way, you’re still bringing transactional Business Central data into Power BI without a governed semantic layer to help. That’s great for ERP accuracy, but not for analytics. Why? Because every question now means joining, filtering, and aggregating raw ledger rows with no semantic layer to help.
One analyst builds a sales dashboard and calculates margin their way. Someone else builds the P&L and does it differently. A third person needs inventory turns and pulls Item Ledger Entry into yet another PBIX, with their own logic. No one person is to blame, but now your business logic is scattered across disconnected models, each with its own definition.
It gets worse. In a busy manufacturing setup, Value Entry can mean five million rows over three years. The OData connector struggles with that volume—timeouts are common. And there’s no DirectQuery escape hatch—the BC connector runs in Import mode only, so every refresh pulls that load again. Fan-out joins—where Value Entry links to both Item Ledger Entry and G/L Entry—quietly inflate row counts and give you cost and margin numbers that look fine until someone checks them by hand.
What the Sprawl Costs Once It Sets In
Semantic drift is the first bill you pay. Gross margin shows up one way in sales, another in the P&L—someone’s wrong, but who can prove it? Even “active customer” isn’t clear when three departments use three filters. These disagreements already slow your close. Add Copilot, and the confusion just multiplies.
Performance is the next hit. That report that ran fine for one company and one month? Now it crawls when you add more entities, more history, and more users hitting refresh. Cross-company consolidation makes it worse, since BC endpoints are company-scoped—every company has to be queried separately before anything can be combined. Reconciliation happens in the report layer, not the data layer—so you’re left cleaning up the mess.
History is the third cost—and it’s the one most teams find out about too late. Move a customer to a new sales region or re-categorize an item in BC, and your prior-period numbers can shift—because most models join transactions to the current customer or item record, not the one in force when the transaction posted. Without a layer to preserve slowly changing dimensions, last quarter’s trend line shifts every time someone edits a master record.
Then there’s AI. Copilot, Q&A, and Fabric’s AI features all depend on clean, well-labeled semantic models: consistent names, clear hierarchies, stable KPIs. Ask for revenue by customer segment when that segment isn’t modeled as a stable dimension, and you’ll get an error or the wrong answer. AI only knows what you give it. If you hand it a sprawl of raw BC tables, it has nothing solid to work with.
The Fix: Integrate Once, at the Semantic Layer
So how do you stop the sprawl? The fix is simple to describe: extract Business Central data on a schedule, reshape it into analytics-friendly structures—fact tables for transactions, dimension tables for customers, items, accounts, and time—and load it into a dedicated reporting store. Power BI reports against that store, not live BC.
The difference is governance, concentrated in one place. KPI definitions get agreed once and reused everywhere downstream, so margin means the same thing in every report. Cross-company consolidation happens at the data layer before anything reaches Power BI. Historical data stays stable when BC master records change. Refresh times drop because Power BI queries a store built for analytics rather than a transactional database answering over OData.
The semantic model on top—the one Copilot and Q&A actually use—carries real field descriptions, explicit relationships, and definitions that hold across every report. That’s what turns an AI answer to “what’s driving margin decline in the Southeast” into something you can trust, not just a confident guess. Integrate Power BI to BC once, through a governed layer, and you never give sprawl a chance.
Where Cosmos Fits
Standing up that layer yourself means a data warehouse project: modeling raw BC tables into facts and dimensions, defining KPIs, and maintaining it as Business Central changes. Cosmos exists to skip most of that. It’s built specifically for Dynamics 365 Business Central Cloud, runs on Azure, and ships with a prebuilt, normalized BC data model plus 30 prebuilt reports across financials, sales, inventory, AP, AR, and Purchasing.
That flips your starting point. Instead of a blank warehouse and a requirements spreadsheet, you start with a working model that already covers common BC scenarios across entities. Now the conversation is about which reports matter most and where definitions need to line up. Power BI connects to that curated foundation, not raw ledger tables—so your dashboards sit on a governed model, not one analyst’s assumptions.
That same governed foundation supports both Power BI and Excel reporting, so teams are not rebuilding business logic separately for each tool. Power BI dashboards and Excel reports can draw from the same Cosmos warehouse, while multi-entity consolidation and data from other BC companies, ISV tables, Dataverse, and Azure SQL are brought together inside one model. The platform can run many reports at once without locking up Excel or slowing down Business Central, helping teams avoid the performance bottlenecks that tend to show up during close.
How to Pressure Test Your Integration Approach
Any connector looks great in a thirty-minute demo. The real test? Whether it survives the problems you deal with every month. Here’s what to ask:
- Can it run a live multi-company, multi-year BC report in seconds, not as a staged screenshot?
- Can a finance user adjust a report in Excel without writing SQL or DAX?
- Does it include a prebuilt BC data model and report library, or are you buying a custom modeling project with software attached?
- Do Power BI and any future AI work draw on one shared reporting foundation, or does each tool rebuild the logic separately?
- What happens as you add entities, history, and concurrent users?
- Was it designed for Business Central Cloud, or built for an earlier era and hosted later?
These questions matter for every option on the table. They’re what separate a real integration architecture from a connection that just happens to work right now.
Start at the Layer That Governs
Connecting Power BI to Business Central is always easy—and that’s exactly why sprawl sneaks up on you. The real decision is the layer underneath. Report against raw BC tables and you get drift, slow refreshes, and AI you can’t trust. Report against a governed semantic model and Power BI, Copilot, and whatever comes next all pull from numbers you can stand behind.
If your BC reporting is already spreading across PBIX files that don’t agree, now’s the time to have that conversation—before your next close.
See what governed Business Central Power BI integration looks like with your actual data.
