Hello Friends,
In Analytics world, the most important criteria to make your footprint on the world scale is to have the ability to store and hold humungous volume of data and to have speed of thought response time.
I would talk about Performance part of the ASO cube in this article today (both from books and from experience)
First some tips directly from book: Although there are very limited tuning which you can do on ASO cube based on its architecture, still there are some good ways to better the performance of the cube:
In Analytics world, the most important criteria to make your footprint on the world scale is to have the ability to store and hold humungous volume of data and to have speed of thought response time.
I would talk about Performance part of the ASO cube in this article today (both from books and from experience)
First some tips directly from book: Although there are very limited tuning which you can do on ASO cube based on its architecture, still there are some good ways to better the performance of the cube:
Compression: Default : Account Dimension. Can be changed to any other Dimension having Dynamic Hierarchy
A good candidate for a compression dimension is one that optimizes data compression while maintaining retrieval performance
Dimensions with a large number of stored level 0 members do not perform well if tagged Compression as it would require more time to dynamically aggregate upper level members reducing the performance of the retrieval.
You can check below details from Administration Services Console for an ASO application:
Average bundle fill: The average bundle fill is the average number of values stored in the groups. It can range from 1 to 16, best being 16. Choosing a compression dimension that has a higher average bundle fill means that the database compresses better.
Average Value length: The average value length is the average storage size, in bytes, required for the stored values in the cells. It can vary between 2 bytes and 8 bytes with 2 bytes being the best.Rounding the data values to no more that two digits after the decimal point can reduce the average value length, improving compression
Expected Level 0 size:This field indicates the estimated size of the compressed database. A smaller expected level 0 size indicates that choosing this dimension is expected to enable better compression
Compacting Outline: When you delete members from an aggregate storage outline, the corresponding records of members in the outline file (.otl file) are marked as deleted but remain in the file. The outline file continues to grow as members are deleted and added. You can minimize the outline file size by compacting the file to remove the records of deleted members. Compacting the outline file causes Essbase to restructure the outline and can take place only when no other users or processes are actively using the database
Creating Aggregate View: This helps in improving the retrieval time of ASO Essbase queries. This requires to run aggregation process (single step for combination of aggregation selection and aggregation build) to pre-aggregate database based on:
- Most queried user selection (use query enable feature)
- Query Hint
Aggregate view with following options:
1) Allow Essbase to automatically generate aggregate view based on automatic data analysis and outline hint
- Best for smaller cubes having about 1 million cells
- For larger cubes the Sample Cell values can be adjusted using Essbase.cfg parameter : ASOSAMPLESIZEPERCENT
- Increasing this parameter has its own cons as the amount of data which needs to be analysed would be too high with may reduce the time of aggregation
2) Based on User Query by enabling query tracking
3) Manually creating Aggregate view - This required understanding of data and type of queries which users would be running more frequently
Now time for some real life experiences
Trickle Feed: ASO cubes has the ability to load multiple data files in parallel using Trickle Feed feature. This feature allows allocation on buffer ids to each data file and then create slice by using the existing buffer ids and committing the slice by merging it into the cube datafiles.
Virtual Cube:If you are dealing with large volume of data, we can design the cube by grouping the cubes in number data categories and create respective ASO cubes and can build a transparent partitioned virtual cube on top of multiple data categories based ASO cubes. This helps in improving the retrieval time drastically.
Transaction based outline: Create transaction based outline and incremental data load for ASO reporting cube
Hope this helps many of the developers in building great performing ASO applications
