Updating Fact Table : Best practice

[marric]

Hi,

Ssystem used :
Windows 2003 srv
SQL Server 2005 STD for the OLTP
SQL Server 2005 Ent for the DW with
- SSIS for the ETL
- SSAS for the cube
ASP.NET control to display the OLAP for the user

I know theres is not "one size fits all" solution but I would like to have input about the best pratice when dealing with fact table update.

We are using a OLTP database to manage paramedic events. Those events are entered in the OLTP database and they can be changed later for many reason (Sometimes they enter wrong date and it can affect the measure about how long the call took, the time spent on the crash site, .....). So we need to update the fact table to fix the error (All possible date errors can't be trapped in the ETL process).

Users don't need to keep track of the change made in fact table.

So my question is ... It is ok if, in my ETL, I check for the new row and add them in the DW and I run a query that update all the existing row already in the DW. Is this method against best pratices ? Should I wipe the fact table each time and add the new records ?

thanks for the input ... and sorry for the poor english ... i'm french canadian !

richard

[ngalemmo]

Either updating existing rows or delete/insert specific rows will work Which one is better depends on which is easier and the particular database system you are working with. In the case of SQL Server it doesn't matter much, an update-in-place will probably be more efficient (less work for the system to maintain indexes).