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

Hello Team ,

 

Is there any option with DIS  SCD type 2 transformation , to perform  this action only when it meets specific condition

 

ex: update the specific records as in below example 

 

we need to implement SCD type 2 transformation only for the dept =EEE for other department records , we need to keep as is .

am using below configuration

Here the business key is NAME and DEPT (we have unique records)

detect changes in column Score

 

student_key Name dept Score change_ind change_start_dt change_end_dt
1 Student1 EEE 70 Y 09/03/2022 01/01/5000
2 Student2 EEE 50 Y 09/03/2022 01/01/5000
3 Student1 CSC 70 Y 09/03/2022 01/01/5000
             
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisLysholm
SAS Employee

Hello,

 

I am not aware of a specific option within the SCD Type 2 Loader transformation that would satisfy the requirement if I am understanding the problem correctly. However, a potential workaround that you could test for suitability would be to apply a filter to the source (incremental change data) that would result in outputting only those rows where dept=EEE. The pre-filtered source data would then be connected as an input (source) to the SCD Type 2 Loader. The SCD type 1/2 processing should then only produce insert/update rows that are specific to those rows where dept=EEE. 

 

Please not, I have not tested this workaround. I would recommend thoroughly testing this workaround if you would be pursuing it.

 

Cheers.

 

 

View solution in original post

5 REPLIES 5
ChrisLysholm
SAS Employee

Hello,

 

I am not aware of a specific option within the SCD Type 2 Loader transformation that would satisfy the requirement if I am understanding the problem correctly. However, a potential workaround that you could test for suitability would be to apply a filter to the source (incremental change data) that would result in outputting only those rows where dept=EEE. The pre-filtered source data would then be connected as an input (source) to the SCD Type 2 Loader. The SCD type 1/2 processing should then only produce insert/update rows that are specific to those rows where dept=EEE. 

 

Please not, I have not tested this workaround. I would recommend thoroughly testing this workaround if you would be pursuing it.

 

Cheers.

 

 

learn_SAS_23
Quartz | Level 8
Thanks Chris,
It works in that way , if the delta data is filtered for a specific condition here dept=EEE.
and business key in SCD2 transformation business key is NAME and DEPT , only these rows get updated in the target table.
ChrisLysholm
SAS Employee
You are very welcome and I am glad that this solution appears to be working.
I would just reiterate the recommendation for thorough testing of the solution.

Cheers.
learn_SAS_23
Quartz | Level 8
Hello Chris ,

As you said , this failed in testing phase.

Means , the SCD type2 transformation captures the updates of delta and create new records if there is change.
But it closes all other records (change indicator) with out updating the start and end dates , which are not available in delta as shown in below example.

for example :
if the delta has below data
student_key Name dept Score
1 Student1 EEE 40

then the out put of the table result with SCD type 2 is

student_key Name dept Score change_ind change_start_dt change_end_dt
1 Student1 EEE 70 N 09/03/2022 22/03/2022
2 Student2 EEE 50 N 09/03/2022 01/01/5000
3 Student1 CSC 70 N 09/03/2022 01/01/5000
4 Student1 EEE 40 Y 22/03/2022 01/01/5000

LinusH
Tourmaline | Level 20

From an architectural point of view, I cringe when you have different update methods for different rows in the same table.

I sugest that you keep the table a pure SCD Type 2, and then if you have different requirement from a user perspective, create a view on top (or make an extract to another table) to hide history records for non EEE records.

 

That said, there is an option "Close out records not in the source table" that you can set to "No". And then add another Table Loader to load for non EEE records (Table laoder). This is just an idea, haven't tested myself.

Data never sleeps

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1789 views
  • 2 likes
  • 3 in conversation