This chapter describes how to calculate a database. It also explains the concept of calculating a multidimensional database.
This chapter includes the following sections:
A database contains two types of values. It contains the values that you enter, which are called input data, and the values that have been calculated from the input data.
Consider the following examples:
Small differences in the precision of cell values may occur between calculations run on different platforms, due to operating system math library differences.
Essbase offers two ways that you can calculate a database:
Which way you choose depends on the type of calculation that you want to do.
Outline calculation is the simplest method of calculation. Essbase bases the calculation of the database on the relationships between members in the database outline and on any formulas that have been associated with members in the outline.
For example, Figure 361 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 361: Relationship Between Members of the Market Dimension
Figure 362 shows the Scenario dimension from the Sample Basic database. The Variance and Variance % members are calculated using the formulas attached to them.
Figure 362: Calculation of Variance and Variance %
For more information on creating database outlines, see Creating and Changing Database Outlines.
When you design an overall database calculation, it may be more efficient to calculate some member combinations when you retrieve the data, instead of pre-calculating the member combinations during the regular database calculation. You can use dynamic calculations to calculate data at retrieval time. For more information, see Dynamically Calculating Data Values.
Calc 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 calculator script contains a series of calculation commands, equations, and formulas. For example, the following calculator script increases the actual marketing expenses in the New York region by 5%.
Figure 363: Calc Script Editor
For more information on calculator scripts, see Developing Calculation Scripts.
To understand the nature of multidimensional calculations, you need to know some basic multidimensional concepts.
To illustrate these concepts, consider the following, simplified database:
Figure 364: Calculating a Multidimensional Database
The database has three dimensions: Accounts, Time, and Scenario.
The Accounts dimension has four members:
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.
An intersection of members (one member on each dimension) represents a data value. Our example has three dimensions; therefore, the dimensions and data values in the database can be represented as a cube:
Figure 365: Three-Dimensional Database
As shown in Figure 366, you can see that when you refer to Sales, you are referring to a slice of the database containing eight Sales values.
Figure 366: Sales, Actual, Budget Slice of the Database
When you refer to Actual Sales, you are referring to four Sales values:
Figure 367: Actual, Sales Slice of the Database
To refer to a specific data value in a multidimensional database, you need to specify its member on each dimension. A data value is stored in a single cell in the database. In Figure 368, the cell containing the data value for Sales, Jan, Actual is shaded.
In Essbase, member combinations are denoted by a cross-dimensional operator. The symbol for the cross-dimensional operator is ->. So Sales, Jan, Actual is written Sales -> Jan -> Actual.
Figure 368: Sales, Jan, Actual Slice of the Database
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:
For more information on database calculation order, see Defining the Calculation Order.
By default, the default 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.
However, you can specify any calculator 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. Also, if you want a calculator script to work with settings defined in the Calc Options group of the Database Settings dialog box, you must set the calculator script as the default calculation.
To set the default calculation in Application Manager:
Essbase displays the Set Default Calc dialog box.
Tip: You can set the default calculation outside Application Manager.
Tool |
Instructions |
For More Information |
---|---|---|
You can calculate a database using any one of these tools:
This section includes information on calculating a database using Application Manager, MaxL, and ESSCMD. For information on calculating a database from Spreadsheet Add-in, see the Essbase Spreadsheet Add-in User's Guide or Essbase Administration Services Online Help.
With Application Manager, you can run the default outline calculation from the desktop.
To calculate a database from Application Manager:
Figure 369: Calculate Database Dialog Box
In Database State, Essbase reports the current calculation state of the database. See Table 33 for a descriptions of each state.
The list includes only calculator scripts to which you have security access. For information on Essbase security privileges, see Managing Security for Users and Applications.
Essbase calculates the database. Essbase displays a message while calculating the database.
Tip: For lengthy calculations, use the Windows Alt + Tab key combination to switch to another Windows application. While Essbase calculates the database, other Application Manager functions are disabled.
Tip: You can perform calculations without Application Manager:
Tool |
Instructions |
For More Information |
---|---|---|
To stop a calculation before Essbase completes it, click the Cancel button in the Calculate Database dialog box.
When you cancel a calculation, Essbase does one of the following:
How Essbase handles the cancellation depends on your Essbase Kernel Isolation Level settings. For more information on these settings, see Ensuring Data Integrity.
Essbase now supports parallel calculation in addition to the default serial calculation. This section explains the difference between parallel and serial calculation and explains how parallel calculation works in Essbase.
Serial calculation, the default, means simply that all the steps in a calculation run on a single thread. Each task is completed before the next is started.
Parallel calculation means that the Essbase calculator can analyze a calculation, and, if appropriate, assign tasks to multiple CPUs (up to 4):
For more information about parallel calculation, including how to determine whether your OLAP Server should use parallel calculation, see Using Parallel Calculation.
In order to calculate a database, you must have calculate privileges for the database outline.
If you have calculate privileges, you can calculate any value in the database. With calculate privileges, you can calculate a value even if a security filter denies you read and update privileges. Careful consideration should be given to providing users with calculate privileges.
For more information on providing users with calculate privileges and on security filters, see Managing Security for Users and Applications.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |