07-05-2018 06:46 AM
I am using SAS Data Integration Studio. Here is the thing.
In one of the job, I am using SCD Type 2.
data source; input ElectricTag Electric_RK PanName PanRK ; datalines;2009-AAA 100 USA 50
2010-AAA 101 USA 50 2011-AAA 102 UK 51 2011-BBB 103 POL 52 2011-CCC 104 UKR 53
The business key is ElectricTag and PanName. When i say Business key, I am referring to the primary key which is the ElectricTag and PanName.
The target table will have 2 additioinal column
1) Valid_from- Capture the datetime now for any new records
2) Valid_to-Capture the datetime now for any expired/obsoleted records
The scenario is, whenever the newer set of data source comes in, for example, a new ElectricTag called 2011-CCD 105 with the same PanName UKR 53, but the new source doesn't have 2011-CCC anymore.
Hence, i would expect the record below
2011-CCC 104 UKR 53
have Valid_to with today's datetime to indicate this record is no more valid for use.
Meanwhile, the record below
2011-CCD 105 UKR 53
should have valid_from with today's datetime to indicate this record is valid for use.
Currently, my settings for SCD Type 2 doesnt work properly. nothing will be changed at the moment. I need your kind advice based on my SCD Type 2 settings below:
Business Key tab:
Detech changes tab:
P/S: Electric_name=Electric_tag but with different variable name
I need your advice to fulfill the mentioned criteria.
07-07-2018 01:19 AM - edited 07-07-2018 01:20 AM
SCD2 change tracking will maintain history for values under change tracking which change within the same business key.
If I understand what you describe right then if the value of Electric_Tag changes the value of Electric_Name changes as well.
Electric_Name is part of your business key so you end up with a new business key and not with a change to an existing business key.
For this reason the SCD2 loader will treat this as a totally new record (insert) without expiring (update) an already existing record (which has a different business key).