Using ESSCMD

In This Section:

Understanding ESSCMD

Preparing to Start ESSCMD

Starting and Quitting ESSCMD

Using Interactive Mode

Using Script and Batch Files for Batch Processing

Also see:

Understanding ESSCMD

With ESSCMD, you can execute Essbase Server operations at the command line, in either interactive or batch mode.

Interactive mode means entering commands at the ESSCMD command line and receiving prompts where necessary. Interactive mode is convenient for short operations that require few commands, checking for information on the fly, and error checking.

Batch processing mode is used for automating routine Essbase Server maintenance and diagnostic tasks. You can write a script or batch file and run it from the command line. Batch processing mode is convenient if you frequently use a particular series of commands, or if a task requires many commands.

Understanding Syntax Guidelines

In general, use the same syntax for entering ESSCMD commands that you do for other calculation commands. However, differences exist between the ESSCMD interactive and batch processing modes in the requirements for quotation marks and the semicolon statement terminator. Use the guidelines in this section when creating script or batch files.

Quotation Marks

Quotation marks (" ") enclose character parameters and responses to commands.

  • In interactive ESSCMD, using quotes is optional. Use quotes when a parameter has an embedded space; for example:

    CALC "Calc All;"
  • In an ESSCMD script file, always enclose all character parameters and responses to commands in quotation marks; for example:

    LOGIN "Localhost" "user1" "Password";
  • Numeric parameters and responses do not require quotation marks.

  • Do not enclose quotation marks within quotation marks.

Semicolon Statement Terminator

The ; (semicolon) statement terminator signals the end of a command; for example:

SELECT "SAMPLE" "BASIC"; 
  • In interactive ESSCMD, pressing the Enter key signals ESSCMD that the command is complete. The statement terminator is optional.

  • In an ESSCMD script file, you should use the terminator, even though it is optional, if a command has many parameters. Doing so is especially important to signal the end of the parameter list if some parameters are optional.

If you omit some optional parameters and do not use a semicolon to end the list, ESSCMD looks for the remaining values in the next command in the file, leading to unpredictable results.

The SETAPPSTATE and SETDBSTATE commands, defined in the Oracle Essbase Technical Reference, are examples of commands that you should terminate with a semicolon to prevent confusion in processing.

Note:

All syntax examples in this chapter use quotation marks and semicolon terminators.

Canceling ESSCMD Operations

When running ESSCMD, you can cancel an asynchronous operation, such as a calculation, export, or restructure operation, by pressing and holding the Esc key until ESSCMD responds.

Referencing Files

Some commands require that you precede artifact or file names with a numeric parameter, from 1 to 4, that tells Essbase where to look for the artifact or file. The parameter directs ESSCMD to look for files in other applications, databases, or systems.

Table 254 lists each value for the numeric parameter (Number), the file location to which it applies, and the information that ESSCMD requests when you use each parameter setting. appname is the application name and dbname is the database name.

Table 254. List of Numeric Parameters

Number

File

Essbase prompts user for:

1

Local or client-based file

  • Windows: Files in the ARBORPATH/client/appname/dbname directory

  • UNIX: Files in the ARBORPATH/client/appname/dbname directory

2

Remote or server-based file

  • Windows: Files in the ARBORPATH/app/appname/dbname directory

  • UNIX: Files in the ARBORPATH/app/appname/dbname directory

3

File

Fully qualified path to the file, unless file is in the current ESSCMD directory

4

SQL table

Full network and database information for the SQL table

For example, the LOADDATA command can load a data file that resides on the client or Essbase Server. The command requires the numeric parameter to tell Essbase where to look for the data file. This example causes ESSCMD to prompt for the fully qualified path name of the file to load:

LOADDATA 3

File extensions are usually optional in interactive and batch processing modes, except when using commands that require a numeric parameter that indicates the location of files:

  • If you use file option 3 (File), you must enter the file extension in both interactive and batch processing modes.

  • If the artifact is in the directory from which you started ESSCMD, you need not enter a path.

