Populate key into Fact Table

[kate_gkl]

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.

Thanks very much in advance.

[pramod_au]

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.

Please read DS documentation: Parallel Job Developer Guide

Some Good practices are:

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.

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.

Thanks very much in advance.

[ray.wurlod]

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.

[kate_gkl]

ray.wurload wrote:

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.

[rasi]

Following will explain what is reverse lookup

Dimension Table : DIM_DATE
DIM_Date_Surrogate_Key Date_Natural_Key
1 20040721
2 20040722
3 20040723

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.

Fact_Sales
Sales_SK dim_date_Surrogate_Key Sales
1111111 1 400.00
1111112 1 600.00
1111113 2 700.00

Above answers your question.

Thanks
Siva