BookmarkSubscribeRSS Feed
AbdulGhani
Calcite | Level 5

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.

12 REPLIES 12
LinusH
Tourmaline | Level 20
Please share screenshots on the tabs as well.
Data never sleeps
AbdulGhani
Calcite | Level 5

Please find the screenshots attached to this post. 

Erik_Zencos
Obsidian | Level 7

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

 

SAS_DIStudio.png

 

Regards,

Erik Meyer

 

 

 

AbdulGhani
Calcite | Level 5

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. 

 

Erik_Zencos
Obsidian | Level 7
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).
AbdulGhani
Calcite | Level 5

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 ? 

LinusH
Tourmaline | Level 20
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
Erik_Zencos
Obsidian | Level 7

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.

AbdulGhani
Calcite | Level 5

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. 

AbdulGhani
Calcite | Level 5

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

Patrick
Opal | Level 21

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

 

LinusH
Tourmaline | Level 20
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

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
  • 12 replies
  • 4855 views
  • 0 likes
  • 4 in conversation