Multiple Aggregates for 1 Query

[Former Member]

Hi -

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.

thanks,

Kartik.

[Former Member]

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.

[Former Member]

Just a followup -

The scenario you describe is a perfect case for a multi-provider. Then you have the benefit of queries running in parallel, reducing total elapsed run time.

I have not found any benefit to creating separate aggregates on a single Infocube split by year. Depends on your DB, but in Oracle, using STAR_TRANSFORMATION, the execution plan quickly scans the bitmap indexes and idnetifies the fact table rows in question. The heavy lifting is a function of retrieving the rows, not finding them. Since you would have to retrieve the same number of rows from the 3 yearly aggregates as you would from a combined aggregate, don't see value there.

The one place that I would consider a yearly aggregate, is if a very high percentage of the queries on a cube are run for the current year ( or any one particular year) and an aggregate for all the years ws very large, then I might create an aggregate just for that one year solely to save some disk space.

Pizzaman

[Former Member]

Kartik - After rereading your post, is what you're asking is does 3.5 provide parallel processing on mulitple aggregates rather than sequential execution in 3.1? Don't know, but doubt it.

If that's the case, seems like I may have wasted some out time.

I was interpreting the question as asking if a single query could use multiple aggregates when it ran.

Sorry for any confusion.

Pizzaman

[Former Member]

Hi Pizzaman -

Thanks for your input. You did understand the question correctly. Its good to know that a query can infact pickup multiple aggr.

However, in a test I did yesterday, it seems the query is not smart enough to pick up the yearly aggregate based on a calculation. eg. My queries are run with yr= 2005(selection screen input), but they also gather yr-1, yr-2 data. Although, the query hit 2005 aggr, it was looking for CALYEAR=*(RSRT) for the other datasets and didNOT hit 2004 and 2003 aggrs. I could replicate this scenerio by running the query for 2004.

The reason I wanted to split up the aggr by year is to reduce the rollup times going fwd as the aggr will get bigger as time goes on.

We recenly upgraded our Orcale dbase to avail STAR_TRANSFORMATION. I am not sure if it has paid dividends yet. Also, I plan to up our cache size.

any comments?

thanks,

Kartik

[Former Member]

The BW parser looks at the restrictions you specify and sees if they match the fixed values of your yearly aggregates are defined. Since you don't provide a fixed value for yrs 2004 and 2003, it can't make the decision to use those aggregates.

As far as rollup, i don't see the regular daily rollup time being reduced by having yearly aggregates (I'm assuming the new transactions are all for 2005 or at least most of them, rather than evenly spread out over all 3 years). The rollup process should quickly summarize the new Requests that have been loaded and update the aggregate. Separate yearly aggregates would rollup faster, however, if you have to refill the aggregate, <u>and</u> you have parallel processing on so the rollups run concurrently. This would also depend on how big your server is, as parallel processing can quickly result in overwhelming the DB server, although the last svc pavk or two has started to add some options to better control some of the parallel processing options so that you can prevent this form happening.

STAR_TRANSFORMATION is very helpful for most InfoCube queries. The more characteristics you filter on in a query, the better it works due to the fact that Oracle can perform bitmap index merging as the first part of the execution plan. The one place STAR_TRANSFORMATION doesn't work well is if you run recon or other queries that must read all or most of the fact table, since this forces an indexed read of the fact table when a full scan would be more efficient. One way to work around that is to create a very highly summarized aggregate that runs jus to support the recon query. SAP should provide an option to suppress the STAR hint when you are creating a recon query.

You should really review an execution plan for a query that is doing a STAR TRANSFORMATION to see how it works if you have not done so.

Pizzaman