Calculating Essbase Databases

In This Section:

About Database Calculation

About Multidimensional Calculation Concepts

Setting the Default Calculation

Calculating Databases

Canceling Calculations

Parallel and Serial Calculation

Security Considerations

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

About Database Calculation

A database contains two types of values:

  • Values that you enter, called input data

  • Values that are calculated from input data

For example:

  • You enter regional sales figures for a variety of products. You 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 database 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%.

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

Note:

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).

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.

Outline Calculation

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

For example, Figure 106, Relationship Between Members of the Market Dimension 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 106. Relationship Between Members of the Market Dimension

This image shows an outline of the Market dimension that illustrates the relationship between members, as described in the text preceding the image.

Figure 107, Calculation of Variance and Variance % shows the Scenario dimension from the Sample.Basic database. The Variance and Variance % members are calculated by using the formulas attached to them.

Figure 107. Calculation of Variance and Variance %

This image shows an outline of the Scenario dimension, in which Variance and Variance % members have formulas attached to them, as described in the text preceding the image.

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. See Dynamically Calculating Data Values.

Calculation Script Calculation

Calculation script calculation is the second method of calculation. Using a calculation script, you can choose exactly how to calculate a database. For example, you can calculate part of a database 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;

See Developing Calculation Scripts for Block Storage Databases.

About Multidimensional Calculation Concepts

Figure 108, Calculating a Multidimensional Database, which is based on a simplified database, illustrates the nature of multidimensional calculations:

Figure 108. Calculating a Multidimensional Database

This image shows an outline with Accounts, Time, and Scenario dimensions, which is the basis for the discussion of multidimensional calculations that follows the image.

The database has three dimensions—Accounts, Time, and Scenario.

The Accounts dimension has four members:

  • Sales and COGS are input values

  • Margin = Sales - COGS

  • Margin% = Margin % Sales (Margin as a percentage of Sales)

The Time dimension has four quarters. The example displays only the members in Qtr1—Jan, Feb, and Mar.

The Scenario dimension has two child members—Budget for budget values and Actual for actual values.

The outline in Figure 108, Calculating a Multidimensional Database is illustrated as a three-dimensional cube in Figure 109, Illustration of a Three-Dimensional Database:

Figure 109. Illustration of a Three-Dimensional Database

This image shows a cube with three dimensions, as described in the text preceding the image

An intersection of members (one member on each dimension) represents a data value; a data value is stored in one cell in the database. To refer to a specific data value in a multidimensional database, you must specify each member on each dimension. In Essbase, member combinations are denoted by a cross-dimensional operator (->). Create the cross-dimensional operator using a hyphen (-) and a greater-than symbol (>). Do not include a space between the cross-dimensional operator and members.

The single cell containing the data value for Sales, Jan, Actual, as shown in Figure 110, Sales, Jan, Actual Slice of the Database, is written as Sales -> Jan -> Actual.

Figure 110. Sales, Jan, Actual Slice of the Database

This image shows a cube, illustrating a single cell, as described in the text preceding the image.

When you refer to Sales, you are referring to a slice of the database containing eight values, as shown in Figure 111, Sales, Actual, Budget Slice of the Database, which are:

  • Sales -> Jan -> Actual

  • Sales -> Feb -> Actual

  • Sales -> Mar -> Actual

  • Sales -> Qtr1 -> Actual

  • Sales -> Jan -> Budget

  • Sales -> Feb -> Budget

  • Sales -> Mar -> Budget

  • Sales -> Qtr1 -> Budget

Figure 111. Sales, Actual, Budget Slice of the Database

This image shows a cube, illustrating a slice of the database containing eight values, as described in the text preceding the image.

When you refer to Actual Sales, you are referring to four values, as shown in Figure 112, Actual, Sales Slice of the Database, which are:

  • Sales -> Jan -> Actual

  • Sales -> Feb -> Actual

  • Sales -> Mar -> Actual

  • Sales -> Qtr1 -> Actual

Figure 112. Actual, Sales Slice of the Database

This image shows a cube, illustrating a slice of the database containing four values, as described in the text preceding the image.

When Essbase calculates the formula “Margin% = Margin % Sales,” it takes each Margin value and calculates it as a percentage of its corresponding Sales value.

Essbase cycles through the database and calculates Margin% as follows:

  1. Margin -> Jan -> Actual as a percentage of Sales -> Jan -> Actual.

    The result is placed in Margin% -> Jan -> Actual.

  2. Margin -> Feb -> Actual as a percentage of Sales -> Feb -> Actual.

    The result is placed in Margin% -> Feb -> Actual.

  3. Margin -> Mar -> Actual as a percentage of Sales -> Mar -> Actual.

    The result is placed in Margin% -> Mar -> Actual.

  4. Margin -> Qtr1 -> Actual as a percentage of Sales -> Qtr1 -> Actual.

    The result is placed in Margin% -> Qtr1 -> Actual.

  5. Margin -> Jan -> Budget as a percentage of Sales -> Jan -> Budget.

    The result is placed in Margin% -> Jan -> Budget.

  6. Essbase continues cycling through the database until it has calculated Margin% for every combination of members in the database.

See Defining Calculation Order.

Setting the Default Calculation

By default, the calculation for a database is a CALC ALL of the database outline. CALC ALL consolidates all dimensions and members and calculates all formulas in the outline.

You can, however, specify any calculation script as the default database calculation. Thus, you can assign a frequently used script to the database rather than loading the script each time you want to perform its calculation. If you want a calculation script to work with calculation settings defined at the database level, you must set the calculation script as the default calculation.

  To set the default calculation, use a tool:

Tool

Topic

Location

Administration Services

Setting the Default Calculation

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDEFAULTCALCFILE

Oracle Essbase Technical Reference

Calculating Databases

If you have Calculation permissions, you can calculate a database.

When you use Administration Services to calculate a database, you can execute the calculation in the background so that you can continue working as the calculation processes. You can then check the status of the background process to see when the calculation is complete.

  To calculate a database, use a tool:

Tool

Topic

Location

Administration Services

Calculating Block Storage Databases

Oracle Essbase Administration Services Online Help

MaxL

execute calculation

Oracle Essbase Technical Reference

ESSCMD

CALC, CALCDEFAULT, and CALCLINE

Oracle Essbase Technical Reference

Spreadsheet Add-in

Calculating a Database

Oracle Essbase Spreadsheet Add-in User's Guide

Canceling Calculations

  To stop a calculation before Essbase completes it, click the Cancel button while the calculation is running.

When you cancel a calculation, Essbase performs one of the following operations:

  • Reverts all values to their previous state

  • Retains any values calculated before the cancellation

How Essbase handles the cancellation depends on the Essbase Kernel Isolation Level settings. See Understanding Isolation Levels.

Parallel and Serial Calculation

Essbase supports parallel and serial calculations:

  • Serial calculation (default): All steps in a calculation run on a single thread. Each task is completed before the next is started.

  • Parallel calculation: The Essbase calculator can analyze a calculation, and, if appropriate, assign tasks to multiple CPUs (up to four).

See Using Parallel Calculation.

Security Considerations

To calculate a database, you must have Calculate permissions for the database outline. With calculate permissions, you can calculate any value in the database, and you can calculate a value even if a security filter denies you read and update permissions. Carefully consider providing users with calculate permissions.

See User Management and Security in EPM System Security Mode.