Building Aggregates from Aggregates

Hello All,

New to the forum. This is a really good forum and great information. Thanks to all for contributing.

Question - We have a situation where we build an aggregate table from using a detail level table. And, then there are few more tables at different level that can either be built from the original detail table or a pre built aggregate table. I was leaning towards building the other aggregate tables from the detail table instead of using the other aggregate table just because that will force the dependency and we can't build all aggregates in parallel.

What is the recommended approach and Why? Any insight is helpful.

First figure out if you need the additional aggregates in the first place. Do some performance testing on the aggregates you already have (and the detail table) and see if performance is acceptible and/or a little bit of tuning will make them acceptible.

Having a lot of different aggregates of the same data can become a maintenance nightmare with negligible return.

Thanks for the reply.

The aggregates are at different level, one is at account level and the other at fund level. The detail table is at account/fund/transaction level. Presenting this at on the fly at two different levels is goign to be a performance problem for us. So, i can build the account/fund level aggregate and then build fund level from first aggregate or i can build both from detail table. What is recomended?

My problem with building the aggregate based on aggregate is the dependency i have to place and i wouldn't be able to run building both aggregates in parallel.

Thanks for the input folks.

Either way will work. Choose which best fits your situation.

The only thing to be wary of if you are building them separately, is that any oddities in the data that affects the account-level aggregates, affects the fund-level aggregates in the same way. I.e. if the logic that builds the aggregate tables does any transformation, make sure it is the same for both.

For example, if you need to convert monetary amounts into a standard currency, summing up to account level and then converting for the account fact and summing up to fund level and then converting for the fund fact may introduce rounding errors. Convert and then sum.