Too many aggregate tables ?!

Hello everyone,

I was wondering if there was such a thing as too many aggregate tables (of different levels ofc) on a fact table ?

What are your thoughts on this ?


Cheers !

Yeah, sure. Sometimes even 1 aggregate is too many... if it is too specialized or too general (i.e. doesn't improve query performance by much).

That's where an OLAP cube comes in handy. Without cube, you really don't have too many options but throwing in aggregates.

It comes down two points, performance and extra maintenance work on numerous aggregates. As long as performance is reasonable, I would stop building aggregates from certain level and let queries/views to dynamically handle the aggregations to limit the extra pressure on ETL maintaining the aggregates. Proliferation on aggregates could potentially blow out the load window of ETL.

I was wondering if there was such a thing as too many aggregate tables (of different levels ofc) on a fact table ?

If you mean storing aggregates of different levels in a single fact table, this is a big no as the table would have facts of multiple grains and you would always need to be mindful of limiting the aggregation level at query time.

One useful way to design your aggregates is to look at the levels of aggregation at which other metrics suddenly become available and add aggregates of lower level facts to the higher level fact table.

For example, if you have a Shipping fact where the grain is one row for each truck that leaves the depot to make customer deliveries and contains facts like estimated and actual mileage, fuel cost etc., it would be useful to add the number of customers being delivered to, the number and value of order items in the truck, all of which would come from lower level fact tables.

ngalemmo wrote:Yeah, sure. Sometimes even 1 aggregate is too many... if it is too specialized or too general

I'll keep that in mind when designing, but it goes without saying that I won't keep such a table if it doesn't improve the performance.

hang wrote:That's where an OLAP cube comes in handy....

That's the weird thing. I've created an OLAP cube, but it doesn't improve the performance at all. So I guess I'm doing something wrong there.

Dave Jermy wrote:If you mean storing aggregates of different levels in a single fact table ...

That was not what I meant, but thank you for clarifying :-)


Thank you all for taking the time to reply, I appreciate it !

ObjectiveC wrote:I've created an OLAP cube, but it doesn't improve the performance at all. So I guess I'm doing something wrong there.

It should, if you know cube precalculates all the possible aggregates for all the possible levels in any hierarchies that you configured and store them in cells. So at the query time there's little or non calculation required on the fly, just as what your aggregates are supposed to do, but on much larger scale. That's run time performance.

At the processing time, OLAP cube can carry out numerous calculations/aggregations in a much more efficient way than SQL aggregation and hence performs much better. The number of aggregates that a cube can easily handle would become unmanagelbe by conventional aggregation.

Sometimes creating aggregates at a slightly higher level than you initially planned is a good thing. Say you had 5 dimensions each with 4 levels in a hierarchy. Your created 5 aggregates. Each aggregate had 4 dimensions at the 3rd level and a 5th dimension at the 4th level. You might be able to create 1 aggregate with all 5 dimensions at the 3rd level. With the right indexes, your 1 aggregate might perform better than the 5 aggregates - it will certainly reduce the load time. This is a good strategy particularly if you are using the aggregates for building cubes.

hang wrote:
It should, if you know cube precalculates all the possible aggregates for all the possible levels in any hierarchies that you configured and store them in cells. So at the query time there's little or non calculation required on the fly, just as what your aggregates are supposed to do, but on much larger scale. That's run time performance.

At the processing time, OLAP cube can carry out numerous calculations/aggregations in a much more efficient way than SQL aggregation and hence performs much better. The number of aggregates that a cube can easily handle would become unmanagelbe by conventional aggregation.

I now did create aggregate tables defined in the cube, but I thought it should've been fast without me defining those tables. I thought the cube would give you a performance boost even without the aggregate tables, but I guess that's not the case (at least not with me). In fact when I compare the sql execution time between my sql querytool and with the cube, the cube seems to be somewhat slower.

With the aggregate tables defined, everything is superfast.

Thanks for the help you guys !