Creating and Changing Database Outlines

A database outline is made up of dimensions and members organized hierachically. The Outline Editor provides a graphical representation of the dimension hierarchy. Each dimension and consolidated level in a database is represented in a collapsible tree diagram. The branches immediately below the database name are the dimensions, and the branches below a dimension name are members. For more background information on outlines, see Multidimensional Concepts

This chapter explains how to create an Hyperion Essbase OLAP Server database outline using the Outline Editor. You can also change outlines using data sources and rules files. For more information, see Introducing Dynamic Dimension Building All examples in this chapter are based on the Sample Basic database shipped with Hyperion Essbase.

This chapter contains the following sections:

• Creating Outlines
• Verifying and Saving Outlines
• Renaming Dimensions and Members
• Adding Dimensions and Members to Outlines
• Positioning Dimensions and Members
• Naming Generations and Levels
• Customizing the Outline Editor

For information on setting properties, see Setting Dimension and Member Properties

Creating Outlines

The database outline defines the structure of the database. For more information about outlines, see Arranging Dimensions into Hierarchies.

1. Create a new database. See Creating a New Database.
2. Open the new, blank outline. See Opening Outlines.
3. Add dimensions and members to the outline using one of the following means:
   • Manually add the dimensions and members. See Adding Dimensions and Members to Outlines.
   • Copy an existing outline. See Copying Outlines.
4. Verify and save the outline. See Verifying and Saving Outlines.

Opening Outlines

When you open an outline in the Outline Editor, Hyperion Essbase loads the outline into memory on your client machine and you can view and manipulate its dimensions and members graphically.

1. In the Application Desktop window, select the application to view from the Applications list box; for example, the Sample application.
2. Select the database from the Databases list; for example, the Basic database.

   Figure 8-1: Application Desktop Window

   

   If Lock file is selected, other users cannot edit the outline until you close the outline again.

3. Click the Outline button, .
4. Click Open to open the selected outline.

   Figure 8-2 shows the Sample Basic outline in the Outline Editor:

   Figure 8-2: Outline Editor

   

Each dimension and member is listed after a tree node. The node indicates the dimension or member's position in the hierarchy:

	Indicates that the branch can be expanded. For example, double-clicking on Qtr2 expands the branch to show Apr, May, and Jun. You can also expand a member by selecting the member and choosing Outline > Expand to Children (to expand the outline one level) or by choosing Outline > Expand to Descendants (to expand the outline to the lowest level).
	Indicates that the branch is expanded and can be collapsed. For example, double-clicking on Qtr1 collapses the branch to show Qtr1 but not its children. You can also collapse a member by selecting the member and choosing Outline > Collapse to Ancestor.
	Indicates that the member has no children.

When you open the Outline Editor, the Hyperion Essbase Application Manager's menu items change. The Outline Editor contains a toolbar that provides shortcuts to Outline Editor commands and an editing area that displays the current outline. For help with any of the toolbar buttons or menu commands, use the Help menu.

Copying Outlines

1. Select the database to copy in the Application Desktop window.
2. Select Database > Copy. Hyperion Essbase copies the entire database.

You can also use create database as in MaxL or the COPYDB command in ESSCMD to copy a database. For example, to use MaxL to copy the Sample Basic database to the NewApp application and the NewDB database: create database NewApp.NewDb as Sample.Basic; See the online Technical Reference in the DOCS directory for information.

1. Create a new database by choosing File > New > Database.
2. Open the outline you want to copy in the Outline Editor. If you don't know how to do this, see Opening Outlines.
3. Select File > Save As to open the Save Server Object dialog box.

   Figure 8-3: Save Server Object Dialog Box

   

4. Select the name of the new database (the one that you created in Step 1) in the Database list box.

   A dialog box is displayed, asking if you should overwrite the existing database.

5. Click Yes.

Verifying and Saving Outlines

When you save an outline to the server, Hyperion Essbase Application Manager verifies the outline to ensure that:

