ASO Essbase Cube Performance - Tips and Tricks

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:

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

Unix command to list filenames without details with one file in each line

Hello,

This is one of the feature which I found for listing files in UNIX / LINUX without showing details of the files (e.g.: owner, file size etc - typically ls -l) but I wanted to have each file showing in one line sorted in order of time.

I googled a lot but could not find any good response. Then I went into each and every option of ls command in the UNIX manual and found one option -1 (numeric one) which lists the file names with each file name in one line.

My requirement was to list files with only names in order of last created / modified.

Following command worked perfectly for me:

ls -t1

Hope this helps.

Keep sharing

Creating Partition for Essbase applications

Hello,

I am sure you all have created partitions for Essbase applications to cater to specific business requirement using EAS. However, last time when I was trying to create a partition, I was continuously getting an error message while validating the partition :

"Validation was not successful. Please see the errors".

However it does not show any errors in the "Messages" panel. Also I could not find any errors in application / essbase server log (If anyone know where the error messages are stored for partitions, please let me know).

Although I was doing everything right while creating the partitioning - Areas section, mapping section, cell count... everything was correct, still I was getting above error.

While doing research on this error, I found that, the Essbase server which was configured with EAS was showing "EssbaseCluster-1" - This is the default connection which gets created while configuring the Essbase Server with EAS.

Just for my curiosity, I added another connection for the same Essbase server with the host name ("Server Name:Port"). When I did this and tried creating the same partition, it went through.

Again, this is from my experience. Hence please do your own investigation/ research before you recommend this.

Hope this helps we all.

Using ESSUTF8 Utility for Unicode ASO Cube Dim Build

Hello,

It has been long that I have shared any article / topic in my blog. Actually I was too busy with my current projects / assignment.

However, few days back I found an interesting utility while building UNICODE DIMENSIONS for Essbase ASO cube.

When you use Essbase Studio to build Unicode ASO cube dimensions, the in-build features of Essbase Studio take cares of building Unicode ASO Cube dimensions while connecting Relation Database with Essbase Cube.

However, when you are suppose to build Unicode ASO cube dimensions using ASCII format files via MaxL command, you are in trouble. You get dimension build errors (Command Failed. Return Status = 1054036).
However the same MaxL runs perfectly on the EAS MaxL editor.


There are few ways to make this run using MaxL on command.

Option 1 : Create the MaxL using EAS MaxL editor. While saving the MaxL into a file, check the "Unicode" check box which appears at the bottom of the Save File option window. Use this file to run on MaxL Command

Option 2: Use ESSUTF8.sh /ESSUTF8.exe utility stored at the path arbor path(Essbase Server Path)/bin

essutf8 -b -s  

and use this MaxL file to run on MaxL command. you can find more details of using ESSUTF8 utility in the link

Hope this helps.

Happy sharing

Issue: MaxL and Esscmd not opening from windows command line

Hi,

I am currently busy installing Oracle Hyperion EPM product suite v11.1.2.1 on VMware machines and the issues which I face while installation, I just thought of sharing here on this blog.

After installation of Essbase Server on one of the VMware machine, I found that I cant open MaxL or esscmd command from windows command line. I found that ARBORPATH and ESSBASEPATH environment variable were not set. I set it for the seesion and it worked. It can be set in the Environment Variable and then MaxL and Esscmd can be executed from windows command line.

Hope this helps you all

Keep Sharing

OraHyperion