Accessing Multiple Databases

Because ESSCMD supports multiple login instances on Essbase Server, you can access multiple databases in a single session. Even when you log on to multiple databases, you use only one port on your server license.

Considering Case-Sensitivity

The Essbase Server creates application and database names exactly as the user specifies. Case is not changed for any platform.

For backward compatibility, the Essbase Server searches for existing application and database names using the exact case first. However, if it cannot find the file, Essbase Server searches all possible case combinations to find the existing application and database names.

Essbase does not allow you to create application and database names that differ only in case. For example, Essbase displays an error message if application mYdATA exists and you try to create an application MyData.

You can choose to make member names case-sensitive.

  To make a member case-sensitive, see “Making Members Case-Sensitive” in the Oracle Essbase Administration Services Online Help.

Getting Help

For descriptions and syntax for individual ESSCMD commands, see the Oracle Essbase Technical Reference.

Preparing to Start ESSCMD

Before you start ESSCMD, ensure that the following items are installed and running:

  • Essbase

  • Communications protocol (TCP/IP) on Essbase

Starting and Quitting ESSCMD

ESSCMD comprises two files: esscmd.exe and esscmd.hlp (esscmd and esscmd.hlp on UNIX platforms) on Essbase Server and Essbase Administration Server.

  To start ESSCMD, enter ESSCMD at the operating system command prompt.

When ESSCMD starts, a command prompt is displayed:

:::[n]->

where n is the value of the active login instance. Each subsequent, successful login increments this value by one. When ESSCMD starts, the instance number is zero (0).

Note:

Use the SETLOGIN command to toggle between active login instances. Use the LISTLOGINS command to view active login instances.

  To quit ESSCMD, enter EXIT at the prompt and press Enter.

ESSCMD disconnects from the application server and terminates the session.

Using Interactive Mode

In interactive mode, you enter commands and respond to prompts, which is useful when you are performing simple tasks that require few commands. If you are performing more complex tasks that require many commands, consider creating a script file or batch file. See Using Script and Batch Files for Batch Processing.

For syntax conventions when working in interactive mode, see Understanding Syntax Guidelines.

Logging on to Essbase Server

After starting ESSCMD, you must connect to Essbase Server so that you can enter commands.

  To log on to Essbase Server:

  1. At the ESSCMD prompt, log on to the server with the LOGIN command.

    See the Oracle Essbase Technical Reference.

  2. Enter the server name.

    When you connect from the server console, the server name depends on your network setup. For example, the name could be aspen.

  3. Enter your user name.

  4. Enter your password.

The ESSCMD prompt is displayed:

aspen:::userName[1]->

userName is your login name.

You can enter any valid ESSCMD command. For a complete listing of commands, see the Oracle Essbase Technical Reference.

Note:

To load an application into memory and select a database on the Essbase server, use the SELECT command to select a database from an application that resides on the server.

The ESSCMD prompt is displayed:

aspen:appname:dbname:userName[1]->

appname is the name of the application. dbname is the name of the database to which you are connected.

Entering Commands

Choose either of the following methods to enter commands in interactive mode:

  • Enter the command and press Enter.

    ESSCMD prompts you for each of the command parameters. For example, the SELECT command has two parameters, as shown in the command syntax:

    SELECT " appname " " dbname ";

    If you enter only SELECT and press Enter, ESSCMD prompts you for the first parameter, the application name (appname). After you enter the application name and press Enter, ESSCMD prompts you for the database name (dbname).

  • Type the commands and all parameters, and press Enter.

    Using SELECT as the example, you would enter:

    SELECT "Sample" "Basic"; 

Whichever method you use, the interactive prompt now reflects the application and database names. For example, the following prompt tells you that the Sample application and Basic database are selected:

aspen:Sample:Basic:User[1]->

In this case, you can enter other commands without the application or database name parameters that it normally requires.

Canceling Operations

