cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member198401
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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