How to connect Power BI to Microsoft Dynamics NAV
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.
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.
What teams use it for
Inventory value and turns
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
Compares promised and actual shipment dates from Sales Shipment Lines, filtered by customer and product line.
Supplier performance
Receipt dates versus requested dates and price variances from Purchase Receipt and Invoice lines.
Production variance and scrap
Planned versus actual consumption and output from capacity and item ledger entries tied to production orders.
Open order backlog and cash
Backlog by ship month with customer ledger aging next to it, for sales and finance.
Planning exception overview
Counts of action messages by planner and age, to see whether the planning run is trusted.
Microsoft Dynamics NAV integration 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
- 1
Choose the source path
OData for small lookups and prototypes, SQL replica for mid-size sites, a warehouse with a star schema for large or multi-company groups.
- 2
Build dimension and fact tables
Create clean item, customer, vendor, date and location dimensions, and facts from ledger entries. Handle dimension sets and multiple companies explicitly.
- 3
Install and secure the gateway
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.
- 4
Model measures carefully
Define inventory value, margin and on-time delivery once as shared measures, and reconcile them to NAV reports before release.
- 5
Schedule refresh and incrementals
Refresh large facts incrementally using posting date. Run refreshes outside batch posting windows.
- 6
Add natural-language Q&A
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.
- 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?
- 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.
Keeping data 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 checklist
- 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.
Ways to get it done
Direct OData or SQL from Power BI Desktop
Effort: Days to a couple of weeks per report.
Small sites and quick prototypes with moderate data volumes.
Data warehouse with a modeled star schema
Effort: Typically 6 to 12 weeks for a first domain.
Large ledgers, multiple companies, or governance needs.
Partner-built content packs or Netray for the AI layer
Effort: Varies by scope; usually a few weeks on top of existing data.
You want pre-modeled manufacturing measures, or natural-language Q&A running privately.
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.
Frequently asked questions
Should Power BI read from NAV directly or a replica?
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?
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?
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?
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?
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.
More for Microsoft Dynamics NAV
Want Power BI working on your Microsoft Dynamics NAV?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment