Partition for Reporting?
Hi gurus,
We have an application with 12 dims. 3 Dense the rest Sparse (11.1.2.1). We figured out aggregations take too much time and we decided to try Partitioning and check if we can expedite aggregation times.
For doing that, we created a "mirror" app but ASO, and created transparent partition between Planning (essbase app) and the ASO cube.
Basically we need aggregation in 6 dimensions, being the bigger one with about 1600 members.
We included in the area definition as low amount of members as we could, basically going to all level 0 members. 2 of the dimensions are using multiple hierarchies, as we use shared members (not included in the area definition), and it would be the way to fit in to the ASO specification.
After doing this, aggregations are faster than the BSO cube, but still they take so long when we try to get a report with 4 dimensions consolidated to TOTAL (more than 20 minutes for just one single account), and reports will include more than one and more than one month and scenario :(
I don't know if I'm asking to much to the transparent partition as I understand it will calculate first in the BSO (for the dynamic calc members) and then consolidate them in the ASO
Is there any way to imporve the performance in the transparent partition?
What other approach could be used for reporting with almost realtime data readiness between 2 apps?
any suggestion will be highly appreciated