Introduction to Database Calculations

This chapter describes how to calculate a database. It also explains the concept of calculating a multidimensional database.

This chapter includes the following sections:

• Understanding Database Calculations
• Calculating a Multidimensional Database
• Setting the Default Calculation
• Calculating a Database
• Considering Security

Understanding Database Calculations

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:

• You enter regional sales figures for a variety of products. You calculate the total sales for each product.
• 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.
• Your database contains regional sales figures and prices for all your products. You calculate what happens to your total profit if you increase the price of one product in one region by 5%.

Hyperion Essbase offers two ways that you can calculate a database:

• Outline calculation
• Calc script calculation

Which way you choose depends on the type of calculation that you want to do.

Outline Calculation

Outline calculation is the simplest method of calculation. Hyperion 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 25-1 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 25-1: Relationship Between Members of the Market Dimension

Figure 25-2 shows the Scenario dimension from the Sample Basic database. The Variance and Variance % members are calculated using the formulas attached to them.

Figure 25-2: 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

Calc script calculation is the second method of calculation. Using a calc (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 calc script contains a series of calculation commands, equations, and formulas. For example, the following calc script increases the actual marketing expenses in the New York region by 5%.

Figure 25-3: Calc Script Editor

For more information on calc scripts, see Developing Calc Scripts.

Calculating a Multidimensional Database

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 25-4: Calculating a Multidimensional Database

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.

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 25-5: Three-Dimensional Database

From the following diagram, you can see that when you refer to Sales, you are referring to a slice of the database containing eight Sales values:

Figure 25-6: Sales, Actual, Budget Slice of the Database

When you refer to Actual Sales, you are referring to four Sales values:

Figure 25-7: 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 25-8, the cell containing the data value for Sales, Jan, Actual is shaded.

In Hyperion 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 25-8: Sales, Jan, Actual Slice of the Database

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

Hyperion 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. Hyperion Essbase continues cycling through the database until it has calculated Margin% for every combination of members in the database.

For more information on database calculation order, see Defining the Calculation Order.

Setting the Default Calculation

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 calc 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 calc script to work with settings defined in the Calc Options group of the Database Settings dialog box, you must set the calc script as the default calculation.

1. Select Database > Set Default.

   Hyperion Essbase displays the Set Default Calc dialog box.

2. Select Use Calc Script Object.
3. From the list of available calc scripts, select a calc script.
4. Click OK.

	You can use alter database in MaxL or SETDEFAULTCALCFILE in ESSCMD to set the default calculation to an existing calculation. See the online Technical Reference in the DOCS directory for information.
	You can use the SETDEFAULTCALC command in ESSCMD to set a calculation string as the default database calculation. See the online Technical Reference in the DOCS directory for information.

Calculating a Database

You can calculate a database using any one of these tools:

• Hyperion Essbase Application Manager
• Hyperion Essbase Spreadsheet Add-in
• MaxL
• ESSCMD

This section includes information on calculating a database using Hyperion Essbase Application Manager, MaxL, and ESSCMD. For information on calculating a database from Hyperion Essbase Spreadsheet Add-in, see the Hyperion Essbase Spreadsheet Add-in User's Guide.

Running a Calculation

With Hyperion Essbase Application Manager, you can run the default outline calculation from the desktop.

1. In the server dialog box (in this case, Aspen), select the appropriate application and database.

   Figure 25-9: Server Dialog Box

   

2. From the Hyperion Essbase Application Manager menu, select Database > Calculate.

   Hyperion Essbase displays the Calculate Database dialog box.

   Figure 25-10: Calculate Database Dialog Box

   

   Under Database State, Hyperion Essbase indicates the current calculation state of the database:

   Calculation State	Description
   Calculation in progress.	Hyperion Essbase is currently calculating the database.
   Data values have been modified since the last calculation.	Data values have been changed in the database since the database was last calculated. The last calculation may have been an entire calculation of the database or a calculation of a subset of the database.
   Data values have not been modified since the last calculation.	No data values have been changed in the database since the database was last calculated. The last calculation may have been an entire calculation of the database or a calculation of a subset of the database.

3. Select "(Default)" from the list to run the default outline calculation. Or, select a calc script to run the calc script.

   The list includes only calc scripts to which you have security access. For information on Hyperion Essbase security privileges, see Managing Security at Global and User Levels.

4. Click OK.

   Hyperion Essbase calculates the database.

   Note:

   Hyperion Essbase displays a message while it is calculating the database. For lengthy calculations, you may want Hyperion Essbase to perform the calculations in the background. Use the Windows Alt + Tab key combination to switch to another Windows application. While Hyperion Essbase is calculating the database, other Hyperion Essbase Application Manager functions are disabled.

   You can use execute calculation in MaxL or the CALC, CALCDEFAULT, and CALCLINE commands in ESSCMD to perform calculations. See the online Technical Reference in the DOCS directory for information.

Canceling a Calculation

When you cancel a calculation, Hyperion Essbase does one of the following:

• Reverts all values to their previous state
• Retains any values calculated before the cancellation

How Hyperion Essbase handles the cancellation depends on your Hyperion Essbase Kernel Isolation Level settings. For more information on these settings, see Ensuring Data Integrity.

Considering Security

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 at Global and User Levels.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.