Using Essbase Query Governors with Forms Containing User Variables
By Keith Rosenthal on Mar 08, 2013
Many Planning customers today are designing forms containing user variables. User variables allow the user to customize the information presented on a form based on the user’s security. In addition, the number of forms maintained in the application is reduced since multiple versions of the same form having different dimension members do not need to be created.
The issue with forms containing user variables is that form retrieval times can vary considerably depending on the dimension member selected, especially when the user variable is defined as a row dimension member. Since the user variable can be selected based on the user’s read and write access, choosing a member from the upper levels of the dimension hierarchy can increase the number of potential rows significantly if the user variable is defined on a large sparse dimension. As a result, performance can degrade significantly if large numbers of users having selected a high level user variable are opening the form concurrently.
You can evaluate the impact of selecting an upper level dimension member by running Tools|Diagnostics in Planning 22.214.171.124 (or MassGridStatistics in earlier releases – see http://blogs.oracle.com/pa/entry/planning_11_1_2_massgridstatistics for additional information) for a lower level member, middle level member and higher level member. The diagnostics will show the form load times and the load time increase as higher and higher level members are selected.
Although users may have the ability to select a high level dimension member for the user variable, a high level selection is typically not necessary as far as data entry is concerned. Administrators can limit the load times and performance impact of a high level selection by adding either the QRYGOVEXECTIME or QRYGOVEXECBLK query governor settings to the essbase.cfg file. When loading a form, QRYGOVEXECTIME will time out the form loading process if the specified retrieval time in Essbase is exceeded. QRYGOVEXECBLK will time out the form loading process if the number of data blocks is exceeded.
Note that adding the query governor settings will only impact activity on the Essbase server. Activity on the Planning server will still occur to generate the MDX query and set up the grid, so the time out message will take slightly longer to generate. The load on the Planning server could be decreased by adjusting the security and decreasing read access to upper level dimension members. This alternative, however, might prevent users from opening other forms at a higher dimension level, which might not be feasible.