Removing a SCD Type 2 Loader. Alternate way to generate surrogate PK?

[TurnTheBacon]

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.

[Patrick · Accepted solution]

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]

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]

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]

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.