topic SAS DI SCD Type2 Loader Generated Key tab in SAS Data Management

SAS DI SCD Type2 Loader Generated Key tab

JJP1 — Thu, 14 Nov 2019 07:48:23 GMT

Hi ,
Iam trying to create a job using SCD type 2 loader to handle historic data.
So i understood from documentation that business key tab in SCD type 2 transformation is where i need to provide the column
which can be used to compare the rows between source and target to detect changes and maintain history.
Would you please suggest any LIVE scenario on Generated Key has to be defined or not really needed when i want to create the datasets that should store historical data .

please suggest when Is it really needed or not really needed when we want to create historic dataset ?

Re: SAS DI SCD Type2 Loader Generated Key tab

JosvanderVelden — Thu, 14 Nov 2019 12:00:11 GMT

Have you seen this thread: https://communities.sas.com/t5/SAS-Data-Management/How-do-I-set-up-the-SCD-Type-2-Loader-to-capture-out-serviced/td-p/460045

Re: SAS DI SCD Type2 Loader Generated Key tab

Patrick — Thu, 14 Nov 2019 12:59:51 GMT

@JJP1

If you're loading an SCD2 table you will need a generated key. This can either be a unique key or a retained key.

A unique key is unique per row, a retained key is unique per business key (so kind-off a group key which only becomes a primary key if you also add the valid_from_dttm variable).

You want such a generated key so you don't have to replicate the full business key (which can be a composite key comprised of multiple lengthy variables) to related tables (for linking records). You rather want to just use the generated key as foreign key in related tables.

Does that make sense to you?

Re: SAS DI SCD Type2 Loader Generated Key tab

JJP1 — Tue, 19 Nov 2019 06:36:17 GMT

Yes Thanks @Patrick .