Reporting from NAV: the benefits of OData

I’m often asked what options are available when reporting from Dynamics NAV. While NAV’s internal tools, such as Analysis Reports, are quite powerful, some users prefer to leverage tools such as Excel and Power BI.

The Dynamics NAV and Dynamics Business Central architecture has evolved to allow more compatible methods for reporting, utilising technologies such as Open Data Protocol (OData).

Architectural Background

Older versions of Dynamics NAV worked directly on the database. Users connected directly to the database and business logic ran on the user’s computer. This means the user had permissions to access the database directly, and could do so with Excel or any other SQL-compatible tool.

With the introduction of the Role-Tailored Client several years ago, the Dynamics NAV architecture was abstracted into a new Service Tier arrangement.

This isolated the SQL Server, and moved business logic into the Service Tier machine. This paved the way for different deployment styles and clients, such as the Web Client, Phone and Tablet client, and Cloud-based deployments.

Without direct access to the database, how can users report on system data?

What are the benefits of OData?

OData is becoming the primary method for externally accessing data from Dynamics NAV.

It has several benefits compared to SQL:

Works natively with Excel and Power BI.

The PowerQuery add-in for Excel 2013 (standard in Excel 2016+) supports OData feeds for accessing data out of NAV.

The engine used in Excel is identical to the one used in Power BI, so if you know Excel, you’ve a head start in Power BI. Awesome dashboards are just around the corner!

Security

The SQL Database is isolated and only the Service Tier is authorized to talk to it. This adds a layer of security in between the user and the database.

Your database isn’t exposed externally to the internet, reducing the chance of being exploited by malicious users.

Respects NAV Permissions

In the direct model, users were setup to access data directly and permissions in Dynamics NAV were ignored. By using OData, the user must be setup within NAV as a user, and the access to data matches what they can see in NAV.

If someone was accessing data directly through SQL, without maintaining two sets of permissions, they may have had access to more than you bargained for—e.g. payroll.

Leverages caching

The Service Tier has its own cache of data to keep the system running fast. Bypassing the cache requires SQL to maintain its own cache and slow down other users of the system.

Works with FlowFields

FlowFields are a special type of NAV field. These maintain pre-calculated answers to questions like, “how many of this item do I have in stock?” so that summarised data is readily available (for example, the Inventory column on the Item List).

These fields don’t exist in the database in the classic sense—a SQL query wouldn’t be able to see the Column on the Item table (it exists as an “indexed view” of sorts).

Without FlowFields, Direct SQL reports would need to manually sum every Item Ledger Entry for that Item No. in the system to come to the same answer. This takes more resources than accessing the pre-calculated value.

Reduces Hardcoded Data

Any SQL statement that’s run outside NAV will require “hardcoded” values such as Company Names, Location Codes, Item Group Codes, etc. If these change in the system, the queries break.

Integration

OData can not only read data, but can also update data. This gives you options to integrate into third party applications and have data flow between two systems using a standardized language.

What’s around the corner?

The Business Central development team has introduced Representational State Transfer  (REST) interfaces to common data within the system. These endpoints are published under central endpoints, such as the Microsoft Graph. This is known as the Common Data Service, a standardized model for accessing data within the Dynamics 365 ecosystem.

Many new features are on their way, including “deltas” which only show changes, and “deep inserts” that help publish multiple related pieces of information at once (e.g. Sales Header and Lines) without needing to worry about locking.

This paves the way for next-gen integration and reporting options for Business Central.