Update and deletes in incremental loading of the fact table

Dear all,

I'm very new to ETL and I've been trying to incrementally load a fact table. But I have little idea about how Updates and deletes need to be managed. My business requirement wants a snapshot (eg: comparison of this month's MTD with last months MTD) so I have to keep all history values in the fact table. If the record is updated in the OLTP, then it has to be a new record in the OLAP.

My question is this: If I update 1 record in the OLTP, then there will be two records in the fact table (original and updated). So when I'm adding all values in the fact table, wont the numbers be wrong?

Just to be clear this is what I mean: Sale 1 is recorded on 1-Jan-2010 for $3000 in OLTP. Then Sale 1 is loaded into fact on 1-Jan-2010. Then on 23rd Jan-2010, Sale 1 is updated to $5000. If I want to keep the history and load sale 1 again into the fact, then won't sale 1 be worth $8000 in an MTD calc for Jan because in essence it is the same sale?

To avoid this, should I just update the same sale 1 record in the fact? I know that if I'm updating the record, then the old information is lost. but if my business wants the old information, then the MTD value for the record must show $5000 and not $3000 or $8000.

Please help - I'm new to ETL and I dont have much help. I will be grateful to you if I can find a solution to this.

Thanks,
Abhishek

There are three basic forms of fact tables... (I should save this somewhere, it seems the question keeps coming up)...

1. A current view fact table, one where rows are updated in place and there is no history.

2. An accumulating snapshot, where history is maintained as a series of versions of the fact. In such a table you have begin and end effective timestamps for a row. If you need to update a row, you expire the old one (update the end date) and insert a new row. When querying, you select rows based on the desired point in time by selecting rows between the begin and end dates. For current versions the end date is set to some far future date, such as 1/1/3000.

3. A transactional fact. History is maintained as a series of deltas (changes) from the current fact. In this case you have an update timestamp. To get a point in time value, you sum rows where the update timestamp is less than or equal to the desired point in time. Unless your data feed is transactional in nature, this type of fact is more difficult to build than the accumulating snapshot. But it has some advantages... the table is never updated, rows are always inserted. Also it records the magnitude of change simplifying some kinds of analysis. The downside for queries is you must always sum all rows to get the most current value.

I guess what I was looking at was an accumulating fact. I missed the begin and end date logic for the old record. Thank you so much!

Regards,
Abhishek