Updating a Transactional Fact

View previous topic View next topic Go down

Updating a Transactional Fact

Post  ss315 on Tue Jun 03, 2014 6:40 pm

Is it ok to update a transaction fact table? I've read Kimball only recommends this if records are in error.

2 Situations:
1)
Retail client sends transaction records with transaction_key and customer_key and product_key, if a card isn't used they send 0 for the customer_key.
a) Sometimes a client in the store forgets to swipe their card so calls up the call centre to add their card to the transaction, client sends us the transaction again.
b) The client does a card encryption process on their end, sometimes this process encounters errors so they resupply the transactions with the 'correct' card.

2) Retail client sends the profit for a given product, customer, transaction at a later date for certain products. We already have a measure on the fact table to capture profit for a given transaction/product so rather than creating a separate fact table which is identical, the thinking is to update the profit when that extra feed comes in.

Thoughts?
Main negatives we have is the speed of updating such a large fact, generally we just do inserts as it is much faster and the only key management we need to do is ensure the transaction doesn't already exist to avoid duplicates in case they resend transactions, but we'd have to change that if resends included updates as above.

ss315

Posts : 1
Join date : 2014-06-03

View user profile

Back to top Go down

Re: Updating a Transactional Fact

Post  ngalemmo on Wed Jun 04, 2014 12:36 am

You have two choices: either update in place, or do a reversal of the old transaction (insert a new row with the same dimensional values and negatives of all measures) and an insert of the new transaction. The latter option can be done with inserts only, giving you some performance benefit on most database systems.
avatar
ngalemmo

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

View user profile http://aginity.com

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