Oracle Essbase offers two database types namely aggregate storage (ASO) and block storage (BSO).
Is there any difference between ASO and BSO? Which one is better than other? Is there any rule for selection of database types? Are multiple hierarchies are better in ASO than BSO? Is OBIEE works well with ASO than BSO particularly for multiple hierarchies?
This blog is an attempt to answer some of the above questions regarding ASO and BSO Essbase database types
Block storage database typically perform best when they contain fewer than 10 dimensions and when measures and time dimensions are densely populated with data. Block storage models are typically implemented for financial models because they fulfill certain analysis needs that are not possible in the aggregate storage architecture.
Some of the typical analysis it would supports are
- Top-down planning which requires data to be loaded to upper-level members
- Pr-aggregation calculations, such as inter company eliminations or certain types of allocations
- Currency conversion
- Calculations based on input drivers such as Units X Price=Sales where Price is an input value
- Planning implementation (Planning requires block storage databases for back-end data storage)
The examples of business models which could be well suited for analysis in block storage database are as follows
- Sales forecasting (top-down planning and allocations)
- Profitability analysis (cost allocations across products and customers)
- Financial consolidations (currency conversions, intercompany eliminations)
Aggregate storage databases are optimized for sparse data sets that primarily require simple aggregation. All calculations are built into the database outline and calculated on demand, as users query the database. For improving query performance the aggregation can be calculated and stored in advance. Additionally incremental data loading and fast aggregations can provide near real-time analysis of transactional data.
The examples of business models which could be well suited for analysis in aggregate storage database are as follows
- Customer analysis – (many dimensions, millions of customers)
- Procurement analysis – (many products across many customers)
- Logistics analysis – (near real-time information on product shipments)
Data Analysis Needs is a King
Database design is ultimately driven by business need. Each business application has different requirements for scalability, performance, robustness and functionality.
Historically block storage database were used to implement comparatively dens data sets with a manageable number of dimensions. However, not all business models fit this formula. Now Essbase provides the aggregate storage database option for very sparse data sets with large numbers of dimensions and members.
Aggregate storage is not a replacement for block storage. It is different storage option to help to satisfy the analysis needs for any business requirement. The ultimate aim in any implementation is to derive and distribute analytic data as efficiently as possible.
As both block storage and aggregate storage are available the thumb rule is to choose aggregate storage application for operational data while block storage for financial data.
The General Guideline for selection of storage is as below.
- Have a large number of base dimensions (more than 10 ) – Aggregate Storage
- Have a extremely small batch calculation window or may applications to compute in a small window – Aggregate Storage
- Require combinations of very large dimensions – Aggregate Storage
- Need to use calculation scripts to calculate – Block Storage
- Require users to perform interactive top-down planning for sophisticated applications – Block Storage
By combining aggregate storage and block storage can offer huge analytical benefits along with large reduction in IT investment.
- With aggregate storage and block storage on a single platform, Essbase is a functionally rich solution. By connecting both application types through a transparent partition, one can leverage the functionality of both models
- Gain deeper business insight through the increased detail available – more dimensions and members in each dimension
- Faster load and aggregation times provide near real-time access to data
- Leverage each storage type individually as the business situation dictates