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 ?
  • Unfortunately, recomputing all the aggregated tables is the most reliable way to do this. – Randolph West Oct 21 '16 at 22:27
  • hum... this is sad... – irimias Oct 25 '16 at 8:07
  • Normally this type of data storage would be handled using an SSAS server and SSIS to load from your data warehouse. I feel you may be using your SQL RDBMS in a way it wasn't intended. SQL Server is intended to be used by designers who follow 3rd normal form. That means any aggregations based on raw data would be calculated on the fly as needed, not necessarily stored in a table on disk. What I would suggest is a star pattern database or cube. With SSAS and SSIS there should be some other tricks that will help you do a more surgical update of aggregations. The right tool for the right job. – Shooter McGavin Oct 27 '16 at 21:37
  • @ShooterMcGavin : My data model is already a pure star schema. I do agree, SSAS could be a solution to handle this data. But mostly for reporting purpose, which is not what I am looking for. Moreover, SSAS doesn't solve my problem : pluging a cube on the raw data would not be efficient at all (far too long to process). Same for calculating everything on the fly. If it was fast, of course I would consider these options. But it is not. I could buy a super powerfull server with tons of ssd cpu and ram, but I guess we can handle the problem in a smarter way. – irimias Oct 28 '16 at 7:55
  • Considering your circumstances Im not necessarily pushing SSAS as an answer. I was simply stating that as a product, it has more of a feature set that you are looking for. I'm my experience, it tends to be overkill at most places I've worked. The other option, which may be more tedious but also very resource intensive would be to write a series of triggers on each table and allow them to chain through and update aggregated data. This is probably a very bad idea though if this system is also a transactional processing system with any kind of user load. – Shooter McGavin Oct 28 '16 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.