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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

 

 

View solution in original post

2 REPLIES 2
Patrick
Opal | Level 21

@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
Quartz | Level 8
Thank you for your assistance. The end result was as per your reply.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 978 views
  • 0 likes
  • 2 in conversation