While ESSCMD is running, you can cancel an asynchronous operation, such as a calculation, export, or restructure operation, by pressing and holding the Esc key until ESSCMD responds.

Caution!

Do not pause or suspend your system while Essbase is processing a command. Pausing the system may prevent Essbase from correctly completing the command.

Using Script and Batch Files for Batch Processing

If you use a series of commands frequently or you must enter many commands to complete a task, consider automating the task with a script or batch file. These files are useful for batch data loads and complex calculations.

See Understanding Syntax Guidelines.

  • A script file contains ESSCMD commands. You can run a script file from the operating system command line or from within an operating system batch file, and the script file is processed by ESSCMD. By default, an ESSCMD script file has a .scr file extension. You can use a different extension.

  • A batch file is an operating system file that calls multiple ESSCMD scripts and can also include operating system commands. You can use a batch file to run multiple sessions of ESSCMD. You can run a batch file on Essbase Server from the operating system prompt; the file is processed by the operating system. On Windows, batch files have .bat file extensions.

Note:

On UNIX, a batch or script file is written as a shell script. A shell script usually has the file extension .sh (Bourne or Korn shell) or .csh (C shell).

When you run a script or batch file, ESSCMD executes the commands in sequence until it reaches the end of the file.

Some commands may be changed in new releases of Essbase. Changes might affect existing scripts. To ensure that your scripts work correctly in the current release, see the Oracle Essbase New Features and the Essbase Readme for information about changed or deleted commands and change your scripts if needed.

Writing Script Files

ESSCMD script files automate an often-used or lengthy series of commands. Each script file must be a complete ESSCMD session, with login, application and database selection, logout, and termination commands.

  To define a script file:

  1. Enter ESSCMD commands in any editor that saves data in ASCII text.

  2. Save the file with the .scr ESSCMD script file extension.

    For example, the following script file, test.scr, was created in Notepad:

    LOGIN "localhost" "User1" "password";
    SELECT "Sample" "Basic";
    GETDBSTATE
    EXIT;

When run from the operating system command line, this script logs User1 into the Essbase localhost server, selects the Sample application and Basic database, gets database statistics, and quits the ESSCMD session.

Running Script Files

  To run script files in ESSCMD:

  1. Enter the following command at the operating system prompt:

    ESSCMD scriptFileName.scr
  2. Replace scriptFileName with the name of the script file.

    For example, enter the following if the script file is in the current directory:

    ESSCMD TEST.SCR
  3. If the script file is not in the current directory, include the path.

    For example:

    ESSCMD C:\WORK\SCRIPTS\TEST.SCR (an absolute path on Windows)

    or

    ESSCMD..\SCRIPTS\TEST.SCR (a relative path on Windows)

Handling Command Errors in Script Files

ESSCMD error-handling features provide error checking and handling for your script files. You can write error-handling commands into your script file to check for errors and, if necessary, branch to an appropriate error-handling response.

After each ESSCMD command is executed, a number is stored in an internal buffer. If the command executes successfully, 0 is returned to the buffer. If the command is unsuccessful, the error number is stored in the buffer; a state called nonzero status.

For error checking within an ESSCMD script file, ESSCMD provides the following error-handling commands:

  • IFERROR checks the previously executed command for a nonzero return status (failure to execute). If the status is not zero, processing skips all subsequent commands and jumps to a user-specified point in the file, where it resumes. The script file can branch to an error-handling routine or the end of the file.

  • RESETSTATUS reverts all saved status values to 0 (zero) in preparation for more status checking.

  • GOTO forces unconditional branching to a user-specified point in the file, whether or not an error occurred.

In this load.scr example file, if the LOADDATA command does not execute successfully, ESSCMD branches to the end of the file to avoid attempting to calculate and run a report script on the empty database:

LOGIN "localhost" "User1" "password" "Sample" "Basic";
LOADDATA 2 "calcdat";
IFERROR "Error";
CALC "Calc All;";
IFERROR "Error";
RUNREPT 2 "Myreport";
IFERROR "Error";
[possible other commands]
EXIT;

