Managing Applications and Databases

In This Section:

Understanding Applications and Databases

Understanding How Essbase Files Are Stored

Managing Applications, Databases, and Database Artifacts

Migrating Applications Using Administration Services

Porting Applications Across Platforms

Also see the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.

Understanding Applications and Databases

An application is a management structure that contains one or more Essbase databases and related files. Essbase applications and databases usually reside on the Essbase Server. The server computer can store multiple applications.

An Essbase database is a data repository that contains a multidimensional data storage array. A multidimensional database supports multiple views of data so that users can analyze the data and make meaningful business decisions.

Files that are related to Essbase databases are called artifacts or objects. Database artifacts perform actions against one or more Essbase databases, such as defining calculations or reporting against data. By default, artifacts are stored in their associated database folder on the server. Some artifacts also can be saved to a client computer or to other available network directories. See Understanding How Essbase Files Are Stored.

The common types of database artifacts in Essbase:

  • A database outline (a storage structure definition)

  • Data sources

  • Rules for loading data and building dimensions dynamically (rules files)

  • Scripts that define how to calculate data (calculation scripts)

  • Scripts that generate reports on data (report scripts)

  • Security definitions

  • Security filters

  • LROs

  • Partition definitions

Some of these artifacts are optional, such as calculation scripts, filters, and LROs.

For a complete description of each database artifact, see Understanding Database Artifacts.

Understanding How Essbase Files Are Stored

Essbase installation files and files that are created when using Essbase are stored in the following locations:

  • MIDDLEWARE_HOME—Oracle WebLogic Server home, and, optionally, one or more Oracle homes, including EPM Oracle home.

    In a default installation, MIDDLEWARE_HOME is:

    Oracle/Middleware
  • EPM_ORACLE_HOME—Directory under which all Oracle Hyperion Enterprise Performance Management System products are installed.

    In a default installation, EPM_ORACLE_HOME is:

    MIDDLEWARE_HOME/EPMSystem11R1

    For example:

    Oracle/Middleware/EPMSystem11R1
  • EPM_ORACLE_INSTANCE—Deployed product location (includes data and applications, deployed Web applications, and log files).

    In a default installation, EPM_ORACLE_INSTANCE is:

    Oracle/Middleware/user_projects/epmsystem1
  • ESSBASEPATH—The Essbase installation directory.

    In a default installation, the ESSBASEPATH directories are:

    • For Essbase Server:

      EPM_ORACLE_HOME/products/Essbase/EssbaseServer
    • For Essbase Client:

      EPM_ORACLE_HOME/products/Essbase/EssbaseClient

    For a list of directories that are created under ESSBASEPATH, see the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

  • ARBORPATH:

    • The app directory location where Essbase application files (as they are created) and sample applications and databases (provided with Essbase) are stored.

    • The bin directory location where Essbase configuration setting (essbase.cfg) and security (essbase.sec and essbase_timestamp.bak) files (as they are created) are stored.

    In a default installation, ARBORPATH is:

    EPM_ORACLE_INSTANCE/EssbaseServer/essbaseserver1
  • EPM_ORACLE_INSTANCE/common—Directory where common internal and third-party components are stored.

  • EPM_ORACLE_INSTANCE/diagnostics/logs—Directory where log files are stored.

For more information about MIDDLEWARE_HOME, EPM_ORACLE_HOME, and ESSBASE_ORACLE_INSTANCE, see the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

Server Software File Types

Table 134 lists the types of Essbase Server files:

Table 134. Essbase File Types

File Extension

Location

Description

bak

ARBORPATH/bin

Backup of security file

bnd

ESSBASEPATH/bin

Microsoft ODBC file for SQL Interface installation using a DB2 database

cfg

ARBORPATH/bin

Essbase Server configuration file

cnt

ESSBASEPATH/bin

Online help contents file

cpl

ESSBASEPATH/bin

Microsoft ODBC driver for Windows platforms

dll

ESSBASEPATH/bin

