Enterprise Guide - Slowly changing dimension?

[RB1Kenobi]

Hi

I'm creating a master data table which will then be updated daily to add, update, and remove records.    Has anyone achieved slowly changing dimension type 2 load approach using Enterpise Guide?

Thanks!

[Patrick · Accepted solution]

@RB1Kenobi 

With SCD2 you never remove records but you expire them.

NEW records:   INSERT

CHANGED records: UPDATE of end date column of latest already existing version, INSERT of new version

DELETE:    UPDATE of end date column of latest already existing version

SAS DI Studio (the SAS ETL tool) provides an out of the box transformation for loading SCD2. 

With the SAS EG client: You need to write your own SAS code for this. If you don't get the deltas from source (Inserts, Updates and Deletes) then you need first to work this out via code by comparing source with target, After that it's either an INSERT for new and changed records (using PROC APPEND) and an UPDATE for expired (deleted) or changed records (SQL UPDATE). 

Alternatively coding using a SAS Datastep MODIFY statement is also an option.

[RB1Kenobi]

Thank you for your assistance. The end result was as per your reply.