# How to connect Power BI to Dynamics 365 Business Central > Source: https://netray.co/integrations/business-central-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## 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. ## System Microsoft Dynamics 365 Business Central ## Capability power-bi ## Intro 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. ## Use Cases ### Inventory and stock coverage **Description:** 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 **Description:** Promised versus actual receipt dates per vendor from purchase lines and posted receipts, with open order exposure. ### Production variance **Description:** 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 **Description:** Open sales lines by promised date, shipment performance and backlog by customer segment. ### Finance dashboards with dimensions **Description:** G/L entries with global and shortcut dimensions, budget comparison and cash flow, aligned with your chart of accounts and fiscal calendar. ## System 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 ### Choose the data path by volume **Detail:** 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. ### Select tables and publish what is missing **Detail:** 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. ### Set up credentials and gateway **Detail:** 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. ### Model for the business **Detail:** 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. ### Schedule incremental refresh **Detail:** Filter ledger tables by posting date or modified date and refresh only recent partitions, with a full refresh on a slower schedule. ### Reconcile and secure **Detail:** Reconcile totals to standard Business Central reports, configure row-level security, and publish through a workspace app. ### Add natural-language Q&A **Detail:** 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. **Steps:** - 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? **Steps:** - 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. ## 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 - 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. ## Options ### Built-in connector plus AppSource Power BI apps for Business Central **Fit:** Small to mid-sized companies who need standard finance, sales and inventory views fast. **Effort:** Days. ### OData queries or custom API pages feeding a curated model **Fit:** Manufacturers needing production and capacity data not in the standard connector. **Effort:** Two to four weeks. ### Warehouse plus governed natural-language layer (in-house or with Netray) **Fit:** Large ledgers, multi-system reporting or private, on-premises requirements. **Effort:** Four to ten weeks depending on scope. ## 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. ## Faq ### Should I use the connector, OData or a data warehouse? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms business central power bi, power bi dynamics 365 business central, connect power bi to business central, business central power bi on premises gateway, business central odata power bi, business central manufacturing dashboard, business central power bi connector, business central power bi copilot, business central data warehouse, business central inventory dashboard power bi --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json