Creating Applications and Databases Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Creating Applications and Databases


An Analytic Services application is a container for a database and its related files. This chapter provides an overview of Analytic Services applications and databases and explains how to create applications and databases and some Analytic Services objects, including substitution variables and location aliases. For information on everyday management of applications, databases, and their associated files, see the optimization and system administration information in this guide.

Note: The information in this chapter is designed for block storage databases. Some of the information is not relevant to aggregate storage databases. For detailed information on the differences between aggregate and block storage, see Comparison of Aggregate and Block Storage. For information on creating aggregate storage applications, see Aggregate Storage Applications, Databases, and Outlines.

This chapter includes the following topics:

Process for Creating Applications and Databases

To create an application and database, follow these steps:

  1. Design the application. See Quick Start for Implementing Analytic Services,

  2. Create a new application. See Creating a New Application.

  3. Create a new database. See Creating a New Database.

  4. If necessary, set substitution variables at the Analytic 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.

For more information about applications and database, see Understanding Applications and Databases and Understanding Database Objects.

Understanding Applications and Databases

An Analytic Services application is a management structure that contains one or more Analytic Services databases and related files. Analytic Services applications and databases reside on an Analytic Server. The server machine can store multiple applications.

An Analytic Services 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. For more information about multidimensional databases, see Understanding Multidimensional Databases. For more information about how Analytic Services stores data, see Storage Allocation.

This diagram shows the relationships among the parts of an application:

Figure 46: Parts of an Analytic Services Application

Understanding Database Objects

Files that are related to databases are called objects. Database objects perform actions against one or more Analytic Services databases, such as defining calculations or reporting against data. By default, objects are stored in their associated database folder on the Analytic Server. They can also be saved to a client machine or to other available network directories. However, you cannot load data or calculate data on a client machine.

In Analytic Services, the common types of objects include the following:

Some of these objects are optional, such as calculation scripts and linked reporting objects. For a complete list of application and database file types, see Application and Database File Types.

In Administration Services Console, database objects 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, Analytic Services 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 
 

For information about creating outlines, see Creating a New Database and Creating and Changing Database Outlines.

Understanding Data Sources

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

For a list of supported data sources, see Supported Data Sources.

Understanding Rules Files for Data Load and Dimension Build

An Analytic Services database contains no data when it is first created. Data load rules files are sets of operations that Analytic Services performs on data from an external data source file as the data is loaded, or copied, into the Analytic Services 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. A single rules file can be used for both data loads and dimension builds. Rules files have the .RUL extension.

For information about creating rules files, see Rules Files and Creating Rules Files.

Understanding Calculation Scripts

Calculation scripts are text files that contain sets of instructions telling Analytic Services how to calculate data in the database. Calculation scripts perform different calculations than 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 the .CSC extension.

For information about creating calculation scripts, see Developing Calculation Scripts.

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 the .REP extension.

For information about creating report scripts, see Developing Report Scripts.

Understanding Security Definitions

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

For information about setting up and maintaining security information, see Managing Security for Users and Applications.

Understanding Linked Reporting Objects

A linked reporting object is an object associated with a specific data cell in an Analytic Services database. Linked reporting objects can enhance data analysis capabilities by providing additional information on a data point.

A linked reporting object can be any of the following:

For a comprehensive discussion about using linked reporting objects, see Linking Objects to Analytic Services Data.

Understanding Spreadsheet Queries

Within Spreadsheet Add-in, users can create and save queries using Query Designer (EQD). The queries can be accessed at a later time by any user with access to the query. Query files created using Query Designer have the .EQD extension.

For more information, see the Essbase Spreadsheet Add-in User's Guide for Excel.

Understanding Member Select Definitions

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

For more information, see the Essbase Spreadsheet Add-in User's Guide for Excel.

Understanding Triggers Definitions

The triggers feature provided by Analytic Services enables efficient monitoring of data changes in a database.Triggers is licensed separately from Analytic Services. If data breaks rules that you specify in a trigger, Analytic Services can log relevant information in a file or, for some triggers, can send an email alert (to a user or system administrator). For example, you might want 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.

For information on designing, creating, and administering triggers, see Monitoring Data Changes Using Triggers.