Microsoft Windows Dynamic Link Library

eqd

ESSBASEPATH/bin

Query Designer files

exe

ESSBASEPATH/bin

Executable file

hlp

ESSBASEPATH/bin

Online help file

lck

ESSBASEPATH/bin

Lock file

lic

ESSBASEPATH/bin

License information file for ODBC

pl

ESSBASEPATH/bin

Sample Perl script

pm

ESSBASEPATH/bin

Perl Module

mdb

ESSBASEPATH/bin

Message database file

sec

ARBORPATH/bin

Security file

sl

ESSBASEPATH/bin

HP-UX shared library file

so

ESSBASEPATH/bin

Solaris shared library file

xll

ESSBASEPATH/bin

Spreadsheet Add-in

Application and Database File Types

Table 135 lists the file types that Essbase uses to store applications, databases, and their related artifacts.

Table 135. Essbase File Types for Applications and Databases

File Extension

Description

alg

Spreadsheet audit historical information

apb

Backup of application file

app

Application file, defining the name and location of the application and other application settings

arc

Archive file

atx

Spreadsheet audit transaction

chg

Outline synchronization change file

csc

Essbase calculation script

db

Database file, defining the name, location, and other database settings

dbb

Backup of database file

ddb

Partitioning definition file

ddm

Temporary partitioning file

ddn

Temporary partitioning file

esm

Essbase kernel file that manages pointers to data blocks and contains control information used for database recovery

esr

Temporary database root file

esn

Temporary Essbase kernel file

ind

Essbase index file

inn

Temporary Essbase index file

log

Server or application log

lro

LRO file linked to a data cell

lst

Cascade table of contents or list of files to back up

mxl

MaxL script file (saved in Administration Services)

ocl

Database change log

ocn

Incremental restructuring file

oco

Incremental restructuring file

olb

Backup of outline change log

olg

Outline change log

otl

Essbase outline file

otm

Temporary Essbase outline file

otn

Temporary Essbase outline file

oto

Temporary Essbase outline file

pag

Essbase database data (page) file

pan

Temporary Essbase database data (page) file

rep

Essbase report script

rul

Essbase rules file

scr

Essbase ESSCMD script

sel

Saved member select file

tct

Essbase database transaction control file that manages all commits of data and follows and maintains all transactions

tcu

Temporary database transaction control file

trg

Trigger definition file. XML (Extensible Markup Language) format

txt

Text file, such as a data file to load or a text document to link as a LRO

xcp

Exception error log

xls

Microsoft Excel file

API File Types

Table 136 lists the types of Essbase files stored in the ESSBASEPATH/api subdirectories:

Table 136. Essbase File Types in the api Directory

File Extension

Description

a

UNIX static library file

bas

Microsoft Visual Basic program source file, containing header definitions for the Essbase API

h

C or C++ header file, containing header definitions for the Essbase API

lib

C or C++ program library

np

Named Pipes network library

tcp

TCP/IP network library

For a description of Essbase applications, databases, and database artifacts, see Understanding Applications and Databases and Understanding Database Artifacts.

Strategies for Backing Up and Recovering Databases

Regular Essbase backups, which should be integrated into production server maintenance, are key to database maintenance. Backup frequency should be determined by the volatility of the database and server environment and the need for rapid database restoration (should a server interruption occur).

To back up and restore block storage databases, you can use either of the following methods:

  • Automated database backup and restore and transaction logging and replay

    Backup and restore provides the equivalent functionality of manually backing up and restoring a database. When a backed-up database is restored, transactions that occurred after the backup procedure are not recovered. However, with transaction logging and replay, post-backup transactions are captured and can be replayed. Thus, a backed-up database can be recovered to the most-recent state before the interruption occurred.

    The use of the database backup and restore and transaction logging and replay features eliminates the need for various manual steps and, therefore, enables administrators to back up and recover databases more efficiently. Oracle recommends incorporating these features in your backup and recovery strategy.

  • Manual backup and restore

    Essbase customers who have designed a backup and restore strategy that uses manual procedures and who do not need the functionality of transaction logging and replay can continue using their manual strategy.

