Overview

Incremental loading is the process of loading all data from the data source the first time that a table is run, and then only needing to bring in records that have been added or modified since the last time that the data refresh happened. This way only a small number of transactions will need to be brought in during the data load instead of having to reload all data every time. This article will cover how to configure incremental loading for tables.

Details

To configure a table for incremental loading, you will first check out the extension associated with the table to be incrementally loaded. Once the extension has been checked out you will navigate to the Transformations section in Data Management within the Cosmos Portal.

Enabling Incremental Loading

Click the table to incrementally load and then expand the Table Configurations pane on the right by mousing over it. Ensure that the checkbox is checked for the Incremental Load setting. This will turn on incremental loading for the table. 

This will incrementally load Business Central tables based on the company and the "System ID" field, which is a field provided by Microsoft that defines each record in the table uniquely. 

Specifying the Key for the Table

If all source tables feeding into a table in the Data Model section have incremental loading enabled, the Data Model table will be incrementally loaded as well. If any of the source tables feeding the Data Model table do not have incremental loading enabled, then the Data Model table will undergo a full load each time the pipeline is run.

For incremental loading to be successful in the reporting database, Cosmos needs to know what the primary key is for the Data Model table. A primary key is a field, or combination of fields, which ensures that every record in the table is unique.

This is used when the data is loaded in the following manner:

• New data is brought in from the data source. This will include any records that have been created or modified since the last time that the data refresh was run
• Cosmos will use the primary key fields specified by the user to know what field(s) make every record unique
  • For any records brought in from the data source where a primary key already exists, an UPDATE operation is done to update the existing record with the new values from the data source
  • For any records brought in from the data source where the primary key doesn't already exist, an INSERT operation is done to insert the new record into the Data Model table

To specify the primary key for the table, check the box in the mappings section for all fields in the Data Model table that are required in order to make the record unique.

In the example above, the "Customer No" field is the only field needed to ensure that every record is unique. If additional fields were required you would check the box in the Key Field column next to each field.