Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter explains the basic concept of multidimensional database calculation and provides information about how to calculate an Analytic Services block storage database.
This chapter includes the following sections:
Note: Most computers represent numbers in binary, and therefore can only represent real numbers approximately. 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. For details, see IEEE Standard 754 for Floating-Point Representation (IEEE, 1985).
For information about calculating aggregate storage databases see Calculating Aggregate Storage Databases.
A database contains two types of values. It contains the values that you enter, which are called input data, and the values that are 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.
Analytic Services 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. Analytic Services 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 166 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 166: Relationship Between Members of the Market Dimension
Figure 167 shows the Scenario dimension from the Sample Basic database. The Variance and Variance % members are calculated by using the formulas attached to them.
Figure 167: Calculation of Variance and Variance %
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. For a comprehensive discussion of dynamic calculation, see Dynamically Calculating Data Values.
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;
For a comprehensive discussion of calculation scripts, see Developing Calculation Scripts.
For an illustration of the nature of multidimensional calculations, consider the following, simplified database:
Figure 168: 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, as shown in Figure 169:
Figure 169: Three-Dimensional Database
As shown in Figure 170, when you refer to Sales, you are referring to a slice of the database containing eight Sales values.
Figure 170: Sales, Actual, Budget Slice of the Database
As shown in Figure 171, when you refer to Actual Sales, you are referring to four Sales values:
Figure 171: Actual, Sales Slice of the Database
To refer to a specific data value in a multidimensional database, you need to specify each member on each dimension. A data value is stored in a single cell in the database. In Figure 172, the cell containing the data value for Sales, Jan, Actual is shaded.
In Analytic Services, 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 172: Sales, Jan, Actual Slice of the Database
When Analytic Services calculates the formula "Margin% = Margin % Sales," it takes each Margin value and calculates it as a percentage of its corresponding Sales value.
Analytic Services cycles through the database and calculates Margin% as follows:
For a comprehensive discussion of how Analytic Services calculates a database, see Defining Calculation Order.
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.
However, you can 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. Also, 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.
Use any of the following methods to set the default calculation:
Tool |
Topic |
Location |
---|---|---|
If you have Calculation permissions, you can calculate a database. When you use Essbase 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. For instructions, see "Calculating Block Storage Databases" in Essbase Administration Services Online Help.
Use any of the following methods to calculate a database:
Tool |
Topic |
Location |
---|---|---|
To stop a calculation before Analytic Services completes it, click the Cancel button while the calculation is running.
When you cancel a calculation, Analytic Services performs one of the following operations:
How Analytic Services handles the cancellation depends on the Analytic Services Kernel Isolation Level settings. For a description of these settings, see Understanding Isolation Levels.
Analytic Services now supports parallel calculation in addition to serial calculation. Serial calculation, the default, means that all steps in a calculation run on a single thread. Each task is completed before the next is started. Parallel calculation means that the Analytic Services calculator can analyze a calculation, and, if appropriate, assign tasks to multiple CPUs (up to 4).
For a comprehensive discussion of parallel calculation, including how to determine whether Analytic Server should use parallel calculation, see Using Parallel Calculation.
In order to calculate a database, you must have Calculate permissions for the database outline. If you have calculate permissions, you can calculate any value in the database. With calculate permissions, you can calculate a value even if a security filter denies you read and update permissions. Careful consideration should be given to providing users with calculate permissions.
For information on providing users with calculate permissions and on security filters, see Managing Security for Users and Applications.
![]() |