05-04-2018 10:13 AM - edited 05-04-2018 10:13 AM
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.
Abdul Ghani Mohammed.
05-07-2018 11:53 AM
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.
05-07-2018 12:22 PM
05-07-2018 02:15 PM
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 ?
05-08-2018 02:05 AM
05-08-2018 09:57 AM
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.
05-08-2018 12:27 PM
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.
05-08-2018 12:14 PM
05-09-2018 09:28 PM - edited 05-09-2018 09:39 PM
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.
05-09-2018 11:27 AM