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

How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Reply
Occasional Contributor
Posts: 6

How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

[ Edited ]

I am creating an ETL job in SAS integration Studio 4.903 using the SCD-2 Loader. The problem i am facing is with the change tracking. 

The business key is set to the RK. The amount fields are set with a bunch of fields in the detect changes tab. The job does detect changes and adds a new row whenever a change occurs. How ever its not changing the end date on the outdated record. 

 

The example can further be understood in the example attached. Need some advice on how to proceed forward with this. All i want to do is make sure that the end date changes for the outdated records as changes occur. 

 

Regards,

Abdul Ghani Mohammed.

Super User
Posts: 5,849

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Posted in reply to AbdulGhani
Please share screenshots on the tabs as well.
Data never sleeps
Occasional Contributor
Posts: 6

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Posted in reply to AbdulGhani

Please find the screenshots attached to this post. 

Occasional Contributor
Posts: 14

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Posted in reply to AbdulGhani

Hi, if I remember correctly you need to set the following option to yes:

 

SAS_DIStudio.png

 

Regards,

Erik Meyer

 

 

 

Occasional Contributor
Posts: 6

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Selecting that option would close out records that are not in the source table right.

But in this case the record exists (ACCOUNT_RK) but has undergone some changes to it. 

 

Occasional Contributor
Posts: 14

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Posted in reply to AbdulGhani
I don't have access to test this myself, but I do believe this will work for you. The record has changed and the old can be considered to not exist in the source (even if the key is still the same).
Occasional Contributor
Posts: 6

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

I did try the option out and re-ran the job. However the valid_to_DTTM remained the same for the record. 

Do you have any other suggestions that I can try out ? 

Super User
Posts: 5,849

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Posted in reply to AbdulGhani
Look ok, but I'm a bit confused about having a datetime value for from date, and a date value for to date...?
Also, share the mapping tab.
Data never sleeps
Occasional Contributor
Posts: 14

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Yes, please share the mapping tab. Also, using datetime values is fine, I always use that since we could have several updates within a day. Another point, use current indicator (or version number), so sas can compare the business key of the source row to the business keys of the current row in the table. Your business key should probably also have a unique definition, since you are using retained keys you should add your end date to the business key tab. Try this and let us know how it works.

Occasional Contributor
Posts: 6

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

I just tried to add the end date (Valid_to_DTTM) to the business key tab as a part of the indicator. Turns out we cannot use a field for 2 different purposes (EX: Change Tracking+ Business Key Identification). For this specific job i am not generating any keys. So the only way to track the changes in my target table is through the valid from and valid to dates. Please feel free to share any other suggestions for troubleshooting. 

Occasional Contributor
Posts: 6

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Please find the mapping screenshot attached to this reply. The mapped source columns are identical to the target columns. Also the valid_from_DTTM and the valid_to_DTTM fields are system generated which is why won't see any source columns mapped to those 2 target columns. 

 

Let me know if this helps

Respected Advisor
Posts: 4,665

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

[ Edited ]
Posted in reply to AbdulGhani

@AbdulGhani

I can't see anything wrong in your SCD2 configuration.

SCD2 in your DIS version should do the job. I've set up a sample DIS job which I'm attaching here. The job is "self contained" and the only thing you need to do is assign a library to the target table during import (Just remove the .zip extension from the file name. That was necessary in the filename for attaching the file here).

 

As @LinusH already mentioned it's weird that your valid_to_dttm column shows a date and not a datetime value. Is that just the format you're using or is there something else going on?

Given that you've got an issue with the date alignments that's eventually the place where you need to fully investigate and explain why we only see a date value.

 

On a side note:

Your target table is called Financial_Account_Chng. I believe the purpose of this table is to hold account information which changes rather frequently (so not really slowly). For not blowing up table Financial_Account. such account attributes get stored in the _chng table.

If so then the _chng table needs to be narrow and you have to reduce the number of columns to the minimum required (only the columns which change too frequently for storing in Financial_Account). You also shouldn't have any unmapped business columns in the _chng table or you're just asking for performance issues (as the table will quickly grow).

If that's the SAS DDS you're loading into then from my experience the _chng tables are the ones where you'll be facing performance challenges. It might be worth to consider to create a reusable custom transformation which does the job more efficiently than the SCD2 loader. Happy to elaborate further if that's of interest to you.

 

Attachment
Super User
Posts: 5,849

Re: How do I set up the SCD Type 2 Loader to capture out-serviced dates as changes occur

Posted in reply to AbdulGhani
Ok I just wanted to check that haven't had your date columns mapped.
Perhaps you are in the middle of the development process, but you should try to keep columns in target with no mapping to a minimum, and also just try to keep only necessary columns in the preceeding step.
Again your datetime formats looks weird.
There is a debug setting in the loader that will many of the temporary tables not be deleted during execution.
Then execute the generated code in EG - which easier to use when browsing data sets that isn't represented by it's own transformation.
So look into all data set and match those to the actual steps executed.
Data never sleeps
Ask a Question
Discussion stats
  • 12 replies
  • 226 views
  • 0 likes
  • 4 in conversation