Skip to Content
avatar image
Former Member

How to implement type1 scd and type2 scd both in a single dimension table?

I am fairly new to data services and facing few more challenges. Would you please provide inputs on below:

1.I am trying to implement a dimension table having both scd type1 and type2 column. Now lets say my type 2 column is changed, it gonna create a new row with current flag as 'Y' and will expire the old row with 'N' and the column will have new values in the new row. Now, if TYPE1 column is changed, its jut updates the latest row with current flag as 'Y" and older row with flag 'N" still has older value for type1 column. How can i update all the type 1columns in older rows as well.

2.The effective date for the record is 1/1/1900 and expiration date is 12/31/2050 when i run the load first time.When history is preserved the older record expiration date is set to sysdate and a new record with effective date as sysdate has to be added and the expiration date as 12/31/2050. How to achieve this?

I tried various things but somehow my dates are messed up.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 30, 2015 at 02:05 AM

    I would suggest you to keep the design simple. Phase one does the SCD Type I updates and the phase two does the SCD Type II. Each phase should be created a separate Dataflow.

    SAP Data Services Design Pattern: SCD 1 (using Table Comparison Transform)

    How to implement SCD Type 2 using History Preserve Transform in Data Services

    Above tutorial shows you the simple technique

    SCD 1

    Source>Query_Transform>Table_Comparison>Key_Generation>Target

    SCD 2

    Source>Query_Transform>Table_Comparison>History_Preserving_Transform>Key_Generation>Target


    Regards

    Arun Sasi

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks for the suggestion but I have twenty five dataflows  can't take the approach to repeat the process two times ..But with this approach too , the type 1 columns will not be updated for historical records having type 2 .  Below is an example

      surrogate key  Primary key  type1 type2 current-flag

      1                         1.              B.         C.     N

      2.                        1.              B.         A.     N

      3                          1.              X.        D.     Y

      i am looking  update all historical record of type1 with X instead of B..

      Also , if you can look into second question above , it will be helpful.

      Thanks