To back up and restore aggregate storage applications, you must use manual procedures.

See the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.

Using the File System to Manage Applications and Databases During Backup

You should not use the platform file system to copy, move, rename, or delete applications and databases. When an application or database is altered through the file system, the Essbase security file is unable to recognize the changes. This situation creates a mismatch between what actually exists on the hard drive and what exists according to Essbase.

Caution!

Do not move, copy, modify, or delete any of these files: essn.ind, essn.pag, dbname.ind, dbname.esm, or dbname.tct. Doing so may result in data corruption.

Certain application and database files can be managed successfully through the file system:

  • Rules files for dimension builds and data loads (.rul)

  • Data load or dimension build files

  • Calculation scripts (.csc)

  • Report scripts (.rep)

  • MaxL scripts (.mxl or any extension)

  To copy or move an outline file (.otl), you must use Administration Services. See “Copying Outlines” in the Oracle Essbase Administration Services Online Help.

The only time the file system should be used to manage applications and databases is during backup, when the entire directory for an application or database is copied and stored elsewhere. See the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.

Monitoring Applications

Each application that is loaded is an open task or process in the operating system. On Windows platforms, the application is displayed in a command-line window. On UNIX platforms, the application server is a child process of ESSBASE. When the application starts, ESSBASE starts the esssvr process. See Starting an Application.

On Windows platforms, when an application starts, a new icon is displayed in the taskbar. Double-click the icon to view the server window.

Essbase Server records application-level activities in an application log. See Using Essbase Logs.

  To view application activities as they occur, use a tool:

Tool

Instruction

On Windows platforms, use the application-process window

Select the command-line window that bears the name of the application.

UNIX

tail -f logfile

Using Essbase to Manage Applications and Databases

This section describes managing applications and databases.

Viewing Applications and Databases

When you start Administration Services Console, the Enterprise View tree is displayed in the navigation panel. Enterprise View is a graphical tree view of the Essbase environment. It displays the Administration Servers and Essbase Servers that you select. Your view of the Essbase environment may look different from that of other administrators.

Applications and databases, and their associated artifacts, are represented as nodes beneath the Essbase Server node. Artifacts are grouped into container nodes. For example, individual applications are contained in the Applications node, and databases are contained in the Databases container node. If sample applications and databases are installed with Essbase Server, they appear in Enterprise View along with your organization’s applications and databases.

See “About Enterprise View” in the Oracle Essbase Administration Services Online Help.

  To create an application, see “Creating Applications” in the Oracle Essbase Administration Services Online Help.

Copying or Migrating Applications

You can copy an application to any Essbase Server to which you have appropriate access. You can copy (migrate) an entire application to another Essbase Server, or you can copy an application on the same Essbase Server. For example, you may need to migrate an entire application from a development server to a production server. Or, you may want to copy an application on the same server for testing or for backup purposes.

Essbase copies applications differently, depending on whether you are copying to the same Essbase Server or to a different Essbase Server. When you migrate applications, you can select the artifacts to migrate, such as calculation scripts, report scripts, rules files, custom-defined macros and functions, substitution variables, and filters. You can also specify how user and group security is migrated.

Administration Services provides a Migration Wizard that helps you migrate applications. See “Migration Wizard” in Oracle Essbase Administration Services Online Help.

  To copy an application, use a tool:

Tool

Topic

Location

Administration Services

Copying Applications

Oracle Essbase Administration Services Online Help

MaxL

create application as

Oracle Essbase Technical Reference

ESSCMD

COPYAPP

Oracle Essbase Technical Reference

Renaming Applications

When you rename an application, the application and its associated directory (ARBORPATH/app/appname) are renamed. All artifacts within the application (for example, databases or calculation scripts) with the same name as the application are not renamed. Before you rename an application, see Naming Restrictions for Applications and Databases.

  To rename an application, use a tool:

