EPM Tech Tips · Planning · 08
When a form takes thirty seconds to open, look at the form before the server. Usually it asks Essbase for far more cells than anyone needs, or forces dynamic calculations across large sparse hierarchies. Checking the causes in a fixed order finds the one that matters before you redesign the cube.
Before you start
Reproduce and time it. Open the form with the user's login, POV and page selections, and time it. Then check the Activity Report for that day (Application › Overview › Activity Reports) and look for the slowest user interface requests. If the form appears there, you have a baseline for each fix.
Pick your route. If the form is slow for every POV, start with steps 1 to 3. If it is only slow for certain entities, scenarios or parents in the POV, go straight to step 5, then step 4.
Step 1 · Is a rule running before the form loads?
What happens. A business rule set to Run Before Load executes every time the form opens. A Groovy rule set to Run After Load also runs on every open. If either rule processes a wide range, the user waits for the rule, not the grid.
How to check.
Open the form definition and go to its Business Rules tab.
Look for rules with Run Before Load selected, and Groovy rules with Run After Load selected.
Open each one in Calculation Manager and check its scope. A FIX on a whole dimension or a full AGG is a red flag on a load rule.
Temporarily clear the load option in a test environment and time the form again.
Fix. Move heavy calculations to Run After Save, to a scheduled job or to a menu action. If the rule must run on load, narrow it to the members in the form's POV and page.
Step 2 · How many cells is the form asking for?
What happens. The number of cells is rows × columns before suppression. Two dimensions with descendants on rows multiply each other: 1,000 members in each gives one million rows before Essbase removes a single empty one. If a form crosses the data entry cell limit, Planning refuses to load it with the message "Unable to load the data entry form as the number of data entry cells exceeded the threshold". That limit cannot be raised.
How to check.
Open the form layout and count the member selections on rows and columns. Watch for Descendants or IDescendants of a large hierarchy.
Multiply the members on each axis to estimate the raw cell count.
Ask the user which slice they work on.
Fix. Move the largest row dimension to the page, so the user picks one member at a time. Split a large form into smaller forms with a single business purpose. Send search and drill needs to an ad hoc grid or a report. If the form hits the cell threshold, Suppress missing blocks can bring it under the limit, because missing blocks are not counted. Empty blocks are counted, so clear them if allocations don't need them. Clearing affects every user, so keep the scope narrow and test it outside production first.
Step 3 · Is suppression set the right way?
What happens. Suppress missing data hides empty rows only after the query has run, so the query is no smaller. Suppress missing blocks is a row option that skips empty blocks at query time. Oracle notes that it can make the form slower when few or no rows are suppressed, so test before and after. Side effects: some suppressed blocks may ignore Dynamic Calc members, and row members may not indent.
How to check.
Open the form layout and select the row axis. Look at the grid properties for Suppress missing data and Suppress missing blocks.
Open the form with the user's POV and compare the rows before suppression with the rows that display.
Time the form with Suppress missing blocks on and off in a test environment.
Fix. Turn on Suppress missing blocks on the rows when most rows come back empty, which is common when large sparse dimensions are on the rows. Leave it off when nearly every row holds data. Retest as data grows.
Step 4 · Are sparse dimensions on the rows and columns?
What happens. Dense dimensions live inside each block, so putting them on rows and columns is cheap. Each sparse member combination is a separate block, so a sparse dimension on an axis forces Essbase to read many blocks per screen. Oracle recommends only dense dimensions on rows and columns for forms with Enable Autosave, and the same logic applies to load time.
How to check.
Go to Navigator › Create and Manage › Dimensions and check whether each dimension is dense or sparse for the cube the form uses.
Compare that list with the form layout. Mark every sparse dimension on rows or columns.
Check the POV and page. Sparse dimensions there are fine, as long as they point to a single member.
Fix. Put dense dimensions (usually Account and Period) on rows and columns, and sparse ones (Entity, Product) on the POV or page. If a sparse dimension must be on an axis, keep the selection narrow.
Step 5 · Are Dynamic Calc members slowing the form?
What happens. Every Dynamic Calc member in the grid is calculated at retrieval. On dense dimensions this is normal and usually fast. On sparse parents with many children, the calculation reads every child block on each open. Hybrid BSO cubes aggregate dynamic sparse parents much faster, but very wide or deep dynamic hierarchies still cost time. Oracle also warns that level-0 sparse members tagged Dynamic Calc without a formula slow forms and make the query fall back from suppressing missing blocks to suppressing missing rows.
How to check.
List the Dynamic Calc members that appear in the form, on every axis and in the POV.
For each sparse parent, count its children and levels.
Look for level-0 members set to Dynamic Calc with no member formula.
Confirm whether the cube is hybrid. A Service Administrator can disable Hybrid Mode from Application › Overview › Actions (Custom and FreeForm applications only, if your subscription supports it), so check with your team whether anyone has done so. Do not change this setting to fix one form.
Fix. Set level-0 members without a formula back to Store. In large applications, store selected intermediate sparse parents instead of making the whole hierarchy dynamic, and aggregate them in a batch rule. Stored parents only update when the aggregation runs, so schedule it after every data load and tell users. The change needs a database refresh. Test it in a non-production environment first. Keep upper-level dense members as Dynamic Calc.
Step 6 · Is the form itself heavy?
Avoid asymmetric forms where each column has a different member combination; the hybrid engine runs them as several queries in sequence. Use account annotations sparingly, since they add load time. The first open after maintenance loads caches, so time the second open.
Quick checklist
Heavy rule on load (Run Before Load, Groovy Run After Load) · Form › Business Rules
Raw cell count and largest dimension on page · Form layout
Suppress missing blocks vs suppress missing data · Form layout › Grid properties
Sparse dimensions on rows or columns · Dimensions › Dense or sparse, then form layout
Dynamic sparse parents and Dynamic Calc level-0 members · Dimension Editor › Data Storage
Asymmetric layout, annotations, cache · Form layout and a second timed open
Pedro San Martín · Founder & Principal, Asher & Company
