Model size and efficiency

[andyhofer]

Hi all

I have a cube in planning (8.4.1) that is purely used to backup a years data. Dimensions used are :

Accounts - Hierarchical (2234)
Weeks - Hierarchical (53)
Measure (4)
Adjustment (3)
Version (4)
Year (1)
E-List

Total Size 5,684,928 per E-List, number of E-List - Hierarchical (203)

All this is stored in 1 cube, except obviously the E-List hierarchy but I am having problems as the application is struggling to reconcile, and sometimes fails. We added some additional E-List items and unfortunately the application would not reconcile.

My questions are:
1) Would it be more efficient to break out the one cube into a number of cubes in the same app. This would mean the app size is no different.
2) Would removing the hierarchies improve performance. I left the hirarchies in so that users can see Total Revenue, Profit, Functional Totals etc at a glance, if these are removed then the application will simply store values with little at a glance usability.

This application can be used to restore historical data to the rest of our planning solution, so having it to hand is really useful. Also, as we move forward, additional years need to be added, which will of course increase the year dimension. Adding 1 year will double the app size, which even I know is not a good idea. A solution for data storage of this type would be great if someone could provide one.

Thanks in advance.

Andy

[ericlfg]

Hey Andy,

To address your questions:

1. It is possible that breaking the one cube up into smaller cubes could help, especially if you intend to keep increasing the model size.
2. I believe removing the hierarchies could help, as it's reducing the total number of e-list items that need to be reconciled.  However, as you've indicated, it wouldn't make the app very user friendly.

Personally:

1. An upgrade to 10.1 may help as the upper limits have been increased for model sizes.  Additionally, the Reconcile sub system was completely redesigned in 10.1 with performance improvements and efficiency the primary goal.  I've seen models that won't reconcile in earlier versions reconcile in 10.1.

2. If you can't upgrade, then I would look into No Data Access tables and Cut-Down Models.  Implementing these access tables can reduce the total cells per e-list.  This would improve performance and give you more headroom to increase the size of the app.  Eg: I've seen 20 million cell per e-list applications reconcile without any problems.  A helpful tool in this case is the model review tool which can help pinpoint areas where performance could be improved.
https://www-304.ibm.com/support/docview.wss?uid=swg24024994

3. Rather than increase the size of the current archive application by adding additional dimension members, would it be practical to use the deployment facility to copy the application?  Let's say you're getting ready to roll a 2011 version application over to 2012.  You would take a model and data deployment export and import it back into the same environment under a different name. Eg: Profit and Loss ARCHIVE 2011, rather than possibly Profit and Loss 2011.

You would set security on the application for view level access only and the understanding would be that you wouldn't sync or GTP this application. 

Hope something in here helps.