About Block Storage Calculation

An Essbase database (cube) consists of values that its users enter, called input data, and values that are derived (calculated) from the input data. Block storage calculation can occur along the outline, based on member consolidation and formulas, and can also be induced by running batch calculation scripts.

As an example of input data versus derived data, assume that you enter regional sales figures for a variety of products, and calculate the total sales for each.

You enter the budget and actual values for the cost of goods sold for several products in several regions. You calculate the variance between budget and actual values for each product in each region.

The cube contains regional sales figures and prices for all products. You calculate what happens to total profit if you increase the price of one product in one region by 5%.

Essbase offers two methods for calculating a database:

Outline calculation
Calculation script calculation

The method that you choose depends on the type of calculation that you want to perform.

Note:

Small differences in the precision of cell values may occur between calculations run on different platforms, due to operating system math library differences.

Most computers represent numbers in binary, and therefore can only approximately represent real numbers. Because binary computers cannot hold an infinite number of bits after a decimal point, numeric fractions such as one-third (0.3333...) cannot be expressed as a decimal with a terminating point. Fractions with a denominator of the power of two (for example, 0.50) or ten (0.10) are the only real numbers that can be represented exactly. See IEEE Standard 754 for Floating-Point Representation (IEEE, 1985).

Outline Calculation

Outline calculation is the simplest calculation method for block storage cubes. Essbase bases the calculation on the relationships between members in the database outline and on any formulas that are associated with members in the outline.

For example, the image below shows the relationships between the members of the Market dimension in the Sample.Basic database. The values for New York, Massachusetts, Florida, Connecticut, and New Hampshire are added to calculate the value for East. The values for East, West, South, and Central are added to calculate the total value for Market.

Figure 16-1 Relationship Between Members of the Market Dimension

Hierarchy from the Market dimension illustrates how eastern US cities roll up to region East, and all the regions (East, West, South, and Central) roll up to Market.

The reason the child members of Market are added to get the parent values is that their member consolidation operators are set to (+).

The image below shows the Scenario dimension from the Sample Basic database. The Variance and Variance % members are calculated by using the formulas attached to them.

Figure 16-2 Calculation of Variance and Variance %

Hierarchy from the Scenario dimension, in which Variance has attached formula @VAR(Actual, Budget), and Variance % has attached formula @VARPER(Actual, Budget).

It may be more efficient to calculate some member combinations when you retrieve the data instead of calculating the member combinations during the regular database calculation. You can use dynamic calculations to calculate data at retrieval time. Refer to Dynamic Calculation of Data Values.

Calculation Script Calculation

Using an Essbase calculation script, you can choose exactly how to calculate a block storage database. For example, you can calculate part of of the cube, or copy data values between members.

A calculation script contains a series of calculation commands, equations, and formulas. For example, the following calculation script increases the actual marketing expenses in the New York region by 5%.

FIX (Actual, “New York”)
   Marketing = Marketing *1.05;
ENDFIX;

Refer to Develop Calculation Scripts for Block Storage Cubes.