Business Central, Performance and Data Warehouse. Part II

There are many ways to track changes in the Business Central. I’ll give you an example of a few of them.

  1. Field “Last Modified Date time”. Here we are only talking about master tables, such as Item, Customer, Vendor, etc.

Pros

No additional development required. It could work well for small companies to update data source directly in Power BI, for example.

Cons

  • This field is not present in all master tables. And most often it is not in the tables that we need for analysis 🙁 . So, we should create table extension with all the ensuing consequences.
  • Plus, this field is changed in the triggers OnInsert(), OnModify(), OnRename(). We can miss some changes in data if there are insert or modify operations without executing the code in the triggers – Records.Insert(), Record.Modify() or Records.Insert(FALSE), Record.Modify(FALSE). For example, for better performance we want to use bulk insert, modify or delete.
  • We can’t track record deletions. But it depends on the data warehouse configuration. Very often we don’t delete data in a data warehouse at all to maintain the integrity of historical data.
  • When we rename record the system delete the old one and create new one. Let me remind you that there are no foreign keys in the BC database. Consequently, based on information from system virtual tables, such as table [Field] columns [RelationTableNo], [RelationFieldNo] , the system changes links in all related tables. That’s why it takes so long sometimes. Therefore, Rename (changing primary key) is a headache for any integration. And it’s best to avoid it as much as possible.

2. Field “Entry No.”. This applies to transactional tables as “Item Ledger Entry”, “Value Entry”, “G/L Entry”, etc.

Based on the postulate that data in transactional tables is not edited or deleted, we can use this field for replication, each time saving the value of the last replicated “Entry No.”. But there are always exceptions. In my practice, clients often change data manually for various reasons, mainly fixing errors. And in this case there will be a discrepancy between the data in the data warehouse and in the system.

3. Change Log

On the topic of using Change Log, I will dwell in detail in the next article.

But my advice is to use Change Log only in the master tables, only for certain fields and only when it is really necessary.

4. ISV Solutions as Replication in LS Central

Replication of data is a big part of the LS Central system, with the aim of having seamless flow of data between locations.

Replication and Data Director are a very broad topic. Here I would like to note that despite the fact that the main task is replication between the stores and head office, we can still use replication and Data Director to synchronize data with data warehouse.

Depending on the situation and budget, we may use the methods listed above with some limitations. But let’s get back to the topic of this series of articles and look at how we can use Change Tracking to synchronize data with data warehouse.

Change tracking

Change tracking captures the fact that rows in a table were changed, but doesn’t capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that don’t require the historical information, there is far less storage overhead because of the changed data not being captured. A synchronous tracking mechanism is used to track the changes. This has been designed to have minimal overhead to the DML operations.

No alt text provided for this image

How to enable Change Tracking for a Database

You can enable change tracking using the next command:

ALTER DATABASE [Demo Database BC (22-0)] 
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

Or in SQL Server Management Studio by using the database Properties dialog box.

No alt text provided for this image

Enable Change Tracking for a Table

You can enable change tracking using the next command:

ALTER TABLE [CRONUS Canada, Inc_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972] 
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

Or in SQL Server Management Studio by using the Table Properties dialog box.

No alt text provided for this image

Here it is appropriate to ask the question – Do we need to enable change tracking for the principal table and all table extensions?

In our example, we have two tables related to table “Item”.

No alt text provided for this image

My answer is No. Every time when we change field in table extension record we update record in the principal table also.

Change Tracking and Business Central

One fact to support the use of Change Tracking that Change tracking is enabled in Dynamics NAV/BC databases by default.

For Dynamics NAV 2018 change tracking is enabled for the tables

  • Object Metadata
  • Add-in
  • Debugger Breakpoint
  • NAV App
  • NAV App Object Metadata
  • NAV App Tenant App
No alt text provided for this image

For Business Central change tracking is enabled for the tables

  • Published Application
  • Installed Application
  • Inplace Installed Application
No alt text provided for this image

And we can see tons of queries like

Dynamics NAV 2018

