Tuesday, January 20, 2009

Hyperion Essbase Knowledge base- Part 2

  1. What is the difference between FIX and IF statements? In what situations they are used and what are its pros and cons?

This is the favourite question, which everyone will be asking. If should be used for Dense dimensions and FIX for sparse dimensions.

Reason: If you FIX on a Dense dimension. Remember dense dimension member will be present in every block of essbase. so if you FIX on dense dimension you are asking to pull all blocks, so the purpose of "FIX" (where you want to pull only some blocks) is lost. If you FIX on sparse dimensions, only required blocks are pulled and calculation will be fast.

2. In what situation I cannot use AGG MISSING ON setting?

In some cases you may need to load data into parent level. Like allocations, for example you load expense data into east region and allocate the expenses to various cost centers under east region based on the headcount or otehr factors.

If you have AGG MISSING ON and when we aggregate the database. it will clear the parent value if there are no data for children. In such cases we don't use AGGMISSING ON option.

3. Give me an estimate of the largest Essbase db you have worked on (Number of dims, members, calcs, etc)
In my past experience, the biggest databases for BSO Essbase databases were below 10GB in most cases less than 5 GB. And number of dimensions are 8 to 10 with maximum 4 attribute dimensions. In most cases account and period are dense dimensions. number of members are around 5000 for entities. Any dimension members are below 10000.

4. What process do you follow when developing calc scripts?

a. Analyze the requirements documents.
b. Decide whether it is required to write calc script or can we create formulae in the
database outline.
c. Write the calculation script in the incremental method i.e. calculating some
members and test for optimization.

5 .What is the major difference between CALCDIM and AGG commands?
CALCDIM will only rollup only thru one dimensionor specified set of dimension and also calculates the formulae associated with the members
AGG will roll up all dims
ignoring formulae

Other questions you can find answers in DBAG.

What is the significance of data cache?
What are the advantages of attribute dimensions over shared members?
What is the optimum setting index cache and Data file cache?
What type of partitions does ASO support?
Differentiate between dynamic calc and intelligent calc?
How do you optimize data load in BSO?
How do you back up ASO database?
What members cannot be Dynamic Calc?
Have you worked on currency conversion?
What versions of Essbase have you worked on?


  1. Hi,

    Interesting blog and the answers are good.

    Thanks for posting.

    I am adding you to my blog list.

    Manohar Rana

  2. Here are my suggestions to improve your explanation:
    Make the smallest sparse dimension (the one with the least members) the first dimension and then next smallest as the second in the pyramid. In descending pyramid of sparse dimensions, when the essbase calculates it Fixes on the first small sparse dimension which reduces the number of blocks to retrieve immediately. When calc engine Fixes on the next smaller sparse dimension, it further reduces the amount of blocks to retrieve. By the time it Fixes on last dimension, even though it is largest, the previous Fixes have already reduced the number of blocks to select from. By the way, the smallest sparse dimensions are the ones that are non-aggregating – e.g. Scenarios and versions.

    1. Hi There ,

      I am quite impressed with the way you gave us a direction to use a hourglass sturcture .As per your comment non aggregating sparse dimension should be kept first (as they are smallest )right?
      But I have read in many of the blogs that first the smallest aggregating sparse dimension should be kept.. then the largset and then the non aggregating sparse dimension . So I got confused here ??