topic Re: using SCD type 2 transformation with where condition in SAS Data Management

using SCD type 2 transformation with where condition

learn_SAS_23 — Wed, 09 Mar 2022 16:40:32 GMT

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

Re: using SCD type 2 transformation with where condition

ChrisLysholm — Wed, 09 Mar 2022 18:55:57 GMT

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.

Re: using SCD type 2 transformation with where condition

learn_SAS_23 — Fri, 11 Mar 2022 14:45:22 GMT

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.

Re: using SCD type 2 transformation with where condition

ChrisLysholm — Fri, 11 Mar 2022 15:12:52 GMT

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.

Re: using SCD type 2 transformation with where condition

learn_SAS_23 — Tue, 22 Mar 2022 14:04:15 GMT

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

Re: using SCD type 2 transformation with where condition

LinusH — Tue, 26 Apr 2022 08:58:03 GMT

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.