When you have set up Incremental load on your data sources in the business unit.
Incremental loading on a business unit
You also need to apply it to the DWH to avoid moving the same data back and forth between the two areas.
Set up Incremental load with the Automatic option
If you are mapping a table from a BU table or from another DWH, you can use the automate feature.
You go to the Data Warehouse, right-click, locate the Automate area, expand that, and choose Set up Incremental Load.
In the Wizard, start by selecting all the tables you want to apply incremental load for and add them to the included tables area.
The second step is to set up primary keys, if there aren’t any. Click on Auto Suggest to apply any necessary primary keys.
The third step is choosing the incremental field. Again, you should click on Auto Suggest to make it generate an IncrementalTimeStamp rule based on the DW_TimeStamp field from the source table. This field is the ideal one for this, as it will be updated if the BU table has an update to a row.
The fourth step is to decide if tables will run with hard deletes. Choose any tables you want to have this for, or leave them be.
The fifth step is to decide if tables will run with soft deletes. Choose any tables you want to have this for, or leave them be. If you chose some for Hard Deletes in the previous step, they will not be available here.
The last step is a Review of your setup, so you can see what changes will be applied to the tables you have selected.
You are able to go back to previous steps if you see some setting that needs to be changed; otherwise, once you click Finish, it will apply the changes.
Once you have deployed and executed, the incremental load table will contain the incremental rule.
It is a requirement that the data extraction settings are Incremental Load and not Automatic.
It is preferable to mix Soft Deletes with the Keep field values up-to-date option.
If you are mapping more than one table and set up an Incremental load, it will still create one rule per mapped table. It is a requirement that they also have the DW_TimeStamp field from the source mapped to the IncrementalTimeStamp field that is used for the role. Additionally t that the primary key fields are the same data types.
Set up Incremental load manually on a table in the Data Warehouse
Besides the automatic method, there also is another way to set it up.
You do this by choosing a table without any incremental load setup and then right-clicking on it and choosing Add Incremental Selection Rule.
There is one prerequisite that needs to be done beforehand and that is to add a field to be used for the rule. In the Automatic section this part is generated with the Auto Suggest, but here you will need to apply a field first. It is generally not ideal to choose the same field as what is used for the rule in the BU, as it can sometimes avoid getting updated when there is changes. So I would apply the DW_TimeStamp field from the source and give it a different name.
Then you can set it up by clicking Add Incremental Selection rule, choosing Yes, and selecting the field to be used for incremental load.
If you want to apply hard or soft deletes, you need to do it afterwards, by going into the Table Settings menu and choosing Data Extraction and choosing one of the options.
This is also where you can apply the Keep field values up-to-date option.
