Creating Applications and Databases

A Hyperion Essbase application is a container for a database and its related files. In addition to the database, an application can include scripts that are used to load data into the database, calculate derived values, and prepare reports. This chapter explains how to work with Hyperion Essbase applications and databases. For information on copying, renaming and deleting applications, databases and their associated files, see Working with Hyperion Essbase Files and Cross-Platform Environments.

This chapter includes the following sections:

• Hyperion Essbase Applications
• Process for Creating Databases
• Starting and Stopping Applications and Databases
• Using the Application Desktop Window
• Deciding Where to Build an Application
• Creating Applications and Databases
• Annotating a Database
• Using Dynamic Calculations
• Using Substitution Variables
• Using Location Aliases

Hyperion Essbase Applications

The Hyperion Essbase Application Manager provides a Windows-based environment where you can create and maintain Hyperion Essbase applications. Application development includes building outlines and dimensions, performing data loading and calculations, and defining security access.

A Hyperion Essbase application is a collection of one or more of the following types of files:

• Multidimensional databases
• Rules for loading data
• Scripts to calculate your data
• Scripts to generate reports on your data
• Security information and application security logs

The application and its parts usually reside on the server machine, but pieces of it can reside on your client machine. The server machine can store multiple applications. The following diagram shows the relationship between the parts of an application:

Figure 7-1: Parts of a Hyperion Essbase Application

When you start an application, the following actions can happen:

• Users can connect to the application.
• The application can respond to commands from the server.
• Users can change the settings of the application.
• Data and user security are enabled.
• Each database in the application can start.

For information on how to start an application, see Starting and Stopping Applications and Databases.

Multidimensional Database Outlines

Database outlines define the structure of a multidimensional database, including all the dimensions, members, tags, types, consolidations, and mathematical relationships. Data is stored in the database according to the structure defined in the outline. See Basic Architectural Elements for information on creating database outlines.

Data Load and Dimension Build Rules

Data load rules are sets of operations that Hyperion Essbase performs on data from an external data source file as it is loaded, or copied, into the Hyperion Essbase database. You need to load data into a Hyperion Essbase database because it contains no data when you create a database for the first time. Specifying data load rules is the most common way to load data into the database. Data load rules files are typically associated with a particular database, but you can define rules for use with multiple databases.

Dimension build rules can also load data into the database, and then modify the database outline based on data in the external data source file.

See Introducing Data Loading for information on creating data load rules.

See Introducing Dynamic Dimension Building and Building Dimensions Using a Rules File for information on creating dimension build rules.

Calc Scripts

Calc scripts are text files that contain instructions to calculate data in the database. Calc (calculation) scripts perform different calculations than the consolidations and mathematical operations that you define in the database outline. Because calc scripts perform specific mathematical operations on members, they are typically associated with a particular database. You can, however, define a calc script for use with multiple databases. See Developing Calc Scripts for information on creating calc scripts.

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. See Developing Report Scripts for information on creating report scripts.

Security Definitions

With Hyperion Essbase Application Manager, you also maintain user information, security information, application activity logs, and server activity logs. This information is saved on the Hyperion Essbase server. Within your organization, you can have multiple servers, each with its own users, applications, and databases. See Managing Security at Global and User Levels for more information about setting up and maintaining security information.

Process for Creating Databases

To implement a multidimensional database you need to complete a number of steps, from creating an application through optimizing your database performance. For a complete list of these steps, see Steps for Implementing Hyperion Essbase.

Starting and Stopping Applications and Databases

To start or stop an application:

1. Connect to the server on which the application resides.
2. Select the application from the Application Desktop window.
3. Select Application > Start/Stop. If you are stopping the application, a confirmation dialog box is displayed:

   Figure 7-2: Stop Application Dialog Box

   

4. Click Yes to stop the application. Hyperion Essbase unloads the application from memory.

You can also use alter system in MaxL, or the LOADAPP and UNLOADAPP commands in ESSCMD, to start and stop an application. See the online Technical Reference in the DOCS directory for information.