Tool

Topic

Location

Administration Services

Renaming Applications

Oracle Essbase Administration Services Online Help

MaxL

alter application

Oracle Essbase Technical Reference

ESSCMD

RENAMEAPP

Oracle Essbase Technical Reference

Deleting Applications

When you delete an application, all artifacts within the application also are deleted. The ARBORPATH/app/appname directory and all files in the directory are deleted.

  To delete an application, use a tool:

Tool

Topic

Location

Administration Services

Deleting Applications

Oracle Essbase Administration Services Online Help

MaxL

drop application

Oracle Essbase Technical Reference

ESSCMD

DELETEAPP

Oracle Essbase Technical Reference

Copying Databases

You can copy a database in an application to any Essbase Server and application to which you have appropriate access. You can copy (migrate) an entire database to another Essbase Server, or you can copy a database on the same Essbase Server. For example, you may need to migrate an entire database from a development server to a production server. Or, you may want to copy a database on the same server for testing or for backup purposes. Essbase copies databases differently depending on whether you are copying to the same Essbase Server or to a different Essbase Server. See “Copying Databases” in Oracle Essbase Administration Services Online Help.

Administration Services provides a Migration Wizard that helps you migrate applications and databases. See “Migration Wizard” in Oracle Essbase Administration Services Online Help.

When you copy a database, all files associated with the database, except data files (.pag and .ind), are copied to the destination application. Before copying, make sure you have enough disk space to contain a full copy of the database and its related files.

  To copy a database, use a tool:

Tool

Topic

Location

Administration Services

Copying Databases

Oracle Essbase Administration Services Online Help

MaxL

create database as

Oracle Essbase Technical Reference

ESSCMD

COPYDB

Oracle Essbase Technical Reference

Note:

Essbase allows copying a non-Unicode database to a Unicode application; however, copying a Unicode database to a non-Unicode application is not allowed.

Renaming Databases

When you rename a database, the database and its associated directory (ARBORPATH/app/appname/dbname), and the outline file (.otl) are renamed. All other artifacts in the database (for example, calculation scripts) with the same name as the database are not renamed.

  To rename a database, use a tool:

Tool

Topic

Location

Administration Services

Renaming Databases

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

RENAMEDB

Oracle Essbase Technical Reference

Deleting Databases

When you delete a database, all artifacts within it are also deleted. The ARBORPATH/app/appname/dbname directory and all files located in the directory are deleted.

  To delete a database, use a tool:

Tool

Topic

Location

Administration Services

Deleting Databases

Oracle Essbase Administration Services Online Help

MaxL

drop database

Oracle Essbase Technical Reference

ESSCMD

DELETEDB

Oracle Essbase Technical Reference

Using Essbase to Manage Artifacts

This section describes copying, renaming, and deleting artifacts, such as outlines, calculation scripts, report scripts, rules files, and data sources. See Understanding Database Artifacts.

Caution!

The only time the file system should be used to manage applications is during backup, when the entire directory for an application or database is copied and stored elsewhere.

Copying Artifacts

You can copy any database artifact, except an outline, to another application, database, server, or client location. For instructions on copying outlines, see Creating and Editing Outlines.

  To copy an artifact, use a tool:

Tool

Topic

Location

Administration Services

Topics on copying the specific artifact; for example, Copying Rules Files

Oracle Essbase Administration Services Online Help

MaxL

alter object

Oracle Essbase Technical Reference

ESSCMD

COPYOBJECT

Oracle Essbase Technical Reference

Renaming Artifacts

You can rename any artifact except an outline. An outline always has the same name as the database, so you must rename the database to rename the outline.

  To rename an artifact, use a tool:

Tool

Topic

Location

Administration Services

Topics on renaming the specific artifact; for example, Renaming a Rules File

Oracle Essbase Administration Services Online Help

MaxL

alter object

Oracle Essbase Technical Reference

ESSCMD

RENAMEOBJECT

Oracle Essbase Technical Reference

