Reporting Performance with Dynamics NAV Web Services
This information applies to Dynamics NAV 2013 and higher.
Overview
Some report designers and users experience slower report performance when they move to Microsoft Dynamics NAV 2013 (or higher) from prior versions. Microsoft Dynamics NAV 2013 introduced new architecture and features that can affect the reporting speed for the Reporting Intelligence Excel add-in.
There are many factors which can affect reporting speed – some of which can be controlled.
Communication changes with NAV 2013
With prior versions of Dynamics NAV, the Reporting Intelligence Excel add-in communicated to the Dynamics NAV database through C-Front (part of the Dynamics NAV installation)
With the introduction of Dynamics NAV 2013, Microsoft eliminated the C-Front connection and instead introduced Web Services as a communication connection.
Web services are a lightweight, industry-standard way to make application functionality available to a wide range of external systems and users. This change brought many benefits to Dynamics NAV users, including the web client, additional hosting options, and enhanced communication with other applications.
This change did result in a negative impact on reporting performance, however, due to an inherent latency (delay) in web service communications.
Ways to maximize report performance
-
Use the SQL Server Connector
If you have direct access to your Dynamics NAV SQL database and can use Windows Authentication or NAV User Password Authentication to access your Dynamics NAV system, your best performance option may be the Reporting Intelligence NAV SQL Server connection.
-
Operate the latest version of the Reporting Intelligence Excel add-in
Reporting Intelligence continually identifies and introduces performance improvements for our products. Running the current version of Reporting Intelligence will ensure that you are benefiting from the latest advances.
-
Install the latest Reporting Intelligence Business objects in Dynamics NAV 2013 (or higher)
Ensure that the latest version of the Reporting Intelligence business objects are installed in your Dynamics NAV instance. The latest version of the business objects are always included with the current Reporting Intelligence product download file or can be downloaded separately
You can check the Reporting Intelligence Release Notes to verify the latest version number.
-
Enable Reporting Intelligence Performance Optimizations
The full Reporting Intelligence Excel add-ion includes several features which are designed to optimize performance. Confirm that these are enabled. These settings can be found under the Applications Settings tab on the Reporting Intelligence ribbon. Then, select " Performance ". Additional information is available in the article on Batch Function Execution Optimization and the article on Table Cache Optimization.
-
Operate the latest version and hot fixes for Microsoft Dynamics NAV
Microsoft also continually introduces performance improvement to Dynamics NAV. Running the current version and hot fixes will ensure that you are benefiting from the latest advances.
Hot Fix 2831582 improves report performance for Dynamics NAV 2013.
-
Increase the SOAP Max Message Size
Increasing the maximum size for SOAP messages above the default size can improve performance on some reports. This can be set using the Microsoft Dynamics NAV Server Administration Tool.
-
Dedicate an instance of the Dynamics NAV server for reporting
Microsoft Dynamics NAV 2013 and higher utilizes a three-tier architecture that allows multiple instances of the Microsoft Dynamics NAV Server (the middle tier). Dedicating an instance of a Microsoft Dynamics NAV server for reporting will avoid cross traffic with other communication.
-
Proper Table Indexing
Proper indexing of tables in Dynamics NAV optimizes report performance. Ensure that the tables in your NAV database are properly indexed for your reporting needs. This is especially important on large transaction tables.
-
Use best practices for report design
Minimize the number of queries - Each separate Web Service query increases the time it takes for a report to finish. Designing reports to reduce the number of queries can significantly improve performance.
A report that uses NL("Rows") and NF() functions will generally perform faster than a similar report which uses a series of individual NL() functions.
Reports build using the NL("Table") function will generally perform faster than similar reports made using other Reporting Intelligence functions.
Select the proper keys in your report – Selecting the best key for your queries can greatly improve performance.
-
Other suggestions
Schedule reports to be run during slow periods.
Report from a data warehouse. Jet Analytics includes a data warehouse which is designed expressly for reporting from Microsoft Dynamics NAV 2013 and higher. The data warehouse combines data from multiple tables in your Dynamics NAV database into a single table. This makes reporting much more efficient.
Report from data Cubes Reporting Intelligence Enterprise includes a set of OLAP Cubes for Microsoft Dynamics NAV 2013 and higher. These cubes provide the fastest access to the data.