Hi all
Just wondering if I could tap into your wide experience to help us with an issue we are facing at the moment.
We have recently rebuilt an existing application we use, with one of the larger changes being to enable multi-currency functionality. As you know this adds the HSP Rates and Currency dimensions to the Planning application.
From doing this, this has increased our pag files from around 7 pag files to ~20 pag files. We accept this since there are 2 reporting currencies and the local currency, so ~3 times larger seems fair. This is with all data aggregated, all currency calculated. Anyway, our issue is that when we do a dense restructure, this reduces our page files to about 17 page files. This also makes some sense to us since the database may become fragmented from data loads, business rules, user input and the like, so the dense restructure is just reducing fragmentation. However, when we do a simple agg (i.e. AGG(Entity,Product,Project)), this blows the pag files to 23.5 pag files. This is confusing because we expect that since our database is already fully calculated and aggregated, there shouldnt be such a dramatic change in pag file size. No data loads nor other business rules have happened between this time.
On doing some investigation, it seems that the upper level blocks have increased dramatically, but as I said before, the database should have already been fully calculated and aggregated. To prove this, we simply did another dense restructure and it reduced to 17 pag files, and then ran the agg again and it went back up to 23 pag files.
The other strange thing is that our index file goes from about 500Mb when its 17 pag files to about 2Gb when its 23 pag files. Its like the AGG is fragmenting our database.
Does anyone know what is causing this and are there any steps to stop this from happening?
We are on 11.1.2.2 patched to the latest and its a Hyperion Planning BSO cube. From a dimensionality perspective, the only significant change was the currency and hsp rates dimensions. The switches we use in the AGG rule are just:
SET CALCPARALLEL 4;
SET CACHE HIGH;
SET LOCKBLOCK HIGH;
SET UPDATECALC OFF;
SET AGGMISSG ON;
Thanks in advance!