Navision Integration & Tips

You might already have aware of new Navision - CRM integration new features introduced in Navision 2009 R2. This integration technically gives possibility in Navision to capture data change tracking in a better way than earlier version. In this article, I want to share with you my point of view of this possibility data change tracking mechanism and give you alternative ways to achieve change tracking with another technology within Microsoft framework.

 

What is New in Navision 2009 R2 technically are couples of new event in Code Unit 1 “ApplicationManagement”. These new event handles wider span than some similar event which have already known well like OnGlobalInsert, OnGlobalModify, etc.  Below pictures show the new events that handle tables in Navision database. It is seem like Navision team likes to call them integration module as they give the related object name similar to this.

GetDatabaseTableTriggerSetup event

This event will be triggered automatically on the very first time a table get insert, modify, delete or rename. The objective of this event is to inform Navision framework to wire up the table to the event.  Notice that the return parameters insert, modify, delete and rename will determine which event to wire with the table id respectively.  This event will not trigger twice for the same table and you need to restart the NST service to reset the behaviour or re-open company if you are using classic client.

OnDatabaseInsert event

This event will be triggered when certain table id has been wire up with TRUE insert parameter and insert process take in place. The parameter of RecRef contains new value of the record.

OnDatabaseModify event

This event will be triggered when certain table id has been wire up with TRUE modify parameter and modify process take in place. The parameter of RecRef  contains updated value of the record.

OnDatabaseDelete event

This event will be triggered when certain table id has been wire up with TRUE delete parameter and delete process take in place. The parameter of RecRef contains the last value of the record.

OnDatabaseRename event

This event will be triggered when certain table id has been wire up with TRUE rename parameter and rename process take in place. The parameters of RecRef and xRecRef contain the latest value and old value respectively.

How to Make It Works

You should tick “enable connector” at “Marketing Setup” form In order to make this integration work. When you enable the connector, Navision will automatically setup some web service and create some records in table 5150 Integration Page. These are the pre-set tables that by default system will track the data changes, the list are hardcoded in code unit 5150 Integration management.

If you make any changes on above tables, Navision will be record them in table 5151-Integration Record and the good thing is that it update on the existing record and use Record ID as the searching key instead of incrementally create a new record. This is good that this table would not grow rapidly and it only stores the primary key values which are contained in Record ID column. Thus the Modified On column reflect the last changes made on this table and the record.

The integration features objective is to record down the data change tracking and setup relevant Navision web service to allow external system to fetch the information. Presently, NAV-CRM integration adapter will pull the Integration Record periodically and synchronize with CRM database state for any un-synch data state.

Key Important to know about it

Here some important information to know before you starts to utilize it.

• Regardless if your code calls insert/modify/delete with or without trigger, the database events above still prevail. These is mean that if you write a code with INSERT (True) or INSERT (False) or INSERT the OnDatabaseInsert event still trigger.
• The database event trigger is run immediately after table trigger. This is mean that if you have some code in OnInsert trigger table, then the OnInsert trigger will run first before it run OnDatabaseInsert trigger.
• Database event trigger will not run if there is any error in table trigger. This is mean if you have some code in OnInsert trigger table and there is an error occurs in this trigger then OnDatabaseInsert trigger will not occurs.
• The transaction will rolled back if there is an error occurs in Database event trigger. This is mean the database event trigger is part of transaction and need to carefully consider in your code customization.
• Any error in a transaction before Commit will cause data changes to roll back and this imply to the data change tracking.
• The Database event trigger will run multiple times as many as affected records if you use MODIFYALL OR DELETEALL.
• Lastly, Database event trigger will not trigger for temporary record variable.

Does it good or bad

Understand from above architecture and behaviour, I would like to share with you my opinion what is good and bad about the integration features:

1. It is good finally Navision has come out with this features that make possibility to do data change tracking in the very simple and straightforward design. It is much better offer compare to change log module and related events.
2. Unlike OnGlobalInsert, OnGlobalModify, OnGlobalDelete and OnGlobalRename which will not trigger if you do not call table trigger, the new database events prevail regardless of table trigger is being called. This is good to ensure any changes will be able to track down. If you have notice, even standard Navision code in some places may not call Modify (true) instead of Modify.
3. Despite of above good side on above point, the database events triggers is costly and more reactive. If you have probably ever create complicate logic with lengthy of codes for posting process it is most certainly inevitable to not call modify more than one time of the same table and record. As the result one process of transaction could hit multiple times updating the data change tracking. You may look at code unit 80-Sales-Post which handling Sales Order Posting process which is use modify multiple time for the Sales Header record.
4. Database events is part of your transaction thus you should ensure that the data change tracking update is always successful and it is extra process on every table access you have made.
5. The concurrency and frequent table locking is climbing up and inevitable if you have more tables to be tracked by this integration features. One thing is certain that integration Record table is congested with times of hit from many kind of transaction process and it is likely you will have performance decline.

IMHO, the integration features is giving us possibility to have some table synchronization with external system but I have doubt that it can be used for full scale of Navision database. For limited tables, less frequent update record, not overly large records, I guess the integration will work greatly and with minimum customization you can enhance for not only to CRM system but others.

Despite of this, there is another way to do data change tracking for data synch. SQL server 2008 R2 introduce change tracking which is more or less has same objective like Navision integration features.

SQL Change Tracking

SQL Server 2008 introduces change tracking, a lightweight solution that provides an efficient change tracking mechanism for applications. Typically, to enable applications to query for changes to data in a database and access information that is related to the changes, application developers had to implement custom change tracking mechanisms. Creating these mechanisms usually involved a lot of work and frequently involved using a combination of triggers, timestampcolumns, new tables to store tracking information, and custom clean up processes.Some advantages of change tracking:

