Skip to content
EN
Contact

Dynamics NAV Power BI dashboard

We bring all the companies in your NAV database together in one Power BI model, ready for Business Central. We built an ERP dashboard for Fokker Services Group.

Discuss your NAV environment Read the Fokker Services Group case study

A single dashboard connects Fokker Services Group’s ERP system to external marketplaces

Fokker Services Group wanted real-time insight into all relevant parts data during the quotation process. We built a central dashboard that connects its ERP system to multiple external marketplaces, using Python, SQL and Dash.

Read the case study

Can Power BI read from our Navision database without slowing down NAV?

Every month, a controller runs the same NAV reports for each company and pastes them into Excel. Power BI can read from the SQL Server database or via web services, but each option has implications for permissions, system load and a later transition.

Schedule a conversation

A Navision dashboard relies on a load step for each company, one model and a gateway

Discuss your NAV environment

All companies in one model

NAV stores each company’s data in its own tables within the same database. We load the data with a company code, so you can view it by company or on a consolidated basis.

Read data without disrupting accounting

We load only new entries, using a read-only account and, where possible, a copy, so NAV users do not notice a difference.

A gateway to your server

An on-premises data gateway connects Power BI to your NAV server through outbound connections, without opening inbound ports on your network.

ERP data to guide decisions

Revenue, margin, inventory and outstanding entries by company, as we did for Fokker Services Group when we brought ERP and market data together in one dashboard.

We read the database first, then design the reports

  1. Map the database and companies

    Which NAV version, which companies, which custom tables, and which reports are currently circulating in Excel.

  2. Load the ledger entries and validate the model

    We read the ledger entries for each company, build the model and check the results against the reports from NAV.

  3. Go live with a gateway and management

    The gateway, refresh schedule and access permissions go live with a designated owner, and the loading step can be replaced independently.

Build your own Power BI model on NAV now, or wait for the move to Business Central?

Talk it through with us

A good fit if

You run NAV on-premises for multiple companies and want to compare their figures now, even if the move to Business Central is still ahead.

Not a good fit if

The move to Business Central is already decided, and your current reports are sufficient until then. In that case, build directly on Business Central’s APIs.

What determines the scope

The number of companies, how much customisation NAV contains, whether a copy of the database is available, which other sources you use alongside NAV, and who manages the model.

Will the work be wasted after the move?

Not if the model is independent of the source. We keep the data loading step separate from the data model, so moving to Business Central mainly changes how the data is retrieved. Reports and definitions stay in place.

Directly from SQL Server, via NAV web services or via a data warehouse?

Directly from SQL Server

Choose this if NAV runs on-premises and you want to get started quickly. Use a read-only account, preferably to access a copy of the database.

Via OData web services

Choose this if you want data as it appears on a NAV page or in a NAV query. Our advice: use this for master data and read large transaction tables directly from the database.

Via a data warehouse

Choose this if NAV is one of several sources, or if historical data needs to be preserved through the transition. See how to get a data warehouse built.

Turning ERP data into management insights is something we do regularly

Data Science Lab is a Microsoft Partner for Fabric and has worked with data and AI since 2016. For Fokker Services Group, we brought ERP and market data together in a single dashboard; for Ammega, we built Power BI dashboards and a Power BI app.

Book an introductory meeting

Your NAV version and companies are the starting point for our first conversation

Stef Stoelwinder

Stef Stoelwinder

Business Development

Stef is your first point of contact. In our first conversation, we’ll discuss your NAV version, the companies in your database and your current reports.

E-mail info@datasciencelab.nl

Phone +31 (0)20 3636 724

Which NAV companies would you like to see in a single Power BI overview?

Tell us which NAV version you use, how many companies are in the database and which reports you currently produce. We’ll reply by email or suggest an initial meeting.

Emailinfo@datasciencelab.nl Phone+31 (0)20 3636 724

Enter a valid email address.

Let us know how we can help you.

Frequently asked questions

Microsoft describes two routes: from the SQL Server database where NAV stores its data, or through OData web services that you publish in NAV. We bring the data in through a loading step and build the Power BI model on top of it.

To find out why we start with the decision rather than the charts, see Power BI dashboards.

For tables with the DataPerCompany property, the company name appears before the table name, separated by a dollar sign. In SQL Server, the customer table for the demo company is called CRONUS International Ltd_$Customer, for example. Tables without that property are shared by all companies.

The loading step reads the same table for each company and combines the data with a company code. That means a new company is included automatically.

When you want data as a NAV page or query displays it, including its filters and calculated fields. You publish the page or query in the Web Services window. The OData URL contains the company name, so each company has its own address.

For large tables, such as general ledger entries, we prefer to read directly from the database using a read-only account.

Because NAV usually runs on your own servers, while the Power BI service runs in the cloud. The on-premises data gateway is Windows software on your network that establishes the connection through outbound connections. For shared reports, use standard mode rather than personal mode.

We explain how we set up workspaces and permissions for each team at Power BI consultant.

According to Microsoft’s lifecycle page, mainstream support for Dynamics NAV 2018 ended in January 2023, and extended support ends in January 2028. The same site lists separate dates for older versions.

Its successor is Dynamics 365 Business Central. For a dashboard, that means building the model so you can change the source later.

Microsoft describes a path where you first upgrade from NAV to Business Central on-premises, version 14 or later, and then migrate to Business Central online. Custom-built functionality in C/AL must become AL extensions; otherwise, the data in those tables will not be migrated. The alternative is a reimplementation using only master data, balances and settings.

In the model, the way you retrieve the data changes, but the definitions do not.

Business Central online makes data available through API pages and queries, an OData v4 service recommended by Microsoft, and published web services. Since February 2022, Power BI reports have read from a read-only copy of the database.

We use the same approach with a separate model for SAP Business One, SAP S/4HANA and Exact Online.

If you have one company, NAV’s standard reports meet your needs and the move to Business Central is already planned, build the dashboard once in the new environment. Or, if each company records its entries differently, sort out the underlying data first; see data quality.

You can read about the management information to include in a financial dashboard in Power BI on that page.

What else do you need to decide when using Power BI with an ERP database?

View all services