• All member and alias names are valid. Members and aliases cannot have the same name as other members, aliases, generations, or levels. The section Rules for Naming Dimensions and Members presents conventions for member names.
• Only one dimension is tagged as accounts, time, currency type, or country.
• Shared members are valid. Shared members with formulas or children cannot occur before the actual members on which they are based. Shared members do not have any attributes or UDAs. Shared members must have a real member with the same name.
• Level 0 members are not tagged as label only.
• Shared or label only members do not have formulas.
• The currency category and currency name are valid for the currency outline.
• Dynamic Calc members do not have more than 100 children.
• Boolean attribute dimensions have only two members. Boolean attribute names are the same as the Boolean attribute member names defined for the database.
• The level 0 member name of a date attribute dimension must match the date format name setting (mm-dd-yyyy or dd-mm-yyyy). If the dimension has no members, because the dimension name is the level 0 member, the dimension name must match the setting.
• The level 0 member name of a numeric attribute dimension is a numeric value. If the dimension has no members, because the dimension name is the level 0 member, the dimension name must be a numeric value.
• Attribute dimensions are located at the end of the outline, following all standard dimensions.
• Level 0 Dynamic Calc members of standard dimensions have a formula.
• Formulas for members are valid. Hyperion Essbase performs formula validation only when the Outline Editor toggle-option Options>Server Formula Validation is set ON. (A check mark next to the option name indicates server formula validation is set ON.)

During outline verify, Hyperion Essbase Application Manager also performs the following conversions to appropriate numeric attribute dimension member names and displays them in the outline:

• It moves minus signs in member names from the front to the end of the name; for example, -1 becomes 1-.
• It strips out leading or trailing zeroes in member names; for example, 1.0 becomes 1, and 00.1 becomes 0.1.

For more information about numeric attribute dimensions, see Attribute Types.

Verifying Outlines

1. Select Outline > Verify or click the Verify, , button.

   If the outline has no errors, the Verify dialog box is displayed:

   Figure 8-4: Verify Dialog Box

   

2. Click OK.

   If the outline is not valid, a dialog box is displayed listing the errors that the outline contains.

3. Fix these errors and verify the outline again.

Saving Outlines

You can save outlines to the client or the server. By default, Hyperion Essbase saves outlines in the server directory.

1. Select File > Save. Hyperion Essbase saves the outline.
2. If you are saving changes to an existing outline, Hyperion Essbase may restructure the outline. For example, if you change a member name from Market to Region, Hyperion Essbase moves data stored in reference to Market to Region.

   If Hyperion Essbase needs to restructure the database, the Restructure Database dialog box is displayed:

   Figure 8-5: Restructure Database Dialog Box

   

3. Select the data that Hyperion Essbase should restructure.
   • Select All data when you want to keep all changes that pertain to the modified outline.
   • Select Level 0 data when you expect to recalculate the database. If all data in the outline is at level 0, choosing Level 0 data can save space and improve calculation performance.
   • Select Input data when you expect to calculate and you have loaded data into non-level 0 members.
   • Select Discard all data when you expect to reload the data or when your outlines is so radically changed that no existing data applies.

     For more information, see Database Restructuring.

4. Click OK.

Setting Dense and Sparse Data Storage

When you create and save an outline, Hyperion Essbase automatically configures the dimensions in the outline as either dense or sparse. You can view the current storage configuration by choosing Settings > Data Storage.

Figure 8-6: Data Storage Dialog Box

By default, the configuration is set to Automatic. You can clear Automatic to set the dense and sparse configuration manually.

You must set the standard dimensions with which you plan to associate attribute dimensions as sparse. Hyperion Essbase Application Manager automatically sets attribute dimensions as sparse.

For information on choosing dense or sparse storage, see Selection of Sparse and Dense Dimensions.

Renaming Dimensions and Members

You can change the names of existing dimensions or members.

1. Select the dimension or member name with a right mouse click. A member edit text box is displayed.
2. Enter the new name.
3. Press the Enter key or select another member.
4. If you have the Outline Editor set up to prompt you when you rename a dimension or member, a dialog box is displayed. Click Yes to rename the dimension or member.

