topic Re: DI-studio : SCD Type 2 - close out records in SAS Data Management

DI-studio : SCD Type 2 - close out records

Allan_dk — Thu, 18 Sep 2014 09:05:21 GMT

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

Re: DI-studio : SCD Type 2 - close out records

jakarman — Thu, 18 Sep 2014 10:58:15 GMT

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.

Re: DI-studio : SCD Type 2 - close out records

Patrick — Thu, 18 Sep 2014 11:11:47 GMT

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?

Re: DI-studio : SCD Type 2 - close out records

Patrick — Thu, 18 Sep 2014 11:14:25 GMT

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?

Re: DI-studio : SCD Type 2 - close out records

jakarman — Thu, 18 Sep 2014 11:27:03 GMT

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.

Re: DI-studio : SCD Type 2 - close out records

Allan_dk — Thu, 18 Sep 2014 11:34:47 GMT

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;

Re: DI-studio : SCD Type 2 - close out records

Allan_dk — Thu, 18 Sep 2014 11:37:45 GMT

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.

Re: DI-studio : SCD Type 2 - close out records

Patrick — Thu, 18 Sep 2014 22:08:09 GMT

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

Re: DI-studio : SCD Type 2 - close out records

Allan_dk — Mon, 22 Sep 2014 10:56:35 GMT

Here is the real data and some more dokumentation.

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

Re: DI-studio : SCD Type 2 - close out records

jakarman — Thu, 25 Sep 2014 06:24:50 GMT

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

Re: DI-studio : SCD Type 2 - close out records

Allan_dk — Thu, 25 Sep 2014 06:33:44 GMT

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.

Re: DI-studio : SCD Type 2 - close out records

Allan_dk — Wed, 08 Oct 2014 17:48:21 GMT

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

Re: DI-studio : SCD Type 2 - close out records

AnnaBrown — Wed, 08 Oct 2014 19:10:57 GMT

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

Anna

Re: DI-studio : SCD Type 2 - close out records

LinusH — Wed, 08 Oct 2014 20:16:44 GMT

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