Hi everyone. I have encountered a scenario that need to populate key from dimension table to Fact table. I am wondering is there any good practice that we can use to solve this scenario.
Hope you guys can share your opinions and experience.
Create a job which loads dimension keys into Fact table. Run this jobs using job sequencer. Depending upon the requirement u can run this job in parallelism or sequentially.
You may also use Parallel Extender to assign keys.
1.Have ur Dim table in Hash file.
2.Take care in selecting modulo number for Hash file design.
3.Try to use Inter Process Communication Stage, write cache etc.
kate_gkl wrote:Hi everyone. I have encountered a scenario that need to populate key from dimension table to Fact table. I am wondering is there any good practice that we can use to solve this scenario.
Hope you guys can share your opinions and experience.
Typically you're streaming data from source into the fact table (ideally via a staging area). What you need to do is to provide a mechanism - probably a dataset - containing the "reverse lookup" from the key values that are found in business data and the surrogate keys with which you keyed the dimension table.
By this means you will be able to populate the foreign key column in the fact table that accurately coincide with rows in the dimension table.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
What you need to do is to provide a mechanism - probably a dataset - containing the "reverse lookup" from the key values that are found in business data and the surrogate keys with which you keyed the dimension table.
Can you please explain further on what you mean by "reverse lookup"? Thanks for your help.
Business Data(Which is the source data)
Rec_No Sales_Date Sale_Value
100 20040721 400.00
200 20040721 600.00
300 20040722 700.00
Based on your Business Data the Sales_Date is used as the reverse lookup with the Date_Natural_Key in DIM_Date table. Once you got the DIM_Date_Surrogate_Key of the Dimension table(DIM_DATE) you can pass this to the fact table.