1. Select the dimension or member name in the outline.
2. Click the Data Dictionary button, , press the Enter key, or select Edit > Properties to open the Member Properties dialog box
3. Enter the new name in the Name text field.
4. Click OK.

Adding Dimensions and Members to Outlines

This section describes how to add dimensions and members to outlines. It contains the following sections:

• Rules for Naming Dimensions and Members
• Adding Dimensions to Outlines
• Adding Members to Dimensions

You must position attribute dimensions at the end of the outline.

Rules for Naming Dimensions and Members

When naming dimensions and members in the database outline, follow these rules.

• The maximum length is 80 characters.
• Names are not case-sensitive unless there is a check mark next to Settings > Case Sensitive Members.
• Do not use " (quotation marks) or tabs anywhere in a name.
• Do not use the following characters at the beginning of a name:

  @	(at sign)
  \	(backslash)
  {}	(braces)
  ,	(comma)
  -	(dash, hyphen, or minus sign)
  =	(equal sign)
  <	(less than sign)
  ()	(parentheses)
  .	(period)
  +	(plus sign)
  '	(single quotation mark)
  _	(underscore)
  |	(vertical bar)

• Do not place spaces at the beginning or end of a name. Hyperion Essbase ignores spaces at the beginning or end of a name.
• Do not use the following words as dimension or member names:
  • $$$UNIVERSE$$$
  • #MISSING or #MI
  • Calc script commands, operators, and keywords. For a list of commands, see the online Technical Reference in the DOCS directory.
  • Report script commands. For a list of commands, see the online Technical Reference in the DOCS directory.
  • Function names and function arguments. For a list of functions, see the online Technical Reference in the DOCS directory.
  • Names of other dimensions, members (unless the member is shared), generation names, level names, aliases, and combinational aliases in the database

    Note:

    If you enable Dynamic Time Series members, do not use the associated generation names: History, Year, Season, Period, Quarter, Month, Week, or Day. See Applying Predefined Generation Names to Dynamic Time Series Members.

• In calc scripts, report scripts, partition definitions, security filters, and formulas, you must enclose member names in quotation marks ("") in the following situations:
  • The name starts with a numeral (for example, 100-Blue)
  • The name contains spaces anywhere in the name
  • The name contains any of the following characters:

    &	(ampersand)
    *	(asterisk)
    @	(at sign)
    \	(backslash)
    {}	(braces)
    []	(brackets)
    :	(colon)
    ,	(comma)
    -	(dash, hyphen, or minus sign)
    =	(equal sign)
    !	(exclamation point)
    >	(greater than sign)
    <	(less than sign)
    ( )	(parentheses)
    %	(percent sign)
    .	(period)
    +	(plus sign)
    ;	(semicolon)
    /	(slash)
    ~	(tilde)

• In calc scripts and formulas, you must enclose the following member names in quotation marks (""):

  AND	IF
  BEGIN	MACRO
  DOUBLE	MBR
  DYNAMIC	MEMBER
  ELSE	NOT
  ELSEIF	OR
  END	RANGE
  ENDIF	STRING
  FUNCTION	THEN

Tips to make names unique:

• Concatenate the member and alias names; for example, 100-10_Cola and 100-10_Smith.
• Add prefixes or suffixes to member names. For example, if the parent is the state and several states have a city called Jackson, appending the state abbreviation creates the unique names Jackson_CA, Jackson_IL, and Jackson_MI.

Adding Dimensions to Outlines

Dimensions are the highest level of organization in an outline. Dimensions are made up of members. For more information on dimensions, see Multidimensional Concepts

1. Select the database name, for example, Basic.
2. Select Edit > Add Child or click the Add Child button, , to open a member edit text box in the Outline Editor.

1. Select the dimension after which you want to add the new dimension.
2. Select Edit > Add Sibling or click the Add Sibling button, , to open a member edit text box in the Outline Editor.

   Figure 8-7: Member Edit Text Box

   

1. Enter the name of the dimension; for example, Year. See Rules for Naming Dimensions and Members for restrictions.
2. Press Enter to display a dialog box asking you to verify that you want to add the dimension.

   Figure 8-8: Confirm Add Dialog Box for Dimensions and Members

   

