HI THERE
I have to handle type 2 dimension in dim table can u let me know how datastage handle it
thanxs
samra
TYPE 2 DIMENSON
Moderators: chulett, rschirm, roy
Search is your friend because, as you may imagine, this topic has been discussed quite a bit on the forums. 
For starters, here is one possible starting point.
For starters, here is one possible starting point.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Design both the population of dimension tables, and the subsequent population of fact tables. Design, too, the business rules that determine which columns are significant in determining that a change has occurred and which are not (for example, you usually don't keep old telephone numbers).
Leverage DataStage's hashed file technology to perform the "reverse lookups" from natural key to surrogate key in each case.
Leverage DataStage's hashed file technology to perform the "reverse lookups" from natural key to surrogate key in each case.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: TYPE 2 DIMENSON
You can follow this. This is one of the approches we follow.
1. There will be records for Update ( That needs to be a changed record of the existing record) and there will be new records also.
2. For insert records look up into the target table and see if the exact record exists, if it exists then do not process it or reject it. If it does not exist then insert it into the targer with BEG_DT as CURRENT_DATE and END_DT as a distant future dat such ass 9999-12-31.
3. For Update records look up into the target table and see if this record has undergone any change. If yes then Update the existing record in target by updating the END_DT of existing record as CURRENT_DT. And insert a new record with new SUROGATE_KEY (if any) and BEG_DT as CURRENT_DATE and END_DT as a distant future dat such ass 9999-12-31.
Please lemme know if the process is clear to you.
Thanks
1. There will be records for Update ( That needs to be a changed record of the existing record) and there will be new records also.
2. For insert records look up into the target table and see if the exact record exists, if it exists then do not process it or reject it. If it does not exist then insert it into the targer with BEG_DT as CURRENT_DATE and END_DT as a distant future dat such ass 9999-12-31.
3. For Update records look up into the target table and see if this record has undergone any change. If yes then Update the existing record in target by updating the END_DT of existing record as CURRENT_DT. And insert a new record with new SUROGATE_KEY (if any) and BEG_DT as CURRENT_DATE and END_DT as a distant future dat such ass 9999-12-31.
Please lemme know if the process is clear to you.
Thanks