# How to connect Power BI to Microsoft Dynamics NAV > Source: https://netray.co/integrations/dynamics-nav-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Connect Power BI to NAV through published OData feeds, a read-only SQL Server replica, or a data warehouse built from NAV tables. For on-prem NAV, the on-premises data gateway links the Power BI service to your network. A warehouse layer is usually worth it for ledger-heavy manufacturing reports. ## System Microsoft Dynamics NAV ## Capability power-bi ## Intro Power BI and Dynamics NAV are a natural pair, since both come from Microsoft and NAV's role centers have supported Power BI parts in several releases. Yet manufacturers often hit problems: slow OData refreshes, confusing per-company table names, and dashboards that disagree with NAV reports. The root cause is usually where the data is pulled from. Ledger entries run to millions of rows, and NAV stores some totals through SIFT indexes that are not obvious to a BI tool. Choosing the right connection path early saves months. This page compares the three main paths and shows which manufacturing dashboards are worth building first, plus how natural-language questions can sit on top. ## Use Cases ### Inventory value and turns **Description:** Value Entry and Item Ledger Entry rolled up by item category and location, with slow-mover flags and days of supply. ### On-time delivery to customers **Description:** Compares promised and actual shipment dates from Sales Shipment Lines, filtered by customer and product line. ### Supplier performance **Description:** Receipt dates versus requested dates and price variances from Purchase Receipt and Invoice lines. ### Production variance and scrap **Description:** Planned versus actual consumption and output from capacity and item ledger entries tied to production orders. ### Open order backlog and cash **Description:** Backlog by ship month with customer ledger aging next to it, for sales and finance. ### Planning exception overview **Description:** Counts of action messages by planner and age, to see whether the planning run is trusted. ## System Surface - OData feeds from published web services: simple to start, but large tables refresh slowly and field names carry NAV display quirks. - Direct SQL Server connection: fastest for big ledgers, with per-company table names such as Company$Value Entry. Use a replica to protect production. - SIFT and flow fields: totals shown in NAV pages may be computed, so a BI model must recalculate them from entries. - On-premises data gateway: required for the Power BI service to refresh from a server inside your network. - Role center Power BI integration: some NAV versions can embed reports in the role center, with version-specific setup. ## How It Works ### Choose the source path **Detail:** OData for small lookups and prototypes, SQL replica for mid-size sites, a warehouse with a star schema for large or multi-company groups. ### Build dimension and fact tables **Detail:** Create clean item, customer, vendor, date and location dimensions, and facts from ledger entries. Handle dimension sets and multiple companies explicitly. ### Install and secure the gateway **Detail:** Install the on-premises data gateway on a server that can reach SQL, use a read-only login, and plan for gateway clustering if reports are critical. ### Model measures carefully **Detail:** Define inventory value, margin and on-time delivery once as shared measures, and reconcile them to NAV reports before release. ### Schedule refresh and incrementals **Detail:** Refresh large facts incrementally using posting date. Run refreshes outside batch posting windows. ### Add natural-language Q&A **Detail:** Use Power BI Q&A or Copilot if your tenant allows, or a private assistant that queries the same semantic model for on-prem needs. ## Example Flows ### Nightly refresh of an inventory dashboard **Trigger:** A scheduled refresh runs at 02:00 after the nightly Job Queue completes. **Steps:** - Gateway connects to the SQL replica using the read-only login. - Incremental refresh pulls new Item Ledger and Value Entries by posting date. - Measures recalculate inventory value and days of supply. - Planners open the dashboard at shift start. **Result:** Fresh numbers each morning without hitting production during working hours. ### Manager asks a question in plain language **Trigger:** Operations manager types: which customers had late shipments last quarter? **Steps:** - The assistant maps the question to the on-time delivery measure and customer dimension. - It queries the semantic model rather than raw tables. - It returns a ranked table and a chart. - A drill link opens the underlying shipment lines. **Result:** Faster answers for non-analysts, using the same definitions as the dashboards. ## On Prem Power BI Desktop and Report Server can run entirely on-prem, while the cloud Power BI service needs the data gateway to reach your SQL replica. If data must never leave, use Power BI Report Server or another on-prem BI tool, and pair it with an open-weight model hosted locally for natural-language questions over the semantic model. Keep the replica or warehouse in the same network zone as NAV, and restrict BI users to curated views instead of raw NAV tables. ## Security - Use a read-only SQL login limited to curated views, not the NAV service account. - Apply row-level security in the model for plants, companies or sales regions. - Secure the gateway host and keep its credentials in a managed store. - Review who can export data from reports, since exports bypass NAV permissions. ## Options ### Direct OData or SQL from Power BI Desktop **Fit:** Small sites and quick prototypes with moderate data volumes. **Effort:** Days to a couple of weeks per report. ### Data warehouse with a modeled star schema **Fit:** Large ledgers, multiple companies, or governance needs. **Effort:** Typically 6 to 12 weeks for a first domain. ### Partner-built content packs or Netray for the AI layer **Fit:** You want pre-modeled manufacturing measures, or natural-language Q&A running privately. **Effort:** Varies by scope; usually a few weeks on top of existing data. ## Pitfalls - Pulling OData for million-row ledgers causes timeouts and angry NAV users. - Ignoring per-company tables and double counting in group reports. - Dashboards that do not reconcile to NAV, ending trust quickly. - Forgetting that flow fields and SIFT totals are not stored in base tables. - Refreshing during posting batches, which locks tables. ## Faq ### Should Power BI read from NAV directly or a replica? **Answer:** Use a replica or warehouse for anything beyond small volumes. Reading production directly can slow posting and users. A nightly restore or log-shipped copy is a common approach, depending on freshness needs. ### Do we need the on-premises data gateway? **Answer:** Yes, if the Power BI service in the cloud must refresh from a SQL Server or OData source inside your network. Power BI Report Server avoids the gateway but is a separate deployment. ### Why do my numbers differ from NAV reports? **Answer:** Usually due to flow fields, date filters, dimension handling, or including or excluding certain entry types. Reconcile one measure at a time against a NAV report and document the rule. ### Can we ask questions in plain English? **Answer:** Yes, through Power BI Q&A or Copilot features where available to your tenant, or a private assistant over the same semantic model for on-prem needs. Quality depends on well named measures. ### Will this still work when we move to Business Central? **Answer:** Your model, measures and dashboards can largely carry over if you separate the source layer. Only the connection and some table names change, so build the warehouse with that in mind. ## Search terms dynamics nav power bi, power bi for dynamics nav, navision power bi, connect power bi to dynamics nav, dynamics nav power bi odata, dynamics nav power bi gateway, dynamics nav manufacturing dashboard, dynamics nav power bi sql --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json