DI-studio : SCD Type 2 - close out records

[Allan_dk]

In the transformations propeties/options it is possible to activate

Close out records not in source table: For records not in source table, assume they are to be closed out in target table.

When a record is missing in the source data the transformation set the validtime_end til %SYSFUNC(DATETIME())  and that is what I expect.

Before

ID	vt_start	vt_end	Value
1	01jan2014:0:0:0	15mar2014:0:0:0	A
1	15mar2014:0:0:0	31dec9999:0:0:0	B

After the record is found missing

ID	vt_start	vt_end	Value
1	01jan2014:0:0:0	15mar2014:0:0:0	A
1	15mar2014:0:0:0	17sep2014:0:0:0	B

But the next day the transformation runs it apparently updates the record in the target table.

ID	vt_start	vt_end	Value
1	01jan2014:0:0:0	15mar2014:0:0:0	A
1	15mar2014:0:0:0	18sep2014:0:0:0	B

I lose track of when the record was detected missing.

What to do?

DI-studio v4.6

[jakarman]

Not only the date of your missing has changed but also the vt_end date of that other record. With SCD-type historical records are expected to be archived not changed.

The problem of that missing date should be reviewed. Either it is some data quality is that needs to be corrected or it is a not closed record that gets updated.

In both cases imputation by the execution moment is not the best option. In the first case there will be a more logical end-date as period end in the latter case it must be recognized as getting to be updated.    

[Patrick]

This is about the DIS SCD2 standard loader so the question is "why" is the loader behaving this way. Is it a bug or something which could be controlled by setting some of the options differently?

[jakarman]

When you know what should be done and what is wanted, you can give those answers yourself. The question left would be:

- I am missing an option somebody an alternative?
- I am seeing this is behaving this way, In my opinion that is not correct am I missing something?
That is more thinking in conceptual lines than just doing some mouse clicks and something seeing unexpected.       

[Patrick]

I have tried with DIS 4.9 to replicate what you describe but didn't have this issue.

What kind of surprises me is that your end dates "vt_end" has "00:00:00" and not some real times in it given that the formula is: ETLS_CLSDATE = DATETIME();

Do you have an explanation for this?

Can you please post a screen shot of "SCD Type 2 Properties/Options/SCD *".

Are there any columns under SCD1? - I've tried this one but still couldn't replicate what you describe.

The relevant loader generated code I get is as below. Does this look the same in your version of DIS?

      /* Close out any records that are in the xref table but not in the source.  */

      else if inXref and not inSort then

        do;

            ETLS_CLSDATE = DATETIME();

            Load_Dttm = input("&etls_loadtime", datetime20.);

            ETLS_LOADTIME = input("&etls_loadtime", datetime20.);

            output work.etls_close;

        end;

Oh, I just realized you're writing "..validtime_end til %SYSFUNC(DATETIME())". That should be the expression for the start datetime not the end datetime.

Just to make sure:

You're using columns vt_start and vt_end under tab "Change Tracking" and it looks as below?

Date	Column Name	Expression
Beginning Date	vt_start	%sysfunc(datetime())
End Date	vt_end	'01Jan5999:00:00:00'DT

If not: Could you please post a screenshot how things actually look under this tab?

[Allan_dk]

vt_end has 0:0:0 because it is just testdata.

There are no columns under SCD1

    /* Close out any records that are in the xref table but not in the source.  */

      else if inXref and not inSort then

         do;        

            ETLS_CLSDATE = DATETIME();           

            output work.etls_close;           

         end;        

      run;

[Allan_dk]

Beginning Date	vt_start	%sysfunc(datetime())
End Date	vt_end	'01Jan9999:00:00:00'DT

Except from that we use 9999 instead of 5999 you are correct.

[Patrick]

"vt_end has 0:0:0 because it is just testdata."

What do you mean by that? It's data you've made up when posting your question or it's test data in your source?

As you're having an issue with the date alignment please post an example with real datetime values.

Are you - by any chance - mapping vt_start and vt_end from source?

It needs to look like this (valid_from_dttm and valid_to_dttm are your vt_ variables).

[Allan_dk]

Here is the real data and some more dokumentation.

https://docs.google.com/document/d/1nuuzTusBcz1QgmVn_J_6A8Gc1vkeU8gdkj-lL9NqasI/edit?usp=sharing

[jakarman]

Did you see you have put all colums as being subject to "detect changes" including beging and end dates?

It is not logical to put all columns there, only those that should be checked on that content must be there.

See figure 4 in http://www2.sas.com/proceedings/forum2007/100-2007.pdf    http://www2.sas.com/proceedings/sugi31/096-31.pdf

[Allan_dk]

Yes I know that i have all columns marked for detect changes. Including vtend and vtbegin.

In this case it makes sense - including vtbegin and vtend. These are just ordinary variables that I would like to keep track on.

[Allan_dk · Accepted solution]

Well, SAS Support, was able to help me.

"if you want only key values close out the first time encountered when using the option "Close out records not in source table", you would need to add the "User current indicator" under Change Tracking in addition to "User beginning and end dates". Adding the current indicator method will prevent closed keys from being retrieved during the subsequent executions of the SCD Type 2 process."

[AnnaBrown]

Excellent, Allan_dk! Thanks for coming back to this thread with the solution.

Anna

[LinusH]

Agree with Anna.

But crazy solution. Does anybody want they closed out records being closed out all the time? You shouldn't need the current indicator...

Now that we are on the subject (sorry for hi-jacking the thread - should never happen again:smileyblush:): shouldn't the close date reflect the expression of the "Beginning Date" on the Change Tracking tab?

Like it is now, if we use a centralized date (like a batch reference date) we could have mixed types of close dates (valid to dt), one using supplied batch date, and others using the computer clock. The current work-around is to pre-join the transactions with the target table and close out the record "manually".