Techniques for Updating existing fact records

View previous topic View next topic Go down

Techniques for Updating existing fact records

Post  johnpaulmurphy on Wed Sep 16, 2009 2:57 pm

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.

johnpaulmurphy

Posts : 1
Join date : 2009-09-16

View user profile

Back to top Go down

Re: Techniques for Updating existing fact records

Post  ngalemmo on Thu Sep 17, 2009 11:26 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Techniques for Updating existing fact records

Post  beyeguru on Tue Sep 29, 2009 12:53 am

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

beyeguru

Posts : 5
Join date : 2009-08-03

View user profile

Back to top Go down

Re: Techniques for Updating existing fact records

Post  jgilfill on Tue Oct 06, 2009 10:30 pm

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

jgilfill

Posts : 1
Join date : 2009-04-13

View user profile

Back to top Go down

Re: Techniques for Updating existing fact records

Post  ngalemmo on Wed Oct 07, 2009 12:29 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Techniques for Updating existing fact records

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