SCD Type 2 with historical records

[Former Member]

Hello Team,

While implementing SCD type 2 I need one specific solution for below requirement,Can someone please help me:

Thanks,

Amit

[Former Member]

Thanks Ravi ,

Its working in debug mode but not updating in Target table.

I can see result with updated flag in debug mode but its not reflecting in target table.I already tried re-importing target table but no luck.

-Amit

[former_member208402]

Hi Amit Agrawal,

For Valid_from you can pass sysdate in the Query transform after the source table.

use 2 map_operation transforms after history preservation. 1 map operation for insert records and 2nd one is for update records. in map_operation insert (discard all op codes except insert --->insert) and take key generation after it.

in map_operation update (discard all op codes except update ---> normal) and take a query transform after this and pass sysdate to Valid_to column and now take another map_operation after this query transform (discard all op codes except normal ---> update) and connect it to target table.

Thanks,

Ravi kiran.

[Former Member]

Thanks Ravi for your answer,

Could you please let me know how to get sysdate in Valid_to in second run.We have only two option in History preserving transformation. for Valid_From. In my example I need sysdate in last record as Valid_to and sysdate as Valid_from in new record.

Thanks

-Amit

[former_member208402]

Hi Amit Agrawal,

Your dataflow should look like this

Source--->Query--->Table_comparison--->History_preservation--->Key_generation ---> Target

in Query:-

1.Remove key1 as primarykey

2.Add a new column Sur_key (Surrogate_key) as datatype int and make it as primary key and pass 1 as default value in the mapping.

3. Add new column Valid_to column as data type date (both valid_from and valid_to should be of same data type) and pass 31.12.2999 value in the mapping

4. add new column Active_Flag and pass 'Y' as default in the maping

5. in order by drag and drop key1 column

In Table comparison

1. in table name select the target table

2. in generated key column select Sur_key column

3. Drag and drop Key1 column in input primary key and column1,column2,valid_from in the compare columns

4. select comparison method as sorted input

in History Preservation

1. in valid_from select your source Valid from column

2. in valid to select your source Valid To column

3. in valid to date give default date, in your case 2999.12.31

4. in column select Active Flag column

5. in set Value give 'Y'

6. in reset value give 'N'

in Key generation transform

1. in table name select your target table name

2. in Generated key column select Sur_key column

3. increment value should be 1

validate your job and execute.

Thanks,

Ravi kiran