To a start or stop a database:

1. Select the database in the Application Desktop window.
2. Select Database > Start/Stop. If you are stopping the database, a confirmation dialog box is displayed:

   Figure 7-3: Stop Database Dialog Box

   

3. Click Yes to stop the database. Hyperion Essbase unloads the database from memory.

You can also use alter application in MaxL, or the LOADDB and UNLOADDB commands in ESSCMD, to start and stop a database. For more information, see the online Technical Reference in the DOCS directory.

Using the Application Desktop Window

When you start the Hyperion Essbase Application Manager, an icon is displayed at the bottom of the window. This is the Application Desktop window for client-based applications. When you connect to a Hyperion Essbase server, a window is displayed that lists all the applications on the server. This is the Application Desktop window for server-based applications.

Figure 7-4: Application Desktop Window

Before you can work with database outlines, calc scripts, report scripts, data loading or dimension building rules, you must select an existing application or create a new one.

1. Select the application name from the Applications list box. The Databases list box then displays the names of all the databases in the selected application.
2. Select a database name from the list box. The Application Desktop window is displayed as follows:

   Figure 7-5: Application Desktop Window with Database Selected

   

When you select an application and database from the list boxes, select one of the following buttons to work with outlines, scripts, and rules files.

	The Outline button lists all database outlines for the selected database. This button performs no action if (all dbs) is selected.
	The Calc Script button lists all calc scripts available for the selected application or database.
	The Report Script button lists all report scripts available for the selected application or database.
	The Data Load Rules button lists all rules files available for the selected application or database.

• To open one of these files, select it from the list, and then select Open.
• To create a new calc script, report script, or rules file, select the appropriate button, and click New.
• To create a new outline, see Creating a New Database.
• To run a calc script or report script, select the file from the list, and then select Run.
• Click Help for details about this window.

Deciding Where to Build an Application

Before creating an application and database, you should decide:

• Whether to build the application and database on the Hyperion Essbase server, where other users can access them, or on your client machine.

  You can create an application on your client machine, or you can connect to a Hyperion Essbase server and build it there. Building an application on the client allows you to develop and test it in an isolated environment. However, it does limit what can be done with the application.

  You must build your application on the server if you want to:

  • Give other users access to the application.
  • Start the application automatically when the server starts.
• Whether to copy an existing application and database or to create a new one .
• What tools to use--Hyperion Essbase Application Manager's graphical user interface or ESSCMD commands.

Creating Applications and Databases

You can use either Hyperion Essbase Application Manager or ESSCMD to create an new application or database.

Creating a New Application

To create a new application with Hyperion Essbase Application Manager:

1. Select File > New > Application. The following dialog box is displayed:

   Figure 7-6: Create New Application Dialog Box

   

   Note:

   Depending on your license agreement, you may see a Storage Type list box.

2. Type the name of the new application in the Application Name text box. This must be a unique name of 8 characters or less.
3. Select the client or server on which to create the application:
   • To create a client-based application, click Client. Hyperion Essbase creates a subdirectory for the application within the essbase\client directory, where essbase is the drive and directory into which you installed your Hyperion Essbase client. The new subdirectory has the same name as the application.
   • To create a server-based application, click Server and select one of the servers listed in the Server list box. If the Server list box is empty, follow the directions for connecting to a server. Hyperion Essbase creates a subdirectory for the application within the server's APP directory. The new subdirectory has the same name as the application.

You can also use create application in MaxL or the CREATEAPP command in ESSCMD to perform this task. For more information, see the online Technical Reference in the DOCS directory.

Creating a New Database

To create a database using Hyperion Essbase Application Manager:

1. Open the Application Desktop window:
   • For client-based applications, click the icon at the bottom of the Hyperion Essbase Application Manager window.
   • For server-based applications, connect to the appropriate server.

     The Application Desktop window is displayed:

     Figure 7-7: Application Desktop Window

     

2. From the Applications list box, select the name of the application in which you want to create the database.
3. Select File > New > Database. The following dialog box is displayed:

   Figure 7-8: Create New Database Dialog Box

   

