Techniques for Updating existing fact records

I have certain cases where I need to adjust the data in the fact tables due to late arrivers etc...
I wanted to get peoples opinion on what techniques work best when you have to do updates to Fact rows i.e. overwrite, adjust but keep audit etc...

Thanks.

Its a matter of what the business wants/needs. The simplest is to update in place, which is fine if there is no need to recreate prior reports or provide any audit trail of changes. If you need an audit trail, the most flexible approach is to insert net change rows (one row if it is a change in measures, or two rows if dimensional references change as well) and add an 'as of' date to the fact table. This permits reporting current as well as prior states of the facts using simple queries.

For our situation an "Upsert" is working out pretty well as users always want to see the most updated data.

Where could I find more information on the "net change rows" method you mentioned?

It's a technique I have used, but I don't know if it has been formally written up anywhere.

Basically, when you need to update a fact you read the old fact and invert the sign of all measures (multiply by -1) and write the data to a temp table. You then write the new fact to the same temp table. After everything has been processed, you sum all the rows in the temp table by the foreign key values. The result is the net change. Eliminate all rows where all measures are zero and insert the rest into the fact table.

This works for any change that may take place. If a foreign key changed, you would get two rows, the orginal old and new rows, and if the dimensions did not change you would get one row with the net difference.

This technique works best of you have an ODS or persistant staging area that contains a current version of the transaction. If you are doing it against a fact table maintained in this manner, you need to extract the negative of all rows (original and delta) for that transaction from the fact table to the temp table, then sum and filter as before. The result is the same, it is just that you wind up dealing with a lot more rows if changes occur frequently.