Question:
Does anyone know of any material detailing when it appropriate to use aggregates? I'm alway wondering why aggregates are always thrown out as a solution. Shouldn't the cube be designed in an appropriate manner or is the use for aggregates best when there is too much volume?
Any thoughts and opinions are appreciated!
_________________
What does not kill you makes you stronger...
(Unless you are bleeding profusely)
Answer:
I would assume that if a cube has been badly modeled up front and is in use then the easiest solution is to create aggregates. However these are like papering over the cracks in some instances.
_________________
Oh so that what that button does
Answer:
Sure, if the cubes are designed according the results needed, and not the opposite, it should work without aggregates ....
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
Yes, but what about after many months or years ?
Sometimes new needs will involve less characteristics ... Or you will create new hierarchies and will want to aggregate with their values.
So, steps of maintenance may sometimes creates aggregates.
_________________
Laurent THIBERT
Business Connection Amerique
Answer:
I don't think appropriately modeled cube would exclude the use of aggregates. It should be the other way around since a cube should cover many topics of information for regularly run reports and ad hoc queries. The aggregates should help the former's run time when the data volume is huge.
Answer:
Of course ... all is a matter of choices .
first choice is : only few cubes, all queries on these cubes, and aggregates to help queries
Second choice is : developing new cubes which are more close to the needs, more aggregated (no need of keeping all the invoices lines since beginning ...), regularly emptied ..
Two choices are good one, depends on the strategy chosen.
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
At service.sap.com/bw -> Performance there are a lot of documents about performance including some specifically about aggregates. The creation of appropriate aggregates is always mentioned as an important factor in any discussion of performance tuning (it is the thickest chapter of the BW360 course notes). The following document is a good starting point and provides references to specific notes about creating aggregates.
https://websmp105.sap-ag.de/~sapidb/011000358700001890502003
There are often trade-offs in basic cube design between query performance and other factors. In almost all cases aggregates allow you to overcome these; omitting degenerate dimensions, turning navigable attributes into characteristics, and referencing master data directly from the fact table. For this reason I think it is better to focus on the factors other than query performance when designing cubes, such as business requirements, data consistency and maintainability, and loading.
However much the data model is optimized the major advantage of aggregates over the basic cubes is that the fact table can be so much smaller; it is clearly much more efficient to return 10 rows of data from a 100 row aggregate than a 10 million row cube. Except in rare cases of very small cubes I'd say aggregates are always appropriate.