:Error

Note:

You can use the OUTPUT command to log errors to a text file.

For the syntax and usage of ESSCMD error commands, see the Oracle Essbase Technical Reference.

Reviewing Sample Script Files

Essbase provides sample script files, based on the Sample.Basic database, that demonstrate common batch operations. The sample script files reside in the following directory:

ARBORPATH/app/Sample/Basic

Sample Script: Importing and Calculating Data

The sample1.src file executes the following actions:

  • Logs on to Essbase Server

  • Selects an application and database

  • Prevents other users from logging on and making changes to the database

  • Imports data from a text file

  • Calculates the database

  • Reenables logins

  • Exits ESSCMD

LOGIN "Poplar" "TomT" "Password";
SELECT "Sample" "Basic";
DISABLELOGIN;
IMPORT 2 "ACTUALS" 4 "Y" 2 "ACTUAL" "N";
CALCDEFAULT;
ENABLELOGIN;
EXIT;

Sample Script: Building Dimensions and Importing and Calculating Data from a SQL Source

The sample2.scr file executes the following actions:

  • Logs on to Essbase Server

  • Selects an application and database

  • Prevents other users from logging on and making changes to the database

  • Updates the outline from an SQL data source

  • Imports data from SQL

  • Calculates the database

  • Reenables logins

  • Exits ESSCMD

LOGIN "Poplar" "TomT" "Password";
SELECT "Sample" "Basic";
DISABLELOGIN;
BUILDDIM 2 "PRODRUL" 4 "PRODTBL" 4 "PROD.ERR";
IMPORT 4 "TOMT" "PASSWORD" 2 "ACTUAL" "N";
CALCDEFAULT;
EXIT;

Sample Script: Scheduling Report Printing

The sample3.scr file executes the following actions:

  • Logs on to Essbase Server

  • Selects an application and database

  • Assigns reports that output to files for later printing

  • Exits ESSCMD

LOGIN "Poplar" "TomT" "Password";
SELECT "Sample" "Basic";
RUNREPT 2 "REP1" "REP1.OUT";
RUNREPT 2 "REP2" "REP2.OUT";
RUNREPT 2 "REP3" "REP3.OUT";
EXIT;

Writing Batch Files

You can write a batch file that runs one or more ESSCMD scripts and includes operating system commands. See your operating system instructions to learn the syntax for writing batch files.

Handling Command Errors in Batch Files

For the operating system batch file, you can use ESSCMD command return values to control the flow of scripts that the batch file executes.

An ESSCMD program returns an integer value upon exiting. This value represents the status of the last executed command, usually whether the command succeeded or failed. You can set up your batch file to test for this value, and if the test fails, branch to an error-handling response. This process is similar to creating a script file. See Handling Command Errors in Script Files.

For example, a batch file could contain three scripts: an ESSCMD batch file that loads data, a calculation script that performs calculations on the data, and a report script that reports on the results of the calculation. If the load batch file fails, the calculations and reporting also fail. In this case, it would be best to stop the batch file after the failure of the load file and correct the error that caused the failure before continuing. If your batch file tests for the return value of the load process, and this return value indicates failure, the batch file can jump to the end of the file and stop or execute some other error-handling procedure, rather than attempting to calculate data that did not load.

The following example shows a Windows operating system batch file and the contents of one of the ESSCMD scripts it runs, load.scr. Because error-checking requirements vary, the syntax in this example may not correspond to that of your operating system. See your operating system documentation for error checking in batch files.

An operating system batch file could contain commands like this:

ESSCMD LOAD.SCR
If not %errorlevel%==0 goto Error
ESSCMD CALC.SCR
If not %errorlevel%==0 goto Error
ESSCMD REPORT.SCR
If not %errorlevel%==0 goto Error
Echo All operations completed successfully
EXIT

:Error
Echo There was a problem running the script