@Srikanth713
Sorry for not believing you.
I've created now a DIS job (with version 4.903) to prove to you that things work as they should only to proof to myself that they don't.
I can replicate what you describe. I couldn't find a resolution within the SCD2 loader itself.
I'm not sure if that's an issue which has been introduced with one of the newer DIS versions because I was really in the believe I've used the retained key generation in the past and it worked. ....but may be I'm just confused.
If you need a quick solution then I can't think of anything else than either a pre- or post process which maintains the key (you can eventually use the surrogate key generator transformation for it), or use your business column if numeric and just map it into the retained key column, or define the retained key as being character 32 and create a digest value using the business key, ...or drop the retained key at all from your target table.
Looking into the DIS generated code I believe the issue is this code bit.
/* Sort target table records before creating the temporary cross reference */
/* table: */
proc sort data = mywork.scd2_multiday_target(where = (change_current_ind = 'Y'))
out = work.etls_sortedxref(keep=retained_key busikey_1 busikey_2 VALID_FROM_DTTM VALID_TO_DTTM
change_current_ind change_tracked_var);
by busikey_1 busikey_2;
run;
It always annoyed me that change_current_ind gets set to 'N' also when only expiring a record without adding a new changed record.
This way it's totally redundant information to all records with a valid_to_dttm in the far future.
But now because it's implemented the way it is below selection won't include expired records even if they are the most current change.
proc sort data = mywork.scd2_multiday_target(where = (change_current_ind = 'Y'))
out = work.etls_sortedxref....
Table etls_sortedxref is then used to determine if a source record is new or a change to an already loaded business key. And because the expired record is not in etls_sortedxref the logic determines that it's a new business key and though creates a new value for the retained key.
Do you want to raise this with SAS TechSupport or shalll I do it?
... View more