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

update facts?

3 posters

Go down

update facts? Empty update facts?

Post  topcat Wed Jan 09, 2013 4:41 pm

I have a fact table that tracks retail returns. there is a reason dimension, but often no return reason is supplied initially. the reason dimension has a "not available" record, so when the fact comes in initially, the key is set to this record. now what happens is the customer is contacted and the return reason is determined and selected in the source system. when this new data comes to the warehouse, should i insert a new fact record or update the existing? pros and cons for both, if i update, then the initial response is lost. if i insert a new fact record, then if i aggregate the returns for a given period, the amounts will be incorrect.


topcat

Posts : 19
Join date : 2012-08-09

Back to top Go down

update facts? Empty Re: update facts?

Post  ngalemmo Wed Jan 09, 2013 5:44 pm

You have three options:

1. Implement a snapshot fact table, meaning rows are updated in place.
2. Implement an accumulating snapshot fact table, meaning rows are inserted and carry an effective and expiration date.
3. Maintain return reason as a separate factless fact table. Easier to load, but more challenging to use in queries.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

update facts? Empty Re: update facts?

Post  BoxesAndLines Thu Jan 10, 2013 10:26 am

I would assume most of the business folks are not interested in tracking history for late arriving dimension data, especially for the case of Unknown to Known. If the database can take the performance hit, I would update the fact recrod.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

update facts? Empty Re: update facts?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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