Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
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:
To create an application and database, follow these steps:
For more information about applications and database, see Understanding Applications and Databases and Understanding Database Objects.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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 |
---|---|---|
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 |
---|---|---|
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.
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).
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:
Keep in mind the following rules when 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 |
---|---|---|
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 |
---|---|---|
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 |
---|---|---|
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.
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.
You can create a location alias for a particular database.
To create a location alias, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
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 |
---|---|---|
![]() |