• Reduced development time.
  Because change tracking functionality is available SQL Server 2008, you do not have to develop a custom solution. For more information, see Change Tracking Overview.
• Schema changes are not required.
  Using change tracking does not require the following tasks:
  • Adding columns.
  • Adding triggers.
  • Creating side tables in which to track deleted rows or to store change tracking information if columns cannot be added to the user tables.
• Built-in clean up mechanism.
  Clean up for change tracking is performed automatically in the background. Custom cleanup for data that is stored in a side table is not required.
• Change tracking functions are provided to obtain change information.
  Functions enable information to be easily queried and consumed The column tracking records provide detailed information about the changed data. For more information, see Change Tracking Functions (Transact-SQL).
• Low overhead to DML operations.
  Synchronous change tracking will always have some overhead. However, using change tracking can help minimize the overhead. The overhead will frequently be less than that of using alternative solutions, especially solutions that require the use triggers.
• Change tracking is based on committed transactions.
  The order of the changes is based on transaction commit time. This allows for reliable results to be obtained when there are long-running and overlapping transactions. Custom solutions that use timestamp values must be specifically designed to handle these scenarios.
• Standard tools to configure and manage change tracking.
  SQL Server 2008 provides standard DDL statements, SQL Server Management Studio, catalogue views, and security permissions. For more information, see Configuring and Managing Change Tracking.
   

For detail information of SQL change tracking can be browse in this URL: http://msdn.microsoft.com/en-us/library/bb933875.aspx

Enable SQL change tracking on Navision Database

 I am using SQL Server 2008 R2 express edition and install Navision 2009 R2 demo database. Firstly I need to enable the change tracking at database level by using SQL server management studio. I go to the Demo NAV (6-0) database property and select Change Tracking page and set Change Tracking property to True.

In this demonstration, I would want to change tracking for table 13-Salesperson/Purchaser. Likewise how we do for database level, I would set the Change Tracking property to True at [CRONUS Singapore Pte_ Ltd_$Salesperson_Purchaser] table property as follow. I will leave the set track columns updated as it is to make this demonstration not too complicated.

Once you activate change tracking on certain table, SQL server create a new internal tables to store the change tracking records. I am using query in SQL management studio to see the information provided in change tracking table. I run following query and currently the change tracking for salesperson/purchaser is empty. The CHANGETABLE takes two parameters, the first is the internal change tracking table which CHANGES command will return internal names by passing the table name and the second parameter is the tracking version number. At this moment I would like to return all the change tracking of salesperson/purchase table. Notice that change tracking tables will provide key columns according to the Primary key tables. For my case, the code column is the only PK for salesperson/purchaser table.

I am creating a new salesperson in Navision as you can see in below picture. When I am doing this, Navision has triggered insert follow by modify and I will expect the SQL change tracking capture some information.

Let’s see what we have got from change tracking table. I run the same query and now we have something in query result.

 

SYS_CHANGE_VERSION show the latest change version and it is higher than SYS_CHANGE_CREATION_VERSION. It is higher because insert take place first after modify and change version 12 is belong to record creation and change version 13 is belong to modify. Interestingly the data change tracking do not split the line for each operation but merge them into line as for the same key column value. The SYS_CHANGE_OPERATION column show “I” represent Insert operation and it is due the version request is lower than insert version. The “Code” column show AK as expected of primary key value.

One important thing to understand change tracking version number is not exclusive for a table but database level and it should be starting from 1 for the first change operation. Fortunately SQL Server provides CHANGE_TRACKING_MIN_VALID_VERSION command that return minimum version for certain table so we could easily track the changes start from. With this, I could refine above query by using 11 as version parameter. The query result would be the same as previously. The query will filter the change tracking version greater than 11.

Next query is the same query with version parameter 12 and I would have following result. The SYS_CHANGE_CREATION_VERSION now is Null as expected and SYS_CHANGE_OPERATION is “U” indicate as update operation. You have notice that the version number play important part in the data change tracking and this number will increase for every committed transaction made. Thus, it is important to know the current version to check if there is any data change made since last version that we have queried earlier. SQL has command called CHANGE_TRACKING_CURRENT_VERSION that return the current tracking version. At his moment I have made changes in the salesperson/purchaser table on the same record above and now the current version is increased to 14.The next data changes I would want to do is to delete salesperson/purchase code AK and update on salesperson code AH. The change tracking data show the result as below picture. The change tracking result show the latest version on the top line which belongs to update operation of salesperson/purchaser code AH and follow by code AK. The SYS_CHANGE_CREATION_VERSION show blank for code AH and it is because the record creation was done before data tracking is enabled. Interestingly SYS_CHANGE_OPERATION column show “D” for code AK as the Deletion operation is the highest rank among other operation.

Conclusion

I do believe some Navision professional and partner has long time looking for a way to synchronize Navision data to other application as storage cache offline or online in the better way. I found both methods above are making the objectives possible with some marker to take notes.  Navision integration features method is technically is not that good compare as SQL change tracking in term of capacity, efficiency and performance, but more flexible to customize the data content as it is naturally within the Navision domain. The other hand SQL change tracking although is better in technical aspect but need to have extra development for such data access layer to fetch tracking data and cannot influence or make condition how tracking data is been generated. I do hope Navision in the future able to take benefit of SQL change tracking features and integrate them within Navision domain features. I am quite understood that SQL change tracking is quite new and not all Navision customer has using SQL Server 2008. I hope this article is quite useful and give you a new idea of innovation.