In This Section:
Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
To create an application and database:
Design the application.
Create an application.
Create a database.
See Creating a Database.
If necessary, set substitution variables at the Essbase Server, application, or database level.
If necessary, set a location alias for the database.
Create the outline.
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.
Some of these artifacts are optional, such as calculation scripts and LROs. See Application and Database File Types.
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:
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.
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.
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.
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.
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.
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.
To create a database, use a tool:
Except for databases requiring use of the Currency Conversion option, creating one database per application is recommended.
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.
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.
See Using MDX Formulas.
See these Oracle Essbase Administration Services Online Help topics: “Setting Headers in the Rules File” and “Mapping Field Names.”
See Oracle Essbase SQL Interface Guide.
See Oracle Essbase SQL Interface Guide.
See the Oracle Essbase Spreadsheet Add-in User's Guide.
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 “.[Qtr1]”.
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 Using Dimension and Member Names in Calculation Scripts, Report Scripts, Formulas, Filters, Substitution Variable Values and Environment Variable Values, 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 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 set a substitution variable, use a tool:
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.
To delete a substitution variable, use a tool:
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:
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.
You can use location aliases only with the @XREF function. With this function, 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. See the Oracle Essbase Technical Reference.
To edit or delete a location alias, use a tool: