cancel
Showing results for 
Search instead for 
Did you mean: 

Is SCD Type 2 possible without using table comparison and History Preserving?

0 Kudos

Hi All,

I've used table comp,history preserving along with the key gen transform for achieving SCD -Type 2 but recently came across this question.

Wondering if we can achieve this without the use of these 3 transforms.

Any replies are much appreciated.

Regards,

Ankit

Accepted Solutions (1)

Accepted Solutions (1)

former_member208402
Active Contributor

Hi Ankit,

We can implement SCD type2 with out using Table comparison and history preservation transforms.,

We need to perform lookup or left outer join to trace inserts and updates and accordingly do the mappings.

But for insert records we need to use Keygen transform or keygen function to generate surrogate keys in target table.

please let us know if you would like to know more detailed information about how to implement this.

Thanks,

Ravi kiran.

0 Kudos

Hi Kiran,

Thanks for the reply.

Would be glad if you can provide the flow of the transforms or a screenshot of any sample job so that I would understand it little more.

Regards,

Ankit

former_member208402
Active Contributor

Hi ankit,

Design your Dataflow like this

                                                                                         

                                                                                           |-->map_ins_upd---->keygen---->target

                                                                                           |

source------> query_lookup---->query_scd------>case_transform--|

                                                                                           |

                                                                                           |--->qry_UPD--->map_UPD--->target

query_lookup:-

use lookup_ext function and perform lookup on target table and give condition on what ever key columns you would like to compare ( what ever you want to give in table comparison input key columns) and in the output tab in the lookup_ext take any output column.

Now if the lookup condition satisfies the output column will return whatever value that is present in the target and if the condition fails , output column will return NULL.

If the record in the key column coming from the source is not present in the target then the lookup condition fails and return NULL in the output column and if the key column coming from the source is present in the target it means that record is already present in the target and it returns what ever the value that is present in the target for that record in the output column.

if the output column is NULL then it is INSERT record and if the output column is not null means its is update. For update we can perform additional check on compare columns to see if the records are changed or not.

query_scd:-

Take a new output column of datatype varchar and name that column as FLAG.

write this condtion in this FLAG column

ifthenelse(<outputcolumn> is NULL,'INS',UPD')

Here we are differentiating insert record with 'INS' and update records with 'UPD'.

Case_transform:-

give 2 conditions in case transform

1. FLAG in ('INS','UPD')

2. FLAG='UPD'

Map_INS_UPD:-

Map 1st condition to this map_op transform from case and set normal to insert here.

so here INS and UPD records get inserted with new surrogate id (keygen)

qry_UPD:-

map only the columns (ex:- To_date and from_date) to the schema out, to change the date fields for the records and active flag to 'N' in this query transform

Map_UPD:-

set normal to update and connect to target.

Note:- in the lookup take surrogate key from the target as output so that you can use that column to update the records that pass through qry_UPD.

Please let me know if this is clear.

Thanks,

Ravi kiran.

0 Kudos

Thanks a lot Ravi for the detailed explanation.

Regards,

Ankit

0 Kudos

Hi Ravi,

Please share complete job details(pictorial) for SCD2 without TC and HP.

Answers (0)