Deleting Artifacts

You can delete any artifact except an outline. An outline is a required part of a database, so you must delete the database to delete the outline.

  To delete an artifact, use a tool:

Tool

Topic

Location

Administration Services

Topics on deleting the specific artifact; for example, Deleting Rules Files

Oracle Essbase Administration Services Online Help

MaxL

drop object

Oracle Essbase Technical Reference

ESSCMD

DELETE command for the artifact to delete

Oracle Essbase Technical Reference

Locking and Unlocking Artifacts

Essbase uses a checkout facility for database artifacts to ensure that only one user modifies an artifact simultaneously. This section describes how to lock and unlock artifacts, with the exception of outlines. See Locking and Unlocking Outlines.

Note:

Locking artifacts is not the same as locking data blocks. The Essbase kernel handles locking for data blocks but not for artifacts. See Data Locks.

By default, whenever you open a database artifact, Essbase prompts you to lock the artifact. You can change this default behavior for some artifacts; see “Setting Essbase Default Options” in Oracle Essbase Administration Services Online Help.

When an artifact is locked, Essbase does not allow other users to save over, rename, or delete the artifact. You can open a locked artifact and edit it, but you cannot save over the existing artifact. To save changes made to a locked artifact, save the modified artifact to a different location. You can execute and copy locked artifacts.

Unlocking Artifacts

You can unlock artifacts according to your permissions. In Administration Services, you can view all artifact locks for an Essbase Server, application, or database.

  To unlock an artifact, use a tool:

Tool

Topic

Location

Administration Services

Locking and Unlocking Objects

Oracle Essbase Administration Services Online Help

MaxL

alter object

Oracle Essbase Technical Reference

ESSCMD

UNLOCKOBJECT

Oracle Essbase Technical Reference

Migrating Applications Using Administration Services

Using Administration Services, you can migrate applications to any Essbase Server to which you have appropriate access, regardless of platform. For example, you may need to migrate an application from a development server to a production server. When you migrate applications, you can select the artifacts to migrate, such as calculation scripts, report scripts, rules files, custom-defined macros and functions, substitution variables, and filters. You can also specify how user and group security is migrated.

  To migrate an application, see “Copying Applications” in Oracle Essbase Administration Services Online Help.

Porting Applications Across Platforms

Essbase runs on multiple platforms, including Windows and UNIX. For a list of supported platforms, see the Oracle Hyperion Enterprise Performance Management System Certification Matrix (http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html).

After you create an application, you may want to port the application to a server that runs a different operating system. This section describes how to port an application to another Essbase computer.

Porting Essbase applications across servers involves these steps:

Identifying Compatible Files

If you are porting an Essbase application to a server that uses a different operating system, you must identify which Essbase files are compatible with the new operating system.

The following file types are compatible between operating systems:

  • Text files. The Essbase text files are calculation scripts (.csc) and report scripts (.rep), and any MaxL or ESSCMD scripts you have developed. Also, data files can be text files.

  • Rules files. These files are binary files, but they are compatible between operating systems. Rules files have the extension .rul.

  • Outline files. These files are binary files, but they are compatible between operating systems. Outline files have the extension .otl.

The following file types are incompatible between operating systems and must be redefined or reloaded on the new server:

  • Database files with the extensions .db and .dbb

  • Data files with the extension .pag

  • Index files with the extension .ind

  • Security files with the extension .sec

  • Application files with the extensions .app and.apb

  • Essbase Kernel files with the extension .esm

Note:

If you are using the Linked Reporting Objects feature, you must relink any files or cell notes on the new server. For a comprehensive discussion of how LROs are used, see Linking Objects to Essbase Data.

Checking File Names

When transferring files to a UNIX system, be aware of the case-sensitivity of file names. UNIX is a case-sensitive operating system, and files are recognized only if file names are in the correct case. For example, in certain MaxL and ESSCMD operations, you must specify a file name, and the file name must be entered in the correct case.

