Scd type 2 close out

[Srikanth713]

I have a problem with closeout records and as records are closed in Target table since records are not coming from source and when ever the closed record id again coming after few days it is creating the new RK value for same record and it is not picking the same RK value from previous.what can I do for picking same RK and open the record.

[Patrick]

The SCD2 loader will use the same generated key for the same business key if you've ticked the box for the key to be a retained key.

[Srikanth713]

Yes I have given the retain key for account id as business key but it's creating a new RK value of same id for close records when it is reopend

[Patrick]

@Srikanth713 

So you're saying that:

Day 1: source with a new business key X - inserts a row in target with a new RK

Day 2: source with same business key X but a change in another column- expires record from day1 in target, inserts new row with same RK

Day 3: source doesn't have row with business key X - expires current record in target

Day 4: source with business key X - inserts row but creates a new value for RK

Is that what you observe? Do you really have such a case in your target table? And are you 100% sure that the business key is the same (inclusive some invisible "garbage" characters due to DQ issues)?

And just to ask: 

Do you also have a change current indicator column? (there is an issue with the SCD2 loader if you don't).

[Srikanth713]

Day1: suppose I am loading 100 records source and Target contains 100 records
Day:2 I am loading empty source of next rundate so all the records in the target are closed
Day:now i am loading those all the 100 records of same business id from source so now it is creating new RK(reference key) from 101 to 200 and now here I need to pickup the from old RK only not new as id is same

Thanks

[Patrick]

@Srikanth713 

What I'd like to know and why my sample has 4 days: Do you have any cases in your target table where you've got multiple rows with the same RK and business key? You're currently tying the issue to re-opening records. I'd like to know if RK population works at all for you.

Also: Do you have a change current indicator column defined? This is a required column due to a bug in the SCD2 generated code.

[Srikanth713]

Yeah I have used the current indicator also but still it's giving me the same result and I need to know is whether the same RK is picked or not when the closed record is reopened.

Thank you

[Patrick]

@Srikanth713 

I've used the SCD2 loader already in many DIS jobs and version and it works as it should based on how you configure it. The only specialty I know off if is the need for a change current indicator column. This also includes the RK; I can't recall ever having had the problem you describe.

If your target table is not a SAS but a database table then you will have a bit more work to not only set-up functionally correct but also performing code.

If you're really convinced that you've configured everything correctly and also executed valid test cases then I believe best next option is to investigate the SAS Log and the DIS generated SAS code to figure out where the logic is incorrect. 

May be also post screenshots here showing us how you've actually configured the transformation.

[Srikanth713]

[Patrick]

@Srikanth713 

The expression for your valid_from_dttm column can't be right. It needs to be something like datetime() or any other expression that returns a constant SAS datetime value. Using a variable from your table is certainly the wrong thing to do.

I can't tell 100% but it's likely that fixing above will also resolve the RK issue. You need to fix and retest first.

...and I would assume your current target table is "messed up" so best start retesting with an empty target table.

[Srikanth713]

I have given the run date in the mapping to Column(valid_from_dttm)

 Expression("&RPT_DTTM."DT).

To be frank every thing is fine and loading properly as I required but the issue is creating new RK for closed out records when reopened

I need to know why and how to solve it

And sorry for late reply

Thank you

[Patrick · Accepted solution]

@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?

[LinusH]

Instead of trying to fix/trouble shoot this, you could perhaps go around it instead.

This by changing the data model slightly.

I'm usually quite hesistant on end dating the last record for a key. I think the validation date interval shall describe the validility of the data record, not the validility of the key itself.

To describe if a key is still active or not, use status code and/or status date columns instead.