3. Click OK.
4. Enter another dimension name or press Enter to dismiss the member edit text box.

To rearrange dimensions in the outline, see Positioning Dimensions and Members.

Adding Members to Dimensions

Members are organized into dimensions. You can nest members inside of other members. For more information on members, see Multidimensional Concepts

1. Select the dimension; for example, Year.
2. Select Edit > Add Child or click the Add Child button, , to open a member edit text box in the Outline Editor.

1. Select the member after which you want to add the new member; for example, Qtr1.
2. Select Edit > Add Sibling or click the Add Sibling button, , to open a member edit text box in the Outline Editor.

   Figure 8-9: Member Edit Text Box

   

1. Enter the name of the member; for example, Qtr2. See Rules for Naming Dimensions and Members for restrictions.
2. Press Enter to display a dialog box opens asking you to verify that you want to add the member.

   Figure 8-10: Confirm Add Dialog Box for Dimensions and Members

   

3. Click OK.
4. Enter another member name or press Enter to dismiss the member edit text box.

To rearrange members in the outline, see Positioning Dimensions and Members.

Positioning Dimensions and Members

You can rearrange dimensions within an outline or members within a dimension. You can select to position dimension and members by:

• Sorting Dimensions and Members
• Moving Members

If you do not position attribute dimensions at the end of the outline, during outline verification, Hyperion Essbase Application Manager prompts you to move them there.

The positions of dimensions in an outline can affect performance. See Outline Performance Considerations.

Sorting Dimensions and Members

You can arrange dimensions within an outline or members within a dimension in alphabetical order (A to Z) or reverse alphabetical order (Z to A).

When you sort level 0 members of numeric attribute dimensions in outlines, the members are sorted by their values. For example, Figure 8-11 shows text and numeric versions of the Sizes attribute dimension after sorting the members in ascending order. The members of the numeric attribute dimension are sequenced by the numeric values of the members; the member 8 is before the other members. In the text attribute dimension, because the characters are sorted left to right, the member 8 is after the member 24.

Figure 8-11: Sorting Numeric Versus Text Attribute Dimension in Ascending Order

You cannot sort Boolean attribute dimensions. For more information about attribute dimension types, see Attribute Types.

CAUTION:

Moving dimensions and members can affect the results of your batch calculations. See Designing an Outline to Optimize Performance. Also, sorting members could move a shared member before the actual member in the outline, something that we do not recommend.

To sort all dimensions in an outline:

1. Select the database name in the outline; for example, Basic.
2. Select Edit > Sort Ascending to sort the dimensions in alphabetical order or select Edit > Sort Descending to sort the dimensions in reverse alphabetical order.

1. Select the dimension or member containing the members to sort; for example, Market.
2. Select Edit > Sort Ascending to sort the members in alphabetical order or select Edit > Sort Descending to sort the members in reverse alphabetical order.

Moving Members

CAUTION:

Moving dimensions and members can affect the results of your calculations and retrievals. See Designing an Outline to Optimize Performance. Also, sorting members could move a shared member before the actual member in the outline, something that we do not recommend.

1. Select the member or members you want to move by pressing the left mouse button on a member name. (To select multiple members, hold down the Ctrl key while selecting member names.) Continue to hold down the mouse button throughout the move operation.
2. Drag the selection to the desired location in the tree. As you drag the selection, a tree icon is displayed.
3. Place the icon over a member at the new location. A border is displayed around the member name.
   • To insert the member as a sibling of the member, place the icon on the , or to the left of the border. The icon is displayed with a straight line .
   • To insert the member as a child of the member, place the icon in or to the right of the border. The icon is displayed with a bent line .
4. Release the mouse button.

Naming Generations and Levels

You can create your own names for generations and levels in an outline. The name is a word or phrase that describes the generation or level. For example, you might create a generation name called Cities for all cities in the outline.

Use generation and level names in calc scripts or report scripts wherever you need to specify either a list of member names or generation or level numbers. For example, you could limit a calculation in a calc script to all members in a specific generation. See Developing Calc Scripts for information about developing calc scripts.

