I have a question about building multiple aggregates on a cube. Can a query in BW 3.5 pull data from multiple aggregates on a cube or can it pull data from one aggregate at a time?
We plan the following Aggrs -
1. 2003 data.
2. 2004 data.
3. 2005 data.
A query needs all 3 years of data.
We are at 3.1 so can't imagine 3.5 wouldn't be able to do it.
Word of caution -
We have a cube and the queries have more than a dozen restricted key figures in them. At one point I attempted to tailor different aggregates to different RKFs thinking that would improve performance. The query did in fact then use 5 different aggregates. But, very big but, <b>the query performance ended up being worse</b>.
Each aggregate that the OLAP parser decides to use results in another query that must be run, actually two database queries (one against the F and another against the E aggregates) since we did not automatically rollup the aggregate because we had to always back a Request out.
So after my aggregate construction efforts I now had 10 queries that had to run against the database (2 per aggr). These queries are NOT like a multiprovider - they run sequentially, NOT in parallel.
These queries all had to join to dimension tables and master data tables, so I had 5 times more IO on these tables. Additionally, since there were more aggregates and dimension tables, the qurey was less likely to find data in the DB buffer.
So having multiple aggregates for a single query may or may not be a good thing. I ended up going with one larger aggregate that resulted in the query running faster than when it used 5 aggregates. This might all depend on the query specifics, etc. I would just be careful to review results, rather than assuming all the aggregates would be better.