Hyperion Essbase allows one or more cubes (databases) to be grouped into applications. Different approaches have been used as to how many databases should be placed in an application, and questions exist as to the optimal configuration. This brief note documents the recommendation as of my experience , and reasons thereof.
Design Best Practices Recommendation:
The best practices recommendation is to define only one database per application.
The exception is for those applications that take advantage of the Currency Conversion option, which needs to create the conversion rates database in the same application as the database to be converted. The reasons for this recommendation are as follows:
1) Memory Utilization. All databases within an application run under that application’s ESSSVR process. With 32bit Essbase, a given ESSSVR process can only address from 2 – 3+ GB of memory, depending on the Operating System. Therefore, all databases within that application must ration this memory addressability across the caches that are used, the outlines that are loaded in memory, and other requirements (as documented in the Database Administrators Guide), must share this memory space. This can cause memory swapping, and the need to ration out the memory across the databases in a more deliberate manner. If these databases are within separate applications, you can better take advantage of your hardware resources.
2) Concurrent Dimension Builds. Dimension builds are resource intensive activities that may also require database restructures after they complete. When these databases are within an application, you do not get a full parallelization of these activities, and the additional memory needed for outline restructures (two copies of the outline reside in memory during the restructure) will exacerbate the situation highlighted in item 1 above. Therefore for highly volatile databases with similar batch processing schedules, you would receive better throughput on the system by placing these in separate applications.
3) Single Point of Failure. If many databases are in the same application, and the application is no longer able to be started, you would now have all the contained databases inaccessible until the issue is resolved. This can also happen if one of the databases within the application becomes unusable. Databases within separate applications are isolated from this event, and therefore impacts are lessened to the users.
4) Single Log for Application. Essbase writes information, warning, and error messages for databases into the application log. Each application has its own log. Having multiple databases writing to the same log can cause minimal contention lags, and also make monitoring of logs more tedious.
5) User Provisioning. When using Hyperion Shared Services it is not possible to grant different access to databases below one Application. Every Database within an Application will get the same User Access. (This is different from stand alone security). It is therefore easier (or in some cases overall possible) to grant the correct User rights if the Databases are in a one to one relation to their Application.
Although you can create multiple databases in an application in Essbase, it is only recommended for databases requiring use of the Currency Conversion option, or for databases that do not need additional memory and have non-conflicting maintenance schedules. In all other instances, it is highly recommended that implementations follow a one-to-one mapping of databases and applications.
Advanced Technology Team