topic Re: Enterprise Guide - Slowly changing dimension? in New SAS User

Enterprise Guide - Slowly changing dimension?

RB1Kenobi — Tue, 14 May 2019 13:49:22 GMT

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!

Re: Enterprise Guide - Slowly changing dimension?

Patrick — Fri, 17 May 2019 07:08:49 GMT

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

Re: Enterprise Guide - Slowly changing dimension?

RB1Kenobi — Wed, 29 Apr 2020 06:48:36 GMT

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