In This Section:
Process for Creating Applications and Databases
Understanding Applications and Databases
Understanding Database Artifacts
Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Also see:
To create an application and database:
See Creating a Database.
If necessary, set substitution variables at the Essbase Server, application, or database level.
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.
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:
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.
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.otlSee Creating a Database and Creating and Changing Database Outlines.
A data source is external data that is loaded into an Essbase database. The common types of data sources include the following:
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.
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.
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.
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.
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:
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.
Within Spreadsheet Add-in, users can define and save member retrievals with the member select feature. Member specification files have a .sel extension.
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.
Create an application and then create its databases. You can annotate the databases.
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.
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.
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:
Aggregate storage outline formulas
See Using MDX Formulas.
Calculation scripts (block storage databases only)
See Developing Calculation Scripts for Block Storage Databases.
Data load rules file header definitions and field definitions. You can enter variable names for dimension and member names.
See these Oracle Essbase Administration Services Online Help topics: “Setting Headers in the Rules File” and “Mapping Field Names.”
Data source name (DSN) specifications in rules files for SQL data sources
See Oracle Essbase SQL Interface Guide.
SELECT, FROM, or WHERE clauses in rules files for SQL data sources
See Oracle Essbase SQL Interface Guide.
See the Oracle Essbase Spreadsheet Add-in User's Guide.
You can set substitution variables on the Essbase Server using Administration Services, MaxL, or ESSCMD. Set the variable at any of the following levels:
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:
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.
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.
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 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.
You may need to delete a substitution variable that is no longer used.
You can modify or update existing substitution variables. Before updating a substitution variable, see Rules for Setting Substitution Variable Names and Values.
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;