Skip to Content
avatar image
Former Member

SCD Type 2 with historical records

Hello Team,

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

Thanks,

Amit

capture.jpg (119.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jul 27, 2017 at 08:50 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 28, 2017 at 07:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 29, 2017 at 09:38 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 31, 2017 at 12:25 PM

    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

    Add comment
    10|10000 characters needed characters exceeded