I have a report that needs updating daily. I can do this by loading all data each day as a scheduled job overnight that overwrites the previous day and letting the user run it on demand once.
However the best practice is to use incremental loads and a QVD middle layer.
In other words, the report would run against the data in the QVD.
The QVD would be updated daily with just the rows that had changed on the source system in the last 24 hours using a source system change date field.
The only thing that niggles at me with this approach is that to identify the rows that have changed in the last 24 hours, your SQL style select statement still has to read through the entire source table to be able to identify the rows that have changed in the first place.
So is this approach really making a great time saving compared to reading all rows and just overwrite rebuilding the report from scratch?
