SamRock on 10 Jul 2019 20:54:46
Along with Date Time stamp, there needs to be option to select a Unique/primary Key column too. Else we are only appending data leading to duplication, not checking if a Key was already inserted in the past.
More Explanation:
Currently incremental refresh works for the data that was inserted based on DateTime stamp, lets say last 10 days. It does not take into consideration the Primary Key that might have been inserted in the past.
Eg: Lets say a "Sales Order" was created 3 months ago with a Status "Open", but only today its "Status" was changed from "Open" to "Shipped". Since I am only looking rows modified in last 10 days data (for incremental refresh), a new Sales Order row gets inserted into my Dataset causing my "Sales Order" table to be duplicated.
How can we avoid this? Ideally along with the Datetime stamp, there needs to be a option where we specify the Unique Key column too. If incremental refresh contains any of the Unique Keys loaded in the past, they need to be deleted too and reinserted.
Dataset will look like this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sales Order | Status------| LastModifiedDateTime
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SO0000001 | Open | 10-Dec-2018 12:00:00AM
SO0000001 | Shipped | 10-Jul-201812:00:00AM