How to connect Power BI to Dynamics 365 Business Central
Short answer
Power BI reaches Business Central through the built-in connector, OData web services, or a replicated warehouse. Small models can use the connector directly, but larger ledgers usually need a warehouse, and on-premises installs need the on-premises data gateway.
Business Central and Power BI are both Microsoft products, so the first dashboard usually works in an afternoon. The trouble comes later, when the item ledger has millions of rows, refresh times creep up, and numbers on a dashboard disagree with the Business Central page the CFO opens.
Most of the work is therefore not the connection. It is deciding where the data model lives, which tables are replicated, how dimensions and posting dates are modelled, and who maintains it when a new extension adds fields.
This page walks through the realistic connection options, the manufacturing views worth building first, and how to add a natural-language layer on top without giving the model write access or bypassing your measures.
What teams use it for
Inventory and stock coverage
Item ledger entries and value entries rolled up by location, days of cover and slow-moving flags. Reconcile the total to the inventory valuation report before sharing.
Purchase and supplier performance
Promised versus actual receipt dates per vendor from purchase lines and posted receipts, with open order exposure.
Production variance
Planned versus actual consumption and output, capacity ledger hours versus routing, scrap by item. Needs production order and ledger tables, often via custom API pages or OData.
Order book and on-time delivery
Open sales lines by promised date, shipment performance and backlog by customer segment.
Finance dashboards with dimensions
G/L entries with global and shortcut dimensions, budget comparison and cash flow, aligned with your chart of accounts and fiscal calendar.
Microsoft Dynamics 365 Business Central integration surface
- Power BI Dynamics 365 Business Central connector: reads the standard API v2.0 entities and, depending on the connector option, published web services. Convenient but limited by API coverage and throttling on big tables.
- OData V4 web services: publish queries or pages through the Web Services page and consume them with the OData feed connector. Query objects in AL let you pre-join and filter before data leaves the service.
- Replicated warehouse: export Business Central data to Azure Data Lake or Azure SQL, or use a community or partner replication tool, then model in a warehouse. Microsoft's own export options have changed over time, so check the current guidance for your release.
- On-premises Business Central: you can query the SQL database read-only for reporting in some setups, but direct SQL bypasses permissions, ignores extension-managed table naming and can break on upgrades. Prefer OData or API through the on-premises data gateway.
- Embedding: Power BI reports can be shown on Business Central pages and the role center, which keeps users in one place. Row-level security in Power BI is separate from Business Central permissions and must be set up deliberately.
How it works
- 1
Choose the data path by volume
Connector or OData for small and medium companies. A warehouse once ledger tables reach millions of rows or when you need history and cross-system joins.
- 2
Select tables and publish what is missing
List the tables for each dashboard. Publish AL queries or custom API pages for production, capacity and any custom fields. Avoid pulling whole tables you do not use.
- 3
Set up credentials and gateway
SaaS uses Entra ID. On-premises needs the on-premises data gateway installed on a server that reaches the service tier, with a service account that has a narrow permission set.
- 4
Model for the business
Build a date table, a dimension model and shared measures such as on-hand value, open order value and on-time delivery. One definition per measure, reviewed by finance.
- 5
Schedule incremental refresh
Filter ledger tables by posting date or modified date and refresh only recent partitions, with a full refresh on a slower schedule.
- 6
Reconcile and secure
Reconcile totals to standard Business Central reports, configure row-level security, and publish through a workspace app.
- 7
Add natural-language Q&A
Use Power BI Copilot or Q&A where licensed, or put a governed assistant over the semantic model so answers use your measures instead of free-form SQL.
Example flows
Weekly inventory cover review
Trigger: Scheduled refresh completes at 05:00 on Monday.
- Incremental refresh loads new item ledger and value entries.
- Measures compute on-hand, average usage over 90 days and days of cover per item and location.
- Report highlights items under two weeks of cover with open PO dates.
- Planners open the item in Business Central from a link in the report.
Result: A planner reviews exceptions rather than building the list in Excel.
Ask a question of the sales model
Trigger: A sales manager types: which customers had late deliveries last quarter?
- The assistant maps the question to existing measures in the semantic model.
- It runs a read-only query against the model, not the live ERP.
- It returns a table and the measure definitions used.
Result: A fast answer consistent with the published dashboard, and no new numbers invented by the model.
Keeping data on-prem
For on-premises Business Central, install the Power BI on-premises data gateway on a server that can reach the web service tier, and prefer OData or API access over direct SQL. If reports must also stay on-premises, Power BI Report Server can host them, with feature differences from the cloud service, and natural-language features that depend on cloud services will not be available there. For a fully private natural-language layer, expose the semantic model or warehouse to a locally served open-weight model through a read-only query tool. This keeps questions and results inside your network while reusing the same measures your dashboards use.
Security checklist
- Use a dedicated read-only service account for refresh, with permissions limited to reporting tables.
- Implement row-level security in Power BI for company, location or customer scope, and test it with real accounts.
- Do not let analysts connect directly to the production SQL database on on-premises installs.
- Control workspace membership, sharing and export settings; dashboards often contain margin and pricing data.
- For natural-language assistants, run queries read-only against the semantic model or warehouse with row limits and logging.
Ways to get it done
Built-in connector plus AppSource Power BI apps for Business Central
Effort: Days.
Small to mid-sized companies who need standard finance, sales and inventory views fast.
OData queries or custom API pages feeding a curated model
Effort: Two to four weeks.
Manufacturers needing production and capacity data not in the standard connector.
Warehouse plus governed natural-language layer (in-house or with Netray)
Effort: Four to ten weeks depending on scope.
Large ledgers, multi-system reporting or private, on-premises requirements.
Pitfalls
- !Importing entire ledger tables daily and hitting API throttling or refresh timeouts.
- !Multiple dashboards with different definitions of inventory value or open orders.
- !Ignoring dimension handling: dimension set entries need careful modelling or totals will not match.
- !Reading from SQL directly on on-premises and breaking when an upgrade changes table structure.
- !Assuming Power BI security equals Business Central permissions. It does not.
Frequently asked questions
Should I use the connector, OData or a data warehouse?
Use the connector for small to medium data and standard entities. Use OData queries or custom API pages when you need specific joins or manufacturing tables. Move to a warehouse when volumes, history or multi-system reporting make direct refreshes slow or fragile.
Do I need a gateway for Business Central?
For SaaS Business Central, no, Power BI connects over the cloud. For on-premises installs, yes, the on-premises data gateway is needed unless the service tier is reachable from the Power BI service by other approved means.
Can I query the Business Central SQL database directly?
Only on on-premises installs, and it is rarely wise. You bypass permissions, depend on internal table names and risk breakage on upgrade. Published queries and API pages are safer and keep your dashboards supportable.
How do I add natural-language questions to Power BI?
Use Power BI Copilot or Q&A if licensed and the data policy allows it. Otherwise, build a read-only assistant over the semantic model or warehouse that uses your published measures. Either way, validate answers against known dashboards before roll-out.
Why do my Power BI numbers not match Business Central?
Common causes are date filters on posting versus document date, unmodelled dimensions, unposted documents, and different value entry logic for inventory cost. Reconcile each measure to the matching standard report before publishing.
More for Microsoft Dynamics 365 Business Central
Want Power BI working on your Microsoft Dynamics 365 Business Central?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment