Calculate Cubes in Cube Designer

You can create member formulas, create calculation scripts, and execute calculation jobs in cube designer.

• Create and Validate Member Formulas in Cube Designer
• Create and Validate Calculation Scripts in Cube Designer
• Calculate Data in Cube Designer

Create and Validate Member Formulas in Cube Designer

In the Cube Designer Formula Editor, you can write formulas for specific outline members. You can construct member formulas from operators, functions, dimension names, member names, substitution variables, and numeric constants.

• The Cube Designer Formula Editor applies to both aggregate storage and block storage cubes. In aggregate storage, the functions will be MDX functions. In block storage, the functions will be calc script functions.
• Validation works against existing block storage cubes in Essbase (validation is disabled for aggregate storage cubes). It does not detect application workbook changes that have not been applied to the cube.
• Member selection works with existing cubes only.

Formula Editor provides a formula editing pane in which you can enter a formula. You can use the Tab and arrow keys to move focus within Formula Editor. You can also use a point-and-click approach to select and insert formula components into the formula editing pane. A member selection tree helps you place the correct member names into the formula.

1. Open the application workbook for the cube that you want to modify.
2. If a dimension worksheet has been defined with the Formula property, select the cell in the Formula column for the member you wish to create a formula.
3. On the Cube Designer ribbon, click Calculation Editors .
4. From the drop-down menu, select Member formula editor.
5. Enter your login credentials for Essbase, if prompted to do so.
6. In the Formula Editor, create the formula.

   • Use the keyboard to enter formula text. Enclose in quotation marks any member names containing blanks or special characters.

   • Select a cell containing a member name or alias from any dimension worksheet. Place the cursor in the appropriate location of the editor and right-click to paste that name surrounded by quotes into the editor.

   • Double-click on a member in the member selection tree to have that member pasted into the editor.
   • To search for a specific member in the tree, enter the member name in the Member Tree text box, and click Search.

   • Double-click on a function to have that function syntax pasted into the editor.

7. Optional: on block storage cubes, click Validate to check formula syntax.
   If the validation fails, edit the formula and try again. Be sure to check the error message for guidance.
   The Validate button is disabled for aggregate storage cubes.

See:

• Developing Formulas for Block Storage Databases

• Understanding Formula Syntax

• Reviewing Examples of Formulas

Create and Validate Calculation Scripts in Cube Designer

In the cube designer Calculation Script Editor, you can write calculation scripts for specific block storage cubes. Calculation scripts specify how cubes are calculated and, therefore, override outline-defined cube consolidations.

See Developing Calculation Scripts for Block Storage Databases.

• The cube designer Calculation Script Editor applies only to block storage cubes.
• If the cube of the currently open application workbook exists on the server and the communication to the server works, you are in online mode. Otherwise, you are in offline mode. When you are in offline mode, the member tree is disabled.
• If you are editing a script locally, and a script with the same name exists on the server, the Save changes to server and worksheet check box is enabled. If you are editing a remote script (one that exists only on the server), the check box is disabled.
• Validation works against existing cubes in Essbase. It does not detect application workbook changes that have not been applied to the cube.

The Calculation Script Editor provides a calculation script-editing pane in which you can enter a script. You can use the Tab and arrow keys to move focus within Calculation Script Editor. A member selection tree helps you place the correct member names into the script.

1. Open the application workbook for the cube you want to modify.
2. If a calculation worksheet has been defined, select it. If not, create one. See Work with Calculation Worksheets in Cube Designer.
3. On the cube designer ribbon, click Calculation Editors .
4. Select Calculation script editor from the drop-down menu.
5. Enter your login credentials for Essbase, if prompted to do so.
6. In the Calculation Script Editor, create the calculation script.
   • Use the keyboard to enter text. Enclose in quotation marks any member names containing blanks or special characters.
   • Double-click on a member in the member selection tree to have that member pasted into the editor. To search for a specific member in the tree, enter the member name in the Member Tree text box, and click Search.
   • Double-click on a function to have that function syntax pasted into the editor.
7. Click Validate to check calculation script syntax. If the validation fails, edit the script and try again. Be sure to check the error message for guidance.
8. If you want to synchronize changes to the server and the application workbook, you can check Save changes to server and worksheet prior to saving the script.

   Note:

   This option works in online mode.
9. Click Save.
10. Optional: Click Run to run the script.

Calculate Data in Cube Designer

Calculation scripts specify how cubes are calculated and, therefore, override outline-defined cube consolidations. For example, you can calculate cube subsets or copy data values between members. See Developing Calculation Scripts for Block Storage Databases.

During cube development, it is common to recalculate a cube many times when validating the data and formulas. The calculation script files used in the calculation process must be stored in Essbase. If a Calc worksheet is included in the application workbook, then the calculation script files are automatically generated during the cube build process. Individual calculation script files can also be uploaded to Essbase. See Work with Files and Artifacts.

1. In Excel, on the Cube Designer ribbon, select Calculate .
2. On the Calculate Data dialog box, select an application and a cube, and select the calculation script you want to use. To view or edit the calculation script, click Edit. Right click on the calc script name to display options: rename, delete, run, copy, and lock, or unlock.
3. Click Run to start the calculation.
4. When the asynchronous job completes a dialog box is displayed. Click Yes to start Job Viewer and view the status of the calculation, or click No if you do not want to start Job Viewer.
5. (Optional) View the status in Job Viewer.
   See Work with Jobs in Cube Designer.