This week on Tech Tuesday, we will go over a list of helpful hints and tips to improve the performance of your maps integrating in and out of Business Central/NAV.

 

SOAP or Odata?

When you first go configure the connector for Business Central or NAV you will have two options available.

  • Dynamics NAV/D365 Business Central Connector: This is the legacy connector using the NAV SOAP web services. This connector should only be used when connecting to the following versions of NAV. If you are using a newer version of NAV the Odata connector should be used.
    • 2013 R2
    • 2015
    • 2016
  • Dynamics NAV/D365 Business Central Odata Connector: This connector uses the BC/NAV OdataV4 web services to connect to the application. This has several benefits over the SOAP Connector
    • Better Filter Options in data sources
    • Better performance with large data sets
    • Ability to select and join multiple service endpoints
    • Access to Query web services
    • Does not require user to specify which fields are being used in a destination

Data Sources

There are several ways queries of BC/NAV data can be optimized and improved.

SQL

If this is a local installation of BC/NAV, then you will see the best performance by using an Odbc data source to connect via SQL to the NAV tables. The SQL tables can be used for reading data, but SQL should NOT be used for creating and updating data, because it does not conform to the business logic setup in BC/NAV.

Odata Filters

As I mentioned above, the best performance through web services is available through the OdataV4 web services. When you select Odata you have two locations you can add a filter.

  1. The first location is directly on the service. This tells BC/NAV to filter the data set before sending it to SmartConnect. This is the best location to add filters, because it reduces the amount of data sent to SmartConnect, and less unnecessary information will lead to faster map runs.
  2. The second location is when you click ‘Edit Query’ you can combine multiple select services and add filters here. This requires SmartConnect to pull all data from all your selected services, then combine that data. This will result in slower processing and depending on the size of the data sources can cause memory issues on the SmartConnect machine.

Query APIs

Query APIs are the most efficient way to query data from BC/NAV. Here is a list of some of the ways Query APIs are more efficient than page APIs.

  • They are “No Lock” meaning they don’t lock the tables being accessed.
  • They can perform calculations and aggregate data.
  • Limit data set to only specified columns.
  • One web call can read data from multiple tables.

I want to expand a little more on that last point. If we select a simple scenario pulling Sales Orders out of BC, there can be a lot of data coming from several tables. If I have 100000 Orders in my database and each one has 5 lines, that is half a million records that I would have to pull into SmartConnect, parse, and filter. I can filter my headers, but it is harder to limit the lines because they may not contain my filter criteria field. If I am using a query data source, only relevant lines are returned from BC greatly limiting my data set. This will improve the speed BC can return the data, and the speed SmartConnect can process it.

Here is an article documenting creating an API query.

 

Destinations

There are fewer options for optimizing destinations. The main reason SmartConnect moves at the speed it does with BC/NAV destinations is because after every document is sent, we wait for a response of either success or failure and notify the user of the outcome. There are a couple of ways you can improve the time SmartConnect takes to process each document though.

Linked Entities

Some entities in BC/NAV have linked entities. An example would be Sales Invoices will be linked to Sales Lines. You can expose the linked services by checking the ‘Include linked entities’ box in the map destination. Now when SmartConnect sends the header information, it will include the line level detail, which will use one API call on a process that would’ve required extra calls for each line, causing the web calls to grow exponentially.

 

Lookup Fields

Lookup fields can slow down the integration speed, and use more of the limited available API calls. If possible, reduce the number of lookup fields by using other methods to find the correct value. Including any of the following options.

  • Multi Data Source: Combine the data from the original data source with the destination service that you would use as a lookup. This will require one call to BC/NAV to pull in relevant lookup information, rather than calling the service on every document processed.

 

  • Translation Table: Setup a translation table to convert commonly used data. A good scenario to use this approach is currency fields. If the source system reference currency differently than BC, you can lookup the value in a translation table in SmartConnect rather than calling BC for every record.

 

  • Calculated Column: Maybe there is some custom logic you can use to find the value without needing to call the external service. I had a case recently where the Customer Names were the same in both systems, but the Customer Number was different. I needed the Customer Number, and after some digging a found the first 3 characters of the Customer Name were being appended to the Customer Number is the external service. So, I used a calculation to remove those characters before import, and eliminating the need for a lookup based on Customer Name.

Conclusion

In conclusion, to get the best performance out of SmartConnect when pulling data out of BC/NAV, we recommend using the Odata connector and if possible, building custom queries in BC/NAV so the SQL server will run the queries and return only the selection of data needed, and not full tables of data.

To get the best performance out of a SmartConnect destination, make sure you are using ‘linked entities’ when available, and eliminating unnecessary lookup fields.