The Essbase system files use the following naming conventions on UNIX systems:

  • Executable files have no extension and are uppercase (for example, ESSBASE, ESSCMD).

  • Static library files have the file extension .a and are in lowercase (for example, libessnet.a).

  • Shared library files have the file extension .sl on HP-UX, .so on Solaris, and .a on AIX. These file names are in lowercase (for example, libesscur.sl).

  • Security files have the file extension .sec and are in lowercase (for example, essbase.sec).

  • Message database files have the file extension .mdb and are in lowercase (for example, essbase.mdb).

  • Online help files have the file extension .hlp and are in lowercase (for example, esscmd.hlp).

Essbase files on UNIX systems are capitalized with proper case—the first letter is uppercase, and the remaining letters are lowercase. Table 137 gives examples of names for different file types:

Table 137. File Naming Examples for UNIX

File Type

Example

Database files

Mydb.db

Data files

Mydb.pag

Index files

Mydb.ind

Outline files

Mydb.otl

Rules files

Atlanta.rul

Data files to load

Atlanta.txt

Calculation scripts

Mycalc.csc

Report scripts

Myrepo.rep

Archive files

Mydb.arc

Application logs

Myapp.log

Note:

The application name is an exception to the above rule. The application name can be in lowercase.

Table 138 lists several examples of valid and invalid file names on UNIX systems:

Table 138. Valid and Invalid File Names on UNIX

Valid File Names

Invalid File Names

Model.csc

MODEL.CSC

Monthly.rep

Monthly.Rep

Forecast.otl

forecast.otl

Actuals.rul

AcTuAlS.rUl

My_File.txt

My_File.Txt

Note:

Essbase does not allow long file names for applications, databases, calculation scripts, reports, and other database files. All file names for artifacts you create must conform to the Windows 8.3 convention.

Transferring Compatible Files

If two servers are connected, you can create the application and database directories on the new server and use either FTP (File Transfer Protocol) or Administration Services to transfer the compatible application files. If the servers are not connected, you must redefine server information on the new server before reloading the database.

Using FTP to Transfer Files

Using FTP, you can transfer files directly between operating systems. You should transfer only the files that are compatible between operating systems, and you should transfer the files in binary mode.

If you have files with the wrong case on a UNIX server, Administration Services can see them but cannot open them. After you use FTP to transfer files, rename the files on the server to ensure that they are capitalized in the proper case. Alternatively, you can use FTP to rename the file when you transfer the file. For example:

ftp>put oldfile Newfile 

Using Administration Services to Transfer Files

Using Administration Services, you can transfer files from the client computer to the server in the following ways:

  • As part of an application migration. See “Migration Wizard” in Oracle Essbase Administration Services Online Help.

  • As part of a database migration. See “Copying Databases” in Oracle Essbase Administration Services Online Help.

  • One artifact at a time. See the topic for the individual artifact; for example, “Copying Rules Files,” in Oracle Essbase Administration Services Online Help.

Redefining Server Information

If the server you are porting to is not connected to the existing server, you must redefine some information on the new server.

  To redefine server information:

  1. To create users and specify their permissions, use Administration Services on the new server.

    See Granting Permissions to Users and Groups in Essbase Native Security Mode.

  2. To create the applications and databases that you want to port, use Administration Services on the new server.

    See Creating Applications and Databases.

  3. Copy the outline files (.otl) for the databases that you want to port from the old server to the same directory location on the new server. Ensure that the application is not running while you copy these files.

    See Creating and Editing Outlines.

  4. Copy compatible files from the old server to the new server.

    See Identifying Compatible Files.

  5. Reload the database.

    See Reloading the Database.

Reloading the Database

Database files, such as .db, .pag, .esm, and .ind, are not compatible between operating systems. If you port an application to a server on a different operating system, you must repopulate the database by reloading the data from a data file and a rules file (if applicable). One way you can reload is to export the data to a text file, transfer the text file to the new server, and then use the text file to load data. After the load is complete, calculate the new database.