ETL Question for Loading a Fact table

[kenny]

I have a Fact table with the following facts which is supposed to be loaded daily

Case_Statistics_Fact
================
CaseId (FK)
Time_Id(FK)
EsitmatedCost
EstimatedStartdate
ActualCost
ActualStartDate

Here is the scenario

when a case is added it is added with the EstimatedCost and EstimatedStartdate lets say (Jan 1,2010) after a week or x no of days lets (Jan 7,2010) the actualcost and actualstartdate is updated in the source system

The daily ETL will pickup the Jan 1,2010 record and add it to the Fact Table, then on Jan 7 the daily load will pickup the same record with the ActualCost and ActualStartDate populated

My question is, in the Fact Table should I create a new record with the Jan 7,2010 data or update the Jan 1,2010 data

and if I create a Jan 7,2010 record, how will the report developers know which row to pick since there will be 2 records the first one incomplete and the second one with the complete data, reports are run daily, is it normal to update a Fact record when the new data is populated in the source system

Please advise

Thanks
Kenny

[ngalemmo]

You have a lot of options. One, you can update in place and not maintain history. Two, you can add an effective date range and a current flag to the table and keep a history. Three, you can break up the fact table into an actuals table and an estimate table.

[kenny]

ngalemmo thank you sir, you always guide in the right direction, appreciate your help

kenny