SELECT DISTINCT CT.SYS_CHANGE_VERSION, 0[Change Type], CT.SYS_CHANGE_OPERATION, CT.[Object Type], CT.[Object ID] , '00000000-0000-0000-0000-000000000000'[App Package ID], ''[Tenant ID]
FROM CHANGETABLE(CHANGES [dbo].[Object Metadata], @maxChangeTrackingNumberObjectMetadata) CT
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 1[Change Type], CT.SYS_CHANGE_OPERATION, 0[Object Type], 0[Object ID], '00000000-0000-0000-0000-000000000000'[App Package ID], ''[Tenant ID] 
				FROM CHANGETABLE(CHANGES [Debugger Breakpoint], @maxChangeTrackingNumberDebuggerBreakpoint) CT
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 2[Change Type], CT.SYS_CHANGE_OPERATION, 0[Object Type], 0[Object ID], '00000000-0000-0000-0000-000000000000'[App Package ID], ''[Tenant ID] 
			   FROM CHANGETABLE(CHANGES [dbo].[Add-in], @maxChangeTrackingNumberAddIn) CT WHERE CT.SYS_CHANGE_OPERATION = 'U'
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 3[Change Type], CT.SYS_CHANGE_OPERATION, CT.[Object Type], CT.[Object ID], CT.[App Package ID], ''[Tenant ID] 
FROM CHANGETABLE(CHANGES [dbo].[NAV App Object Metadata], @maxChangeTrackingNumberNavAppObjectMetadata) CT
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 4[Change Type], CT.SYS_CHANGE_OPERATION, 0[Object Type], 0[Object ID], CT.[App Package ID], CT.[Tenant ID] 
FROM CHANGETABLE(CHANGES [dbo].[NAV App Tenant App], @maxChangeTrackingNumberNavAppTenantApp) CT
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 5[Change Type], CT.SYS_CHANGE_OPERATION, 0[Object Type], 0[Object ID], CT.[Package ID], ''[Tenant ID] 
FROM CHANGETABLE(CHANGES [dbo].[NAV App], @maxChangeTrackingNumberNavApp) CT 

Business Central

SELECT DISTINCT CT.SYS_CHANGE_VERSION, 3[Change Type], CT.SYS_CHANGE_OPERATION, CT.[Object Type], CT.[Object ID], CT.[App Package ID], ''[Tenant ID]
FROM CHANGETABLE(CHANGES [dbo].[NAV App Object Metadata], @maxChangeTrackingNumberNavAppObjectMetadata) CT
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 4[Change Type], CT.SYS_CHANGE_OPERATION, 0[Object Type], 0[Object ID], CT.[App Package ID], CT.[Tenant ID] 
FROM CHANGETABLE(CHANGES [dbo].[NAV App Tenant App], @maxChangeTrackingNumberNavAppTenantApp) CT
UNION ALL
SELECT DISTINCT CT.SYS_CHANGE_VERSION, 5[Change Type], CT.SYS_CHANGE_OPERATION, 0[Object Type], 0[Object ID], CT.[Package ID], ''[Tenant ID] 
FROM CHANGETABLE(CHANGES [dbo].[NAV App], @maxChangeTrackingNumberNavApp) CT 

So, change tracking is actively used to track modification in metadata (tables, pages, codeunits).

The question arises why then we can not use it for our own purposes. Of course, this is only available On-Premise or Azure SQL. Perhaps in the future, Microsoft will give us access to configure change tracking for tables we need.

Change Tracking and Snapshot Isolation

To obtain consistent and correct results Microsoft recommends

use snapshot isolation

Change tracking has been designed to work well with snapshot isolation. Snapshot isolation must be enabled for the database. All the steps that are required to obtain changes must be included inside a snapshot transaction. This will ensure that all changes that are made to data while obtaining changes won’t be visible to the queries inside the snapshot transaction.

You can read more about snapshot isolation here.

The use of snapshot isolation in dynamics NAV/Business Central has been discussed since the appearance of Dynamics NAV 2018. I think this topic deserves a separate article in which I will try to describe in detail SQL Isolation Level and how we can use it in Business Central to improve performance.

Summary

Due to its lightness, the change tracker allows us to effectively use it not only to synchronize data with data warehouse, but also in integration with 3rd party system such as E-commerce or B2B system.

<< Previous

Business Central, Performance and Data Warehouse. Part I.

Next >>

To be continued…