ETL Question for Loading a Fact table

View previous topic View next topic Go down

ETL Question for Loading a Fact table

Post  kenny on Mon Jan 11, 2010 12:32 pm

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

kenny

Posts : 11
Join date : 2009-10-30

View user profile

Back to top Go down

Re: ETL Question for Loading a Fact table

Post  ngalemmo on Tue Jan 12, 2010 12:23 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

thanks

Post  kenny on Tue Jan 12, 2010 8:27 pm

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

kenny

kenny

Posts : 11
Join date : 2009-10-30

View user profile

Back to top Go down

Re: ETL Question for Loading a Fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum