Updating a Transactional Fact
2 posters
Page 1 of 1
Updating a Transactional Fact
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.
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
Re: Updating a Transactional Fact
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.
Similar topics
» transactional fact vs periodic snapshot fact
» Transactional fact question...
» Transactional fact table with a date ?
» Transactional Fact and update of records
» Fact table for transactional data
» Transactional fact question...
» Transactional fact table with a date ?
» Transactional Fact and update of records
» Fact table for transactional data
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum