Best practices and performance tuning for using Hybrid BSO in EPBCS

I recently presented at ODTUG KSCOPE19 in Seattle. Thanks a ton to ODTUG content selection team for giving me this opportunity to present at such a great conference! I presented along with David DeLozier on Best Practices and Performance Tuning for using Hybrid BSO in EPBCS. I had a great audience with lots of questions being asked. Sincere thanks to Oracle’s Mark Rinaldi for attending this session and contributing to answer questions asked by the attendees on Hybrid BSO.

The Hybrid BSO functionality is not automatically available on EPBCS for existing customers.  It may be enabled by logging an SR with Oracle.  Note that enabling Hybrid BSO requires a fundamental change in the Essbase binaries and hence the existing application will be deleted and the instance updated to the new Essbase with Hybrid BSO Support. Ensure all backups are taken prior to enabling Hybrid.  New Enterprise customers will have Hybrid BSO enabled by default. Just enabling Hybrid BSO mode is not enough, as you would need to update your sparse dimensions and make all or selected upper level dynamic to make use of the Hybrid benefits.

The Hybrid BSO thresholds described in this blog may change as part of Oracle updates in the future.

I have summarized my presentation below:

What is Hybrid BSO?

• Hybrid BSO currently available on on-premise Essbase
• Hybrid BSO now being extended to EPBCS with certain limits or thresholds to maintain acceptable performance in the cloud.
• Supports ASO features like dynamic aggregation i.e. retrieve data at upper levels of sparse dynamic members without aggregating cube
• Supports BSO features for calculation functions
• No upper level data means cube refresh is faster (when full dynamic model is used)
• Reduces application size as only level zero data is present (when full dynamic model is used)
• Existing application can be converted to Hybrid BSO as long as the application passes the best practices (threshold limits) validation

BSO, ASO and Hybrid BSO quick comparison

Getting Started with Hybrid BSO:

• All new EPBCS instances (new SKU subscriptions) will have Hybrid BSO enabled by default. You can choose not to use Hybrid by keeping all sparse members as stored.
• All BSO cubes in the application are enabled for Hybrid BSO mode.  This is an application level setting.
• All Framework (modules) objects have been reconfigured to work with Hybrid BSO.
• Existing EPBCS applications can be converted to Hybrid BSO mode (You need to convert a PBCS app to EPBCS before attempting Hybrid Conversion)

Validation process before Hybrid Conversion:

• Validation for an existing application requires that a significant number of thresholds be met prior to conversion to Hybrid BSO
• Validation thresholds vary for the different delivered cubes and focus on items such as block size, added members, and added rules
• Validations are noted as either Errors or Warnings for each cube
• Items that are noted as Errors must be corrected to proceed with conversion
• Cube validation against set practices (thresholds) performed before converting existing app to Hybrid Mode
• Address any Errors to allow the enablement to continue.  Warnings will allow enablement but may be addressed as well.

Snapshot below shows the best practices validation during Hybrid Conversion. You can download the best practices validation log by clicking on the green arrow button.

Once the application is converted to Hybrid BSO, best practice validation is enforced during database refresh process. Sample screenshot is given below:

Framework (Module) Changes for Hybrid Mode

The following changes are done during Hybrid BSO conversion for the out of box modules like capex and workforce. No manual intervention is necessary as updates are automatically seeded by the Hybrid conversion process.

• All BSO cubes part of the framework are enabled for Hybrid
• Specific sparse dimension parents are made dynamic
• Business rules are updated to remove aggregations for dimensions which are now made dynamic
• Member formulas where applicable are updated based on conversion

Framework Update Examples

Workforce – Employee Dimension

Capex – Asset Class Dimension

Entity – No updates done by Hybrid Conversion process to this dimension

Tuning Considerations for Hybrid BSO

• Experiment with different dynamic options like full dynamic sparse dimensions or partial dynamic for large dimensions.
• Partial dynamic can include some sparse dimensions as stored or certain levels of a large sparse dimension as dynamic
• Aggregate and store certain large dimensions if required, keeping the rest dynamic
• Dynamically calculate as much as possible using the natural hierarchy and monitor for query performance
• Control block size where possible by controlling member additions in dense dimensions
• Order dimensions – dense (dynamic, non formula), dense (dynamic with formulas), sparse (stored), sparse (dynamic, non formula), sparse (dynamic)
• SmartView retrieval time will likely increase in cases of full dynamic Hybrid BSO design. Use BSO tuning best practices, experiment with full dynamic or partial dynamic combinations

Key Takeaways

• Because of best practice validations (threshold limits) , converting an existing application to Hybrid BSO may not be easy
• Factor in Hybrid deployment during design so threshold limits are factored during application design phase
• Hybrid mode would be easy to implement ground up than converting an existing app to Hybrid that would require fundamental updates to the database to pass best practice validation
• There is a threshold limit on block size. Consider expected growth of dense member additions
• Threshold limits on additions of new business rules need to be factored in future enhancements to application functionality
• Tuning will be application specific. Need to experiment with different combinations of sparse dynamic setups
• Potential challenges around calculations like allocations, summary data Xwrites to target cubes. Need to work around these challenges. Explore use of Groovy for such cases
• Test the application thoroughly especially calculations and member formulas
• Conversion to Hybrid BSO for existing applications cannot be rolled back. 

Summary

Hybrid BSO will have significant benefits that of reduced application size and cube refresh times. Reduced application size will depend on Hybrid mode of deployment; full dynamic model or partial. Because of dynamic nature, forms can now show aggregated data immediately after data entry if the dimensions on the form are made dynamic as part of Hybrid conversion. Hybrid BSO is a great feature that must be explored to see if it fits your application. I see a great deal of benefit to using this feature. The tuning will be application specific as each application’s setup will be unique in terms of size, calculation complexity, dense sparse settings etc.