Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)

imdickson — Thu, 05 Jul 2018 10:46:40 GMT

Hello everyone.

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:
Electric_Name

Pan_CD

Detech changes tab:

Electric_Tag

P/S: Electric_name=Electric_tag but with different variable name

I need your advice to fulfill the mentioned criteria.

Re: Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)

Patrick — Sat, 07 Jul 2018 05:20:19 GMT

@imdickson

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).

topic Re: Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS) in SAS Data Management

Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)

Re: Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)