SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Frequent Contributor
Posts: 97

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

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.

Respected Advisor
Posts: 4,779

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

[ Edited ]
Posted in reply to imdickson

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

Ask a Question
Discussion stats
  • 1 reply
  • 154 views
  • 2 likes
  • 2 in conversation