I'm wondering if it is possible to update an aggregated table retroactively in case of one of the dimension changes.
A simplified example to be more clear :
I have a data warehouse with the details of transactions done by the company. Two important data are the date the transaction was created, and the status of this transaction.
For reporting and analysis purpose I want to aggregate this into a smaller table. Let's say, aggregating by day and by status :
aggregateId creation_date status total 1 2016-10-01 1 15 2 2016-10-01 2 93
But sometimes, it can happen that the status of a transaction changes, several days after it was created. For instance one of the transactions made on 2016-10-01 with status 1 has now status = 2 and another has now status = 3. The aggregated table should now look like :
aggregateId creation_date status total 1 2016-10-01 1 13 2 2016-10-01 2 94 3 2016-10-01 3 1
What could be the methods to achieve this ?
- Recomputing all the aggregated table is not a solution : in the real life the table is quite big, with many other dimensions and it takes already a long time to compute one day of data.
- Identifying the aggregated line that need to be changed (here #1 and #2), updating them, and then adding the new ones can be quite complex and hard to maintain (for instance when we add a new column in the aggregated table).
- maybe slowly changing dimension can help but I'm not sure to understand how
- I'm using SQLServer, maybe SSIS can help ?