BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Allan_dk
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Allan_dk
Quartz | Level 8

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

13 REPLIES 13
jakarman
Barite | Level 11

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 --<-----
Patrick
Opal | Level 21

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
Barite | Level 11

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 --<-----
Patrick
Opal | Level 21

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?

Allan_dk
Quartz | Level 8

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;

Allan_dk
Quartz | Level 8
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.

Patrick
Opal | Level 21

"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

jakarman
Barite | Level 11

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 --<-----
Allan_dk
Quartz | Level 8

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
Quartz | Level 8

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
Community Manager

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

Anna


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

LinusH
Tourmaline | Level 20

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

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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