Query to read more than one aggregate at same time - is it possible?

[Former Member]

Hi,

We have 2 aggregates set up. One contains data for 2007, the other for 2006. These are restricted by setting fixed values for 0CALYEAR.

When running a query for a single year the correct aggregate is picked up depending on which year is input.

When running the query for a range which spans both years then no aggreagate is picked up and the cube is then read.

Is there a system setting somewhere (RSADMIN table possibly?) which allows for multiple aggregates to be used when query is executed?

Your help would be greatly appreciated.

Thanks,

Steve

[Former Member]

Hi Steve,

If you define restricted key figures in the query per year, it will read the data from the 2 aggregates.

To check I recommend you to run the query in RSRT.

Choose the option "Execute + debug" and flag the option Display Aggregate Found or even select aggregate if you want to play with it.

As example create 2 aggregate 2007 and 2006 and 2 restricted key figures for those year

You will see that the system will propose

- first the cube and then 2007 and when you will have made your choice he will

- then present you 2006 and the cube.

Hope it helps.

Best regards,

Rodolphe

[Former Member]

Hi Rodolphe,

Thanks for the answer.

Unfortunately we have too many queries against the cube in question to have to create and utilise restricted key figures per year.

I'm looking for a quick fix solution here if it exists - i.e. a parameter setting possibly.

I of course always use RSRT when testing the use of aggregates.

When I enter the range of years I get the following message:

Not possible because -

'____Characteristic/attribute 0CALYEAR does not have aggregation level */%'

This is displayed against both the aggregates for 2006 and 2007.

When using only one year then it correctly finds the aggregate for that year.

Thanks,

Steve

[Former Member]

Steve,

Why you don´t create a third aggregate? As a copy of any of the existing ones but in this case you must set 0CALYEAR equal to *

This should fix your issue.

Regards,

Andreu

[Former Member]

Hi Andreu,

Tha't's just the situation we are getting away from. We want smaller aggregates and hence why we are creating aggregates per calendar year. We rebulid these on a monthly basis for the particular cube in question. We then only have to rebuild the aggregate for the current year which is a also a lot quicker.

I'm sure I've heard of a way to utilize the reading of multiple aggregates either in paralle or sequence when using queries.

Thanks,

Steve