Application Design for EPM Planning
Part II: Calculation Manager Rules and Hybrid BSO
Thank you to Mark Rinaldi, Oracle EPM Product Management, for the presentation on this topic. You can also watch the recorded webinar.
To ensure high performing applications, review the best practices in writing calc scripts and avoid common mistakes when creating rules.
The
presentation covers the following topics with examples for each scenario.
- Rules Best Practices; including but not limited to:
- Example Rule – Multiple passes through the database
- Example Rule – Single pass through the database
- Example of a Rule that copies & creates 0’s
- Removing Zero’s from BSO cube
- Benefits of following Best Practices
- Best Practices for Hybrid BSO; including comparison between BSO & Hybrid BSO
- Best Practices for ASO
- Monitoring Application
Rules Best Practices
- Poorly written rules have a major impact on all aspects of an application.
- Can cause block contention which has a dramatic impact on End User Rule Performance
- Increase data on disk or application size which impacts rule performance & increases AMW time
- Rule performance cannot be gauged without concurrency.
- Rule performance cannot be gauged with limited data in application.
Top 10 Best Practices for Rules
- SET COMMANDS
- Do not use “SET CREATEBLOCKONEQ ON” & “SET CREATENONMISSINGBLK ON” at the top of the rule
- Do not use administrative type commands like “SET CLEARBLOCK EMPTY” in end user rules as it requires a restructure
- Avoid or test the rules using “SET CALCTASKDIMS”. Essbase typically does this automatically – best to let Essbase decide
- The Data Copy rule should include “SET COPYMISSINGBLOCK OFF” to prevent copying empty blocks within the Fix.
- Block Creation should be done using either Datacopy or Sparse Member assignment. Functions (@createblockoneq and @createblock) are very expensive and should be used as a last resort inside limited Fix statement.
- Missing dimension references in fix (e.g. in Data Copy Rule) can waste processing time and increase contention and create unnecessary blocks for all levels of the missing dimensions.
- Remove parallel calc in Business Rules associated to Forms. CALC Parallel or Fix Parallel should be used only with administrative/batch rules.
- Creating unnecessary zero’s will lead to block/data explosion. Carefully review business logic & add necessary if conditions to check for zero’s. Convert zero’s to #missing.
- Eliminate multiple passes on the same blocks. Instead set a proper outer fix and move in and out as necessary. Combine “If” statements instead of If’ing & re’Ifing on the same intersections.
- Avoid using Cross dimensional references on the left hand side of an equation. This has a performance impact.
- Dimensions should be aggregated in order of creation of the most blocks to the least blocks in the script e.g. Agg (1st Most Blocks Dimension, 2nd Most Blocks Dimension, 3rd Most Blocks Dimension). Agg is faster than Calc Dim and is a preferred mechanism for aggregation. Aggregation using @ancestors in end user rules all the way to the top of the dimension can lead to block contention.
- Use RTP’s instead of creating multiple rules with same underlying logic. More rules means more maintenance.
- Use Templates for breaking down & reusing business logic. However, templates should not be fully functioning rules with Fix, EndFix. The rule combining various templates should have proper outer fix and move in & out of smaller pieces as necessary.
Calculation Manager Diagnostics
- Run Errors and Warnings before deploying rule
- Provides information on
- # of passes thru the database
- Gives warnings
- Information on number of blocks and if dimensions are missing
- Rules need to be optimized accordingly
Why Hybdrid BSO?
- Hybrid BSO provides multiple options for configuring/tuning applications
- Application size reduces significantly with Hybrid BSO because of dynamic sparse dimensions which has positive impact on performance & usability
- Hybrid BSO is definitely better than BSO but does not replace ASO for reporting purpose
- Best practices framework monitors application health & provides advance warnings for taking corrective actions for ensuring Optimal Application Performance
Best Practices for Hybrid BSO
All BSO Best Practices apply to Hybrid BSO. However, there are some new considerations as well.
- Making all sparse dimension parent members dynamic can be considered as an option only for small size application.
- A mixed approach with some sparse dimension parent members being store or some intermediate parent members in sparse dimensions being store gives better performance for large size applications.
- For very large-sized applications with large number of reporting dimensions and instantaneous aggregation/reporting requirements, Hybrid BSO- ASO with Smart Push & Aggregate Views in ASO can be leveraged.
- Leaf level members in sparse dimensions should not be made dynamic especially when there is no member formula associated with the member. This can have adverse impact on form performance and also make the Essbase query run as suppress missing rows/columns instead of suppress missing blocks.
- Query Solve Order can be set per dimension/member for ensuring accurate results and tuning the performance of calculation scripts (aggregate before calculate wherever possible). The higher the solve order setting for a member, the lower in the order the member is calculated. The following is the default solve order value:
- Stored members 0
- Sparse dimension 10
- Sparse dimension – Two Pass 20
- Dense dimension – Account 30
- Dense dimension – Time 40
- Dense dimension 50
- Dense dimension – Two Pass Account 60
- Dense dimension – Two Pass Time 70
- Dense dimension – Two Pass 80
- Attribute dimension 90
- Form/Report Layout Symmetric vs Asymmetric - Asymmetric grids are broken into multiple Symmetric Grids by Hybrid Query Engine thereby affecting query performance. This is due to the serial nature of asymmetric report processing. Avoid creating Asymmetric forms.
Best Practices Framework
The following best practices are monitored by the framework:
- Block Size
- Number of Blocks
- Number of Dense Dimensions
- Max number of children under any dynamic/store parent
- Parents with single children
- Level 1 and above not set to dynamic calc/Label only in Dense dimensions.
- Usage of dynamic x-refs
- Percentage Customization
Best Practices for ASO
Optimizing data retrieval from ASO:
- Merge Data Slices & Remove Zero’s
- Compact Outline
- Build optimal number of Aggregate Views
- Reviewing Dimension Hierarchy Types
Monitoring Application
The Activity Report is automatically generated for each day and it enables Service Administrators to understand application usage.
- Identifies Calculation Scripts and User Requests that impact application performance
- Compare service usage and performance from past reports
- The activity report is available from the UI: Application->Overview->Activity Reports.
Watch the recorded webcast on Essential Tools and Resources for Managing Your EPM Applications.
Other Sessions Include the following topics:
===============================================================================
References:
