Incremental Loading is the process of gradually loading the transactional data into your data warehouse and staging databases. This allows us to have faster loading times as only the most recent data is loaded. This can be useful in performing scheduled executions in less amount of time by executing the most recent data only. The standard execution process involves truncating all the tables and fields in staging database and data warehouse first, then the complete data is subsequently loaded from the datasource. This process is commonly known as Full Load.
During the Incremental Loading process, Jet Data Manager determines the fields inside different tables to detect the amount of data that will be moved over. Truncation is disabled in order to preserve the original data. This process decreases the execution time as Jet Data Manager only transfers the newly added records from data source into staging and data warehouse tables. The amount of time required to transfer data actually determines whether or not incremental loading should be applied. Normally, the tables on which incremental loading is applied are larger transaction tables containing huge volumes of inventory and general ledger transactions. We will use G/L Entry table from Dynamics NAV as an example in this document, but the concepts are universal and apply to every data source.
In order to execute incremental loading properly, a primary key must be defined inside a table. A primary key can be defined after you right-click the field name and select Include in Primary Key. A table can have more than one field as primary key.
In order to identify the records which have been added or changed since the last incremental load, you need to check the box or boxes of the respective fields. These fields are generated by the system and incremented sequentially after the new records are added.
The timestamp field is regarded as the best selection for Dynamics NAV. The DEX_ROW_ID and Modified_Datetime are generally regarded as the best selection for Dynamics GP and Dynamics AX respectively. These fields are automatically incremented when new records are added.
If the table that is set up for Incremental Loading is also a source for lookups on other tables, it is necessary to disable automatic index creation. For more information, please read Disabling Automatic Indexing by Insightsoftware.
Select Only modified tables and views to deploy and execute modified data instead of complete business unit.
Click the Start button to initiate the first full load of the tables with source based incremental loading now enabled.
Jet Data Manager 2017 and higher offers setting up incremental load for any number of tables in a wizard-style interface. This interface reduces the time for setting up incremental loading for multiple tables. To make use of the wizard, right-click on the applicable data source, go to Automate, and select Set Up Incremental Load:
Now you can set up incremental loading for any number of tables, select primary key fields and set our data selection rules easily.
You will notice that a series of system generated fields have been added at the bottom of the table.
Select the DW_TimeStamp field that we previously added to the table.
There are two ways. The first one is to identify the primary key for that particular table in the data source, bring that field up into the data warehouse, and then set it up as the primary key. The second way is to select a field that already exists in the table. If there are more than one field that will result in a unique ID, then we should select these fields as a primary key. Check out the article on what Data Selection Rules are and how to create them.
To set a field as primary key in the data warehouse, you can simply right-click on the field and choose Include in Primary Key.
The field will have a little key on it. Then you can Deploy and Execute without an error.
Select Only modified tables and views to deploy and execute modified data instead of complete data warehouse.
Click Start.