See Member Relationships, Generations, and Levels for information about generations and levels.

1. Select Outline > Gen/Level Names to open the Generation and Level Names dialog box.

   Figure 8-12: Generation and Level Names Dialog Box

   

2. Select the appropriate dimension name from the Dimension list box; for example, Year. By default, Hyperion Essbase displays the current dimension.
3. To name a generation, select Generation. To name a level, select Level. For example, to name the months in the Sample Basic database, select Generation.
4. Enter the generation or level number in the Number text box. For example, to name the months in the Sample Basic database, enter 3.
5. Enter the generation or level name in the Name text box; for example, to name the months in the Sample Basic database, enter Months.

   Note:

   You can define only one name for each generation or level. When you name the generations and levels, follow the same naming rules as for members. See Rules for Naming Dimensions and Members.

6. Click Add to display the new name in the list box.
7. Click OK.

To view the list of generation or level names, select Outline > Gen/Level Names again or print the outline.

Customizing the Outline Editor

The Outline Editor displays information about the outline and the dimensions and members that it contains. You can customize what Hyperion Essbase displays in the Outline Editor and which font it uses. This section includes the following topics:

• Making Members Case-Sensitive
• Customizing the View
• Setting the Outline Font
• Confirming Changes to Dimensions and Members

Making Members Case-Sensitive

You can specify whether members in the outline should be case-sensitive. For example, Budget and budget are both unique names if the member names are case-sensitive. By default, member names are not case-sensitive.

To set member names to be case-sensitive, select Case Sensitive Members in the Settings menu.

Customizing the View

You can customize the view of the Outline Editor to view or hide the following details by clearing them in the View menu:

• Consolidation Objects--displays consolidation operators (such as +, -, and %) in parentheses to the right of the member. For information on setting consolidation operators, see Setting Member Consolidation Properties.
• Formula Objects--displays formulas to the right of the member. For information on creating formulas, see Naming Generations and Levels.
• Dimension Tags--displays dimension tags to the right of the member, if a dimension tag other than None is selected. For information on adding dimension tags, see Setting the Dimension Type.
• Aliases--displays aliases in parentheses to the right of the member. For more information on creating aliases, see Creating and Managing Aliases
• Member Properties--displays member properties to the right of the member. For more information on assigning properties, see Setting Dimension and Member Properties
• Comments--displays comments to the right of the member. For more information on adding comments to members, see Setting Comments on Dimensions and Members.
• Attribute Associations--displays associated attribute dimension names to the right of the base dimension and associated attribute dimension members to the right of base dimension members. For information on associating attribute dimensions and members to base dimensions, see Working with Attributes
• Attributes Type--displays the attribute dimension type to the right of attribute dimension names. For information on attribute dimension types, see Attribute Types.
• Toolbar--displays the Outline Editor toolbar, which contains shortcuts for working with outlines, dimensions, and members.
• Properties Bar--displays the Outline Editor properties bar, which contains shortcuts for changing or assigning properties to dimensions and members.

Setting the Outline Font

You can specify the font that Hyperion Essbase uses to display text in the Outline Editor. To set a font:

1. Select Options > Font to open the Font dialog box.

   Figure 8-13: Font Dialog Box

   

2. Select the desired font from the Font list. You can also select the style and size of font. The default display font is Arial, Regular, 10.
3. Click OK.

Confirming Changes to Dimensions and Members

You can specify whether Hyperion Essbase prompts you when you make changes to dimensions or members. By default, Hyperion Essbase opens a dialog box asking you if you're sure you want to change the member or dimension. You must click Yes before Hyperion Essbase makes the change.

1. Select Options > Confirmation to open the Confirmation dialog box.

   Figure 8-14: Confirmation Dialog Box

   

2. Clear the options for which Hyperion Essbase should not prompt.

   By default, all options are selected, which means that Hyperion Essbase opens a dialog box to prompt you before it makes the changes. When an option is not selected, Hyperion Essbase does not prompt you before making that kind of change.

3. Click OK.

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