Follow

Incremental load from staging into the data warehouse doesn't work with Microsoft Dynamics NAV data

Symptoms

Incremental loading works when importing data from a NAV data source into staging, but produces unexpected results when using incremental loading into the data warehouse.  Data may be missing after a full load, or the incremental load may not work properly.

Error Message

None

Cause

This problem happens if you use the NAV "timestamp" field for incremental loading into the data warehouse.  In most source systems, the time stamp or modified date field for a  record is some kind of date/time data type, but NAV uses a varbinary(8) data type instead.

TX DWA's NAV adapter has special logic that can compare on the NAV timestamp field, but incremental loading requires a date, datetime, or numeric incremental value everywhere else.  This means that using timestamp as your incremental field will work from your source into staging, but misbehave if used as an incremental field when configuring loads into a data warehouse.

Solution

As a best practice, we suggest pulling the staging table's DW_Timestamp field into the data warehouse and using that as the incremental field instead of relying on a source database incremental field.  This solution gives you much more flexibility and control over incremental loading between staging and the data warehouse, and also resolves this issue.

Was this article helpful?
2 out of 2 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.