Creating Applications and Databases

In This Section:

Process for Creating Applications and Databases

Understanding Applications and Databases

Understanding Database Artifacts

Creating Applications and Databases

Using Substitution Variables

Using Location Aliases

Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

Process for Creating Applications and Databases

  To create an application and database:

  1. Design the application.

    See Quick Start for Implementing Essbase.

  2. Create an application.

    See Creating an Application.

  3. Create a database.

    See Creating a Database.

  4. If necessary, set substitution variables at the Essbase Server, application, or database level.

    See Using Substitution Variables.

  5. If necessary, set a location alias for the database.

    See Using Location Aliases.

  6. Create the outline.

    See Creating and Changing Database Outlines.

Understanding Applications and Databases

An Essbase application is a management structure that contains one or more Essbase databases and related files. Essbase applications and databases reside on an 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. See Understanding Multidimensional Databases and Storage Allocation.

Understanding Database Artifacts

Files that are related to 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 Essbase Server, and can also be saved to a client computer or to other available network directories. You cannot, however, load data or calculate data on a client computer.

Essbase provides the following common types:

  • 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

  • Linked reporting objects (LROs)

  • Partition definitions

Some of these artifacts are optional, such as calculation scripts and LROs. See Application and Database File Types.

In Administration Services Console, database artifacts are displayed under their associated applications or database in the Enterprise View tree.

Understanding Database Outlines

Database outlines define the structure of a multidimensional database, including all the dimensions, members, aliases, properties, types, consolidations, and mathematical relationships. The structure defined in the outline determines how data is stored in the database.

When a database is created, Essbase creates an outline for that database automatically. The outline has the same name as the database (dbname.otl). For example, when the Basic database is created within the Sample application, an outline is created in the following directory:

ARBORPATH/app/sample/basic/basic.otl

See Creating a Database and Creating and Changing Database Outlines.

Understanding Data Sources

A data source is external data that is loaded into an Essbase database. The common types of data sources include the following:

  • Text files

  • Spreadsheet files

  • Spreadsheet audit log files

  • External databases, such as an SQL database

See Supported Data Sources.

Understanding Rules Files for Data Load and Dimension Build

An Essbase database contains no data when it is created. Data load rules files are sets of operations that Essbase performs on data from an external data source file as the data is loaded, or copied, into the Essbase database. Dimension build rules files create or modify the dimensions and members in an outline dynamically based on data in an external data source. Rules files are typically associated with a particular database, but you can define rules for use with multiple databases. One rules file can be used for both data loads and dimension builds. Rules files have a .rul extension.

See Rules Files and Working with Rules Files.

Understanding Calculation Scripts

Calculation scripts are text files that contain sets of instructions telling Essbase how to calculate data in the database. Calculation scripts perform calculations different from the consolidations and mathematical operations that are defined in the database outline. Because calculation scripts perform specific mathematical operations on members, they are typically associated with a particular database. You can, however, define a calculation script for use with multiple databases. Calculation scripts files have a .csc extension.

See Developing Calculation Scripts for Block Storage Databases.

Understanding Report Scripts

Report scripts are text files that contain data retrieval, formatting, and output instructions to create a report from the database. Report scripts are typically associated with a particular database, but you can define a report script for use with multiple databases. Report scripts have a .rep extension.

See Developing Report Scripts.

Understanding Security Definitions

Essbase provides a comprehensive system for managing access to applications, databases, and other artifacts. Each application and database contains its own security definitions that restrict user access.

See User Management and Security in EPM System Security Mode.

Understanding LROs

An LRO is an artifact associated with a specific data cell in an Essbase database. LROs can enhance data analysis capabilities by providing additional information on a cell.

An LRO can be any of the following:

  • A paragraph of descriptive text (a “cell note”)

  • A separate file that contains text, audio, video, or graphics

  • A URL for a Web site

  • A link to data in another Essbase database

See Linking Objects to Essbase Data.

Understanding Spreadsheet Queries

Within Spreadsheet Add-in, users can create and save queries using Query Designer (EQD). Users with access to the query can access it later. Query files created using Query Designer have a .eqd extension.

See the Oracle Essbase Spreadsheet Add-in User's Guide.

Understanding Member Select Definitions

Within Spreadsheet Add-in, users can define and save member retrievals with the member select feature. Member specification files have a .sel extension.