4. Type the name of the new database in the Database name text box. The Hyperion Essbase Application Manager limits the database name to 8 characters.
5. In most cases, the database type is Normal. To create a currency database, select a database type of Currency. For more information on currency databases, see Designing and Building Currency Conversion Applications
6. Click OK to create the database. Hyperion Essbase creates a subdirectory for the database within the application directory on the server or client. For information on the application directory, see Creating Applications and Databases. The new subdirectory has the same name as the application.

You can also use create database in MaxL or the CREATEDB command in ESSCMD to perform this task. For more information, see the online Technical Reference in the DOCS directory.

Rules for Naming Applications and Databases

When naming applications and databases, follow these rules:

• The length must be 8 characters or fewer.
• Do not use the following special characters anywhere in the name:

  *	(asterisks)
  \	(backslashes)
  []	(brackets)
  :	(colons)
  ,	(commas)
  =	(equal signs)
  >	(greater than signs)
  <	(less than signs)
  .	(periods)
  +	(plus signs)
  ?	(question marks)
  "	(double quotation marks)
  ;	(semicolons)
  `	(single quotation marks)
  |	(vertical bars)

• Do not use spaces anywhere in the name.

Annotating a Database

When you have created a database, you should annotate it. 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.

Hyperion Essbase Spreadsheet Add-in users can view the database note from Hyperion Essbase Spreadsheet Add-in. In Excel, for example, the Note button in the Connect dialog box lets you view database information.

To annotate a database:

1. Select the database you want to annotate from the Application Desktop window.
2. Select Database > Set Note. The following dialog box is displayed:

   Figure 7-9: Set Database Note Dialog Box

   

3. Type some text in the Note text box.
4. Click OK.

Using Dynamic Calculations

When designing and creating your Hyperion Essbase database, you might want to make use of dynamic calculations. Dynamically calculating some data values in your database can significantly improve the overall calculation performance of your database. For more information, see Dynamically Calculating Data Values and Calculating Time Series Data

Using Substitution Variables

When designing your Hyperion Essbase application, you may want to make use of 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 your report script, the information is dynamically updated when you run the final report.

You can use substitution variables in calc scripts, report scripts, or in Hyperion Essbase 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:

• For calc scripts, see Developing Calc Scripts
• For Report Writer, see Developing Report Scripts
• For Hyperion Essbase Spreadsheet Add-in, see the Hyperion Essbase Spreadsheet Add-in User's Guide.

You can set substitution variables on the server using either Hyperion Essbase Application Manager or ESSCMD. Set the variable at any of the following levels:

• Server--providing access to the variable from all applications and databases on the server
• Application--providing access to the variable from all databases within the application
• Database--providing access to the variable within the specified database

Guidelines for Setting Substitution Variables

Keep in mind the following guidelines when setting substitution variables:

• The substitution variable name must be composed of alphanumeric characters or underscores (_) and cannot exceed 80 characters. This name cannot include non-alphanumeric characters, such as hyphens (-), asterisks (*), and slashes(/).
• The value of the substitution variable cannot exceed 256 characters. You can use any combination of characters in the value name. The value may contain any character except the leading ampersand (&).
• If the value of the substitution variable is numeric, you must enclose it in quotes. For example, if the variable name is Month and its corresponding value is 01 (corresponding to January), place quotes around 01 ("01").

Setting a Substitution Variable

You can set substitution variables on the server at the server, application, or database level.

To set a substitution variable using Hyperion Essbase Application Manager:

1. From the Application Desktop window, select Server > Substitution Variables.

   Hyperion Essbase displays the Substitution Variables dialog box:

   Figure 7-10: Creating a Substitution Variable

   

2. In the Server list box, select the server to apply the variable to.
3. In the Application list box, select the application to apply the variable to. Select "(all apps)" to apply the variable to all applications on the server.
4. In the Database list box, select the database to apply the variable to. Select "(all dbs)" to apply the variable to all databases in the selected application.
5. In the Variable box, type the new variable name. See Guidelines for Setting Substitution Variables.
6. In the Value box, type the value name. See Guidelines for Setting Substitution Variables.
7. Click Set to apply the new variable and value.

You can also use MaxL's alter system, alter application, or alter database, or the CREATEVARIABLE command in ESSCMD, to perform this task. For more information, see the online Technical Reference in the DOCS directory.

Deleting a Substitution Variable

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

To delete a substitution variable using Hyperion Essbase Application Manager:

1. From the Application Desktop window, select Server > Substitution Variables.

   Hyperion Essbase displays the Substitution Variables dialog box:

   Figure 7-11: Deleting a Substitution Variable

   

2. In the Server list box, select the server to delete the variable from.
3. In the Application list box, select the application to delete the variable from. If the variable applies to all applications on the server, select "(all apps)".
4. In the Database list box, select the database to delete the variable from. If the variable applies to all databases on the server, select "(all dbs)".
5. In the Variable list, select the variable you want to delete.
6. Click Delete to delete the variable and value.

You can also use MaxL's alter system, alter application, or alter database, or the DELETEVARIABLE command in ESSCMD, to perform this task. For more information, see the online Technical Reference in the DOCS directory.

Updating a Substitution Variable

You can modify or update existing substitution variables.

To update a substitution variable using Hyperion Essbase Application Manager:

1. From the Application Desktop window, select Server > Substitution Variables.
2. In the Server list box, select the server where the variable is set.
3. In the Application list box, select the application where the variable is set. If the variable applies to all applications on the server, select "(all apps)".
4. In the Database list box, select the database where the variable is set. If the variable applies to all databases in the server, select "(all dbs)".

   The name of the existing variable and its current value are displayed in the list.

5. In the Variable box, type the name of the existing variable exactly as it is displayed in the Variable list.
6. In the Value box, type the value name. See Guidelines for Setting Substitution Variables.
7. Click Set to apply the new value to the existing variable.

   In the Value list, the previous value is replaced with the new value.

   Figure 7-12: Updating a Substitution Variable

   

You can also use MaxL's alter system, alter application, or alter database, or the UPDATEVARIABLE command in ESSCMD, to perform this task. For more information, see the online Technical Reference in the DOCS directory.

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 privileges 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.

Creating Location Aliases

You can create a location alias for a particular database.

To create a location alias using Hyperion Essbase Application Manager:

1. Click the Application Desktop window.
2. Select Database > Location Aliases.

   Hyperion Essbase displays the Location Aliases dialog box.

   Figure 7-13: Location Aliases Dialog Box

   

3. Enter the appropriate information in the following text boxes:
   • In the Alias text box, enter the alias, or alternate name, for the remote database.
   • In the Server text box, enter either the name or the IP address for the server that contains the remote database.
   • In the Application text box, enter the name of the application.
   • In the Database text box, enter the name of the database.
   • In the User name text box, enter the user name.
   • In the Password text box, enter the password.
4. Click Set to apply the settings.

   Hyperion Essbase displays the settings in the lower text box.

   Figure 7-14: Creating a Location Alias

   

5. Click OK.

You can also use create location alias in MaxL, or the CREATELOCATION command in ESSCMD, to perform this task. For more information, see the online Technical Reference in the DOCS directory.

Editing or Deleting Location Aliases

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

To edit or delete a location alias using Hyperion Essbase Application Manager:

1. Click the Application Desktop window.
2. Select Database > Location Aliases.

   Hyperion Essbase displays the Location Aliases dialog box.

   Figure 7-15: Editing or Deleting a Location Alias

   

3. From the lower text box, select the location alias that you want to edit, and follow one of these steps:
   • To edit the location alias, edit the text in the text boxes.
   • To delete the entire location alias, click Delete.
4. Click OK.

You can use display location alias in MaxL, or the LISTLOCATIONS command in ESSCMD, to list existing location aliases. You can use drop location alias in MaxL or the DELETELOCATION command in ESSCMD to remove location aliases. For more information, see the online Technical Reference in the DOCS directory.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.