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

I've been asked to remove the "SCD Type 2 Loader" transformation from a job in DI Studio. The reason is that it is superfluous, because everything can be handled as type 1. Removing the SCD Type 2 Loader transformation seems straight-forward, however it appears that it's also responsible for generating the surrogate PK used in the output table. What is the best alternative for generating a surrogate PK once the SCD Type 2 Loader transformation has been removed? And also, is there anything else I should bear in mind when removing a SCD Type 2 Loader transformation from a job?

Thanks for your attention. Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

You will have to decide what to do with all the columns maintained by the Type 2 loader, so the surrogate key, valid_from_dttm, valid_to_dttm, change_current_ind, version (whatever you are using). You will have to check if there are other jobs using these variables in any way.

That the Table Loader doesn't have an option to maintain a surrogate key is the thing I miss the most in this transformation. There is the "Surrogate Key Generator" which you can use to add keys in a pre-process before you load data into target using the Table Loader. I personally don't like this transformation too much (just look at the code it generates) and avoid using it. I'm even no more sure if it's able to also deal with updates so not generating new keys for update records but populating the source with existing ones from target.

If I need a surrogate key generated then I tend to use the Type 2 loader even if all columns are type 1 - or I fully "hand-code" the surrogate key generation.

If using a database then an alternative approach of maintaining a surrogate key could be to have an "insert trigger" which adds the key on the database side - and you wouldn't have to do anything on the SAS side. You might need to talk with the DBA for your database to have something like this added for your database target table.

And last but not least: In case you're getting full loads and you want to expire records which are in target but not in source then you best use the Type 2 loader even if all columns are type 1.

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

You will have to decide what to do with all the columns maintained by the Type 2 loader, so the surrogate key, valid_from_dttm, valid_to_dttm, change_current_ind, version (whatever you are using). You will have to check if there are other jobs using these variables in any way.

That the Table Loader doesn't have an option to maintain a surrogate key is the thing I miss the most in this transformation. There is the "Surrogate Key Generator" which you can use to add keys in a pre-process before you load data into target using the Table Loader. I personally don't like this transformation too much (just look at the code it generates) and avoid using it. I'm even no more sure if it's able to also deal with updates so not generating new keys for update records but populating the source with existing ones from target.

If I need a surrogate key generated then I tend to use the Type 2 loader even if all columns are type 1 - or I fully "hand-code" the surrogate key generation.

If using a database then an alternative approach of maintaining a surrogate key could be to have an "insert trigger" which adds the key on the database side - and you wouldn't have to do anything on the SAS side. You might need to talk with the DBA for your database to have something like this added for your database target table.

And last but not least: In case you're getting full loads and you want to expire records which are in target but not in source then you best use the Type 2 loader even if all columns are type 1.

shivas
Pyrite | Level 9

Hi,

You can try using Surrogate Key generation transformation or key effective date transformation instead of SCD type .

or if you do not want to use any transformation for Surrogate key generation then you try using monotonic() function in the expression builder (not supported by SAS)

Thanks,

Shiva

Patrick
Opal | Level 21

I've just read this blog from Chris Hemedinger. Using uuidgen() could be a true alternative for creating keys. No more max_key lookups and the like - just generate new keys. Quite tempting I must say.

LinusH
Tourmaline | Level 20

From SAS 9.3 (DIS 4.3) there is the Type I Loader transform, that probably does what you are looking for, with no need for work-arounds.

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
  • 4 replies
  • 1464 views
  • 3 likes
  • 4 in conversation