SSAS ETL - Trying to cope with source system problems

Just had a meeting with some of the guys in charge of the source systems we require for the DW.

The good news is that most of the systems have a sensible audit trail of entries for each transaction/action, so if a quantity on order decreases by 1, an entry -1 appears, or cancelling an order has a correspending transaction to offset the earlier one. Some systems have a 'last modified' column so we can see the data has changed, which we can also work with in SSIS to get the data.

Where it gets difficult is where these things are not pesent, or where the system does a hard delete of data and leaves no audit trail (although most decent OLTP systems do 'soft deletes', so the row is marked as deleted, others do not).

Fixing the source systems is obviously the best solution, but it is also expensive. We could possibly put triggers into some of the source systems (where the DBA will let us) to track changes, or we could do it all at the ETL end and rebuild the affected fact table on every cycle.

Not sure what way this is going to go but I think we are going to spend a lot of time getting the ETL right...