Creating Applications and Databases

Since applications contain one or more databases, first create an application and then create databases. If desired, annotate the databases. The following sections describe how to create applications, databases, and database notes:

Creating a New Application

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

Be sure to consult Rules for Naming Applications and Databases before entering the application name.

You can also create a new application that is a copy of an existing application. For more information, see Copying or Migrating Applications.

To create a new application, use any of the following methods:


Tool
Topic
Location

Administration Services

Creating Applications

Essbase Administration Services Online Help

MaxL

create application

Technical Reference

ESSCMD

CREATEAPP

Technical Reference



Creating a New Database

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

You can create normal databases or currency databases. For more information on currency databases, see Designing and Building Currency Conversion Applications.

Be sure to consult Rules for Naming Applications and Databases before entering the database name.

To create a new database, use any of the following methods:


Tool
Topic
Location

Administration Services

Creating Databases

Essbase Administration Services Online Help

MaxL

create database

Technical Reference

ESSCMD

CREATEDB

Technical Reference



Annotating a Database

A database note can provide useful information in situations where 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. In Excel, for example, users use the Note button in the Connect dialog box.

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

Rules for Naming Applications and Databases

When naming applications and databases, follow these rules:

Enter the name in the case you want it to appear in. The application or database name will be created exactly as you enter it. If you enter the name as all capital letters (for instance, NEWAPP), Analytic Services will not automatically convert it to upper and lower case (for instance, Newapp).

Using Substitution Variables

Substitution variables act as global placeholders for information that changes regularly; each variable has a value assigned to it. The value can be changed at any time by the database designer; thus, manual changes are reduced.

For example, many reports depend on reporting periods; if you generate a report based on the current month, you have to 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 in calculation scripts, report scripts, or in Spreadsheet Add-in. You cannot use substitution variables in formulas that you apply to the database outline. For information about using substitution variables, refer to the following chapters:

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

Rules for Setting Substitution Variable Names and Values

Keep in mind the following rules when setting substitution variables:

Setting Substitution Variables

You can set substitution variables on the Analytic Server at the server, application, or database level. Be sure to consult Rules for Setting Substitution Variable Names and Values before setting a substitution variable.

To set a substitution variable, use any of the following methods:


Tool
Topic
Location

Administration Services

Managing Substitution Variables

Essbase Administration Services Online Help

MaxL

alter system

alter application

alter database

Technical Reference

ESSCMD

CREATEVARIABLE

Technical Reference



Deleting Substitution Variables

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

To delete a substitution variable, use any of the following methods:


Tool
Instructions
For More Information

Administration Services

Managing Substitution Variables

Essbase Administration Services Online Help

MaxL

alter system

alter application

alter database

Technical Reference

ESSCMD

DELETEVARIABLE

Technical Reference



Updating Substitution Variables

You can modify or update existing substitution variables. Be sure to consult Rules for Setting Substitution Variable Names and Values before updating a substitution variable.

To update a substitution variable, use any of the following methods:


Tool
Instructions
For More Information

Administration Services

Managing Substitution Variables

Essbase Administration Services Online Help

MaxL

alter system

alter application

alter database

Technical Reference

ESSCMD

UPDATEVARIABLE

Technical Reference



Copying Substitution Variables

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

To copy a substitution variable, see "Copying Substitution Variables" in 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 username, and a password. You need database designer permissions to set location aliases.

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

Note: 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. For more information on @XREF, see the Technical Reference.

Creating Location Aliases

You can create a location alias for a particular database.

To create a location alias, use any of the following methods:


Tool
Topic
Location

Administration Services

Creating Location Aliases

Essbase Administration Services Online Help

MaxL

create location alias

Technical Reference

ESSCMD

CREATELOCATION

Technical Reference



Editing or Deleting Location Aliases

You can edit or delete location aliases that you previously created.

To edit or delete a location alias, use any of the following methods:


Tool
Topic
Location

Administration Services

Editing or Deleting Location Aliases

Essbase Administration Services Online Help

MaxL

display location alias

drop location alias

Technical Reference

ESSCMD

LISTLOCATIONS

DELETELOCATION

Technical Reference





Hyperion Solutions Corporation link