See the Oracle Essbase Spreadsheet Add-in User's Guide.

Understanding Triggers Definitions

The triggers feature enables efficient monitoring of data changes in a database. If data breaks rules that you specify in a trigger, Essbase logs relevant information in a file or, for some triggers, sends an e-mail alert; for example, to notify the sales manager if, in the Western region, sales for a month fall below sales for the equivalent month in the previous year.

See Monitoring Data Changes Using Triggers.

Creating Applications and Databases

Create an application and then create its databases. You can annotate the databases.

Creating an Application

When you create an application on the Essbase Server, Essbase creates a subdirectory for the application on the Essbase Server in the ARBORPATH/app directory. The new subdirectory has the same name as the application; for example, ARBORPATH/app/app1. In Administration Services Console, applications and databases are displayed in a tree structure in Enterprise View.

Before naming the application, see Naming Restrictions for Applications and Databases.

You can also create an application that is a copy of an existing application. See Copying or Migrating Applications.

  To create an application, use a tool:

Tool

Topic

Location

Administration Services

Creating Applications

Oracle Essbase Administration Services Online Help

MaxL

create application

Oracle Essbase Technical Reference

ESSCMD

CREATEAPP

Oracle Essbase Technical Reference

Creating a Database

When you create a database, Essbase creates a subdirectory for the database within the application directory. The new subdirectory has the same name as the database; for example, ARBORPATH/app/app1/dbname1. In Administration Services Console, applications and databases are displayed in a tree structure in Enterprise View.

You can create normal databases or currency databases. See Designing and Building Currency Conversion Applications.

Before naming the database, see Naming Restrictions for Applications and Databases.

  To create a database, use a tool:

Tool

Topic

Location

Administration Services

Creating Databases

Oracle Essbase Administration Services Online Help

MaxL

create database

Oracle Essbase Technical Reference

ESSCMD

CREATEDB

Oracle Essbase Technical Reference

Except for databases requiring use of the Currency Conversion option, creating one database per application is recommended.

Annotating a Database

A database note can provide useful information when you need to broadcast messages to users about the status of a database, deadlines for updates, and so on. Users can view database notes in Spreadsheet Add-in.

  To annotate a database, see “Annotating Databases” in the Oracle Essbase Administration Services Online Help.

Using Substitution Variables

Substitution variables are global placeholders for regularly changing information. Because changes to a variable value are reflected everywhere the variable is used, manual changes are reduced.

For example, many reports depend on reporting periods; if you generate a report based on the current month, you must update the report script manually every month. With a substitution variable, such as CurMnth, set on the server, you can change the assigned value each month to the appropriate time period. When you use the variable name in a report script, the information is dynamically updated when you run the final report.

You can use substitution variables with both aggregate storage and block storage applications in the following areas:

You can set substitution variables on the Essbase Server using Administration Services, MaxL, or ESSCMD. Set the variable at any of the following levels:

  • Essbase Server—providing access to the variable from all applications and databases on the Essbase Server

  • Application—providing access to the variable from all databases within the application

  • Database—providing access to the variable within the specified database

Rules for Setting Substitution Variable Names and Values

The following rules apply to substitution variable names and values:

  • The substitution variable name must comprise alphanumeric characters or underscores ( _ ) and cannot exceed the limit specified in Limits.

  • The substitution variable name cannot include nonalphanumeric characters, such as hyphens (-), asterisks (*), and slashes (/). Do not use spaces, punctuation marks, or brackets ([ ]) in substitution variable names used in MDX.

  • If substitution variables with the same name exist at server, application, and database levels, the order of precedence for the variables: a database-level substitution variable supersedes an application-level variable, which supersedes a server-level variable.

  • The substitution variable value may contain any character except a leading ampersand (&). The substitution variable value cannot exceed the limit specified in Limits.

  • To set a substitution variable value to a duplicate member name, use the qualified member name enclosed in double quotation marks; for example, a value for &Period could be “[2006].[Qtr1]”.

  • When specifying use of a substitution variable, do not insert a substitution variable as a part of a qualified name. For example, it is invalid to specify “[2004].[&CurrentQtr]”.

  • If a substitution variable value is a member name that begins with a numeral or contains spaces or any of the special characters listed in Naming Restrictions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values, different rules apply for how you enter the variable:

    • Enclose the member-name value in brackets ([ ]) if it is used in MDX statements.

    • Enclose the member-name value in quotation marks (“ ”) if it is not used in MDX statements.

  • If a substitution variable value is numeric, different rules apply for how you enter the variable:

    • If it is not used in MDX statements, enclose a substitution variable value in quotation marks; for example, if the variable name is Month, and its corresponding value is 01 (corresponding to January), place quotation marks around 01 (“01”). Substitution variables usually are used with block storage databases; they are not used in MDX statements.

    • If it is used in MDX statements only, such as in formulas in aggregate storage outlines, and the value is numeric or a member name, do not enclose the value in quotation marks.

      Note:

      If a substitution variable value is numeric or a member name starting with a numeral or containing the special characters referred to above is to be used both in MDX and non-MDX situations, create two substitution variables, one without the value enclosed in quotation marks and one with the value in quotation marks.

