Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Techniques for Updating existing fact records

Go down

Techniques for Updating existing fact records Empty Techniques for Updating existing fact records

Post  johnpaulmurphy 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

Back to top Go down

Techniques for Updating existing fact records Empty Re: Techniques for Updating existing fact records

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

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

http://aginity.com

Back to top Go down

Techniques for Updating existing fact records Empty Re: Techniques for Updating existing fact records

Post  beyeguru 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

Back to top Go down

Techniques for Updating existing fact records Empty Re: Techniques for Updating existing fact records

Post  jgilfill 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

Back to top Go down

Techniques for Updating existing fact records Empty Re: Techniques for Updating existing fact records

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

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

http://aginity.com

Back to top Go down

Techniques for Updating existing fact records Empty Re: Techniques for Updating existing fact records

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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