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

DI-studio : SCD Type 2 - close out records

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

DI-studio : SCD Type 2 - close out records

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

IDvt_startvt_endValue
101jan2014:0:0:015mar2014:0:0:0A
115mar2014:0:0:031dec9999:0:0:0B

After the record is found missing

IDvt_startvt_endValue
101jan2014:0:0:015mar2014:0:0:0A
115mar2014:0:0:017sep2014:0:0:0B

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

IDvt_startvt_endValue
101jan2014:0:0:015mar2014:0:0:0A
115mar2014:0:0:018sep2014:0:0:0B

I lose track of when the record was detected missing.

What to do?

DI-studio v4.6


Accepted Solutions
Solution
‎10-08-2014 01:48 PM
Contributor
Posts: 40

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

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

View solution in original post


All Replies
Trusted Advisor
Posts: 3,212

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

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.    

---->-- ja karman --<-----
Respected Advisor
Posts: 4,173

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

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?

Trusted Advisor
Posts: 3,212

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

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.       

---->-- ja karman --<-----
Respected Advisor
Posts: 4,173

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

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?

DateColumn NameExpression
Beginning Datevt_start%sysfunc(datetime())
End Datevt_end'01Jan5999:00:00:00'DT

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

Contributor
Posts: 40

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

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

There are no columns under SCD1

scd2.PNG

    /* 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;

Contributor
Posts: 40

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

Beginning Datevt_start%sysfunc(datetime())
End Datevt_end'01Jan9999:00:00:00'DT

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

Respected Advisor
Posts: 4,173

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

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

Capture.PNG

Contributor
Posts: 40

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

Trusted Advisor
Posts: 3,212

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

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

---->-- ja karman --<-----
Contributor
Posts: 40

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

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.

Solution
‎10-08-2014 01:48 PM
Contributor
Posts: 40

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

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

Community Manager
Posts: 564

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

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

Anna

Super User
Posts: 5,424

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

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:smileyblushSmiley Happy: 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".

Data never sleeps
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1567 views
  • 0 likes
  • 5 in conversation