Setting Substitution Variables

You can set substitution variables on the Essbase Server at the server, application, or database level. Before setting a substitution variable, see Rules for Setting Substitution Variable Names and Values.

  To set a substitution variable, use a tool:

Tool

Topic

Location

Administration Services

Managing Substitution Variables

Oracle Essbase Administration Services Online Help

MaxL

alter system

alter application

alter database

Oracle Essbase Technical Reference

ESSCMD

CREATEVARIABLE

Oracle Essbase Technical Reference

To ensure that a new substitution variable value is available in formulas, partition definitions, and security filters, stop and restart the application. All other uses of substitution variables are dynamically resolved when used.

Deleting Substitution Variables

You may need to delete a substitution variable that is no longer used.

  To delete a substitution variable, use a tool:

Tool

Topic

See

Administration Services

Managing Substitution Variables

Oracle Essbase Administration Services Online Help

MaxL

alter system

alter application

alter database

Oracle Essbase Technical Reference

ESSCMD

DELETEVARIABLE

Oracle Essbase Technical Reference

Updating Substitution Variables

You can modify or update existing substitution variables. Before updating a substitution variable, see Rules for Setting Substitution Variable Names and Values.

  To update a substitution variable, use a tool:

Tool

Topic

See

Administration Services

Managing Substitution Variables

Oracle Essbase Administration Services Online Help

MaxL

alter system

alter application

alter database

Oracle Essbase Technical Reference

ESSCMD

UPDATEVARIABLE

Oracle Essbase Technical Reference

Copying Substitution Variables

You can copy substitution variables to any Essbase Server, application, or database to which you have appropriate access.

  To copy a substitution variable, see “Copying Substitution Variables” in the Oracle Essbase Administration Services Online Help.

Using Location Aliases

A location alias is a descriptor for a data source. A location alias maps an alias name for a database to the location of that database. A location alias is set at the database level and specifies an alias, a server, an application, a database, a user name, and a password. Set the location alias on the database on which the calculation script is run.

After you create a location alias, you can use the alias to refer to that database. If the location of the database changes, edit the location definition accordingly.

You can use location aliases only with the @XREF and @XWRITE functions. With @XREF, you can retrieve a data value from another database to include in a calculation on the current database. In this case, the location alias points to the database from which the value is to be retrieved. With @XWRITE, you can write values to another Essbase database, or to the same database. See the Oracle Essbase Technical Reference.

The following considerations apply when using location aliases in secure (SSL) mode:

  • To enable Essbase to use SSL connectivity, you must set ENABLESECUREMODE to TRUE.

  • If the location alias is being set up to a secure port, you must append :secure to the server name specification. For example, using MaxL,

    create location alias EasternDB from Sample.Basic to East.Sales at Easthost:6423:secure as User1 identified by password1;

Creating Location Aliases

You can create a location alias for a particular database.

  To create a location alias, use a tool:

Tool

Topic

Location

Administration Services

Creating Location Aliases

Oracle Essbase Administration Services Online Help

MaxL

create location alias

Oracle Essbase Technical Reference

ESSCMD

CREATELOCATION

Oracle Essbase Technical Reference

Editing or Deleting Location Aliases

You can edit or delete location aliases that you created.

  To edit or delete a location alias, use a tool:

Tool

Topic

Location

Administration Services

Editing or Deleting Location Aliases

Oracle Essbase Administration Services Online Help

MaxL

display location alias

drop location alias

Oracle Essbase Technical Reference

ESSCMD

LISTLOCATIONS

DELETELOCATION

Oracle Essbase Technical Reference