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

Updating a Transactional Fact

2 posters

Go down

Updating a Transactional Fact Empty Updating a Transactional Fact

Post  ss315 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:
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.

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.


Posts : 1
Join date : 2014-06-03

Back to top Go down

Updating a Transactional Fact Empty Re: Updating a Transactional Fact

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

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

Back to top Go down

Back to top

- Similar topics

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