on 10-29-2015 11:25 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.