Setting Dimension and Member Properties

In This Section:

Setting Dimension Types

Setting Member Consolidation

Calculating Members with Different Operators

Determining How Members Store Data Values

Setting Aliases

Setting Two-Pass Calculations

Creating Formulas

Naming Generations and Levels

Creating UDAs

Adding Comments

Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

Setting Dimension Types

When you tag a dimension as a specific type, the dimension can access built-in functionality designed for that type. For example, if you define a dimension as accounts, you can specify accounting measures for members in that dimension. Essbase calculates the two primary dimension types, time and accounts, before other dimensions in the database. By default, all dimensions are tagged as none.

The following sections describe the dimension types.

  To set a dimension type, see “Setting the Dimension Type” in the Oracle Essbase Administration Services Online Help.

Creating a Time Dimension

Tag a dimension as time if it contains members that describe how often you collect and update data. In the Sample.Basic database, for example, the Year dimension is tagged as time, as are its descendants—all Qtr members and the months (such as Jan). The time dimension also enables several accounts dimension functions, such as first and last time balances.

Rules when tagging a dimension as time:

  • You can tag only one dimension in an outline as time.

  • All members in the time dimension inherit the time property.

  • You can add time members to dimensions that are not tagged as time.

  • You can create an outline that does not have a time dimension.

  To tag a dimension as time, see “Tagging a Time Dimension” in the Oracle Essbase Administration Services Online Help.

Creating an Accounts Dimension

Tag a dimension as accounts if it contains items that you want to measure, such as profit or inventory.

Rules when tagging a dimension as accounts:

  • You can tag only one dimension in an outline as accounts.

  • All members in the accounts dimension inherit the accounts property.

  • You can specify that members of the accounts dimension are calculated on the second pass through an outline. See Setting Two-Pass Calculations.

  • You can create an outline that does not have an accounts dimension.

  To tag a dimension as accounts, see “Tagging an Accounts Dimension” in the Oracle Essbase Administration Services Online Help.

The following sections describe built-in functionality for accounts dimensions.

Setting Time Balance Properties

If an accounts dimension member uses the time balance property, it affects how Essbase calculates the parent of that member in the time dimension. By default, a parent in the time dimension is calculated based on the consolidation and formulas of its children. For example, in the Sample.Basic database, the Qtr1 member is the sum of its children (Jan, Feb, and Mar). However, setting a time balance property causes parents, for example Qtr1, to roll up differently.

  To set time balance properties, see “Setting Time Balance Properties” in the Oracle Essbase Administration Services Online Help.

Example of Time Balance as None

”None” is the default value. When you set the time balance property as none, Essbase rolls up parents in the time dimension in the usual way—the value of the parent is based on the formulas and consolidation properties of its children.

Example of Time Balance as First

Set the time balance as “first” when you want the parent value to represent the value of the first member in the branch (often at the beginning of a time period).

For example, assume that a member named OpeningInventory represents the inventory at the beginning of the time period. If the time period was Qtr1, OpeningInventory represents the inventory at the beginning of Jan; that is, the OpeningInventory for Qtr1 is the same as the OpeningInventory for Jan. For example, if you had 50 cases of Cola at the beginning of Jan, you also had 50 cases of Cola at the beginning of Qtr1.

Tag OpeningInventory as first, as shown in the following example consolidation:

OpeningInventory (TB First), Cola, East, Actual, Jan(+),  50
OpeningInventory (TB First), Cola, East, Actual, Feb(+),  60
OpeningInventory (TB First), Cola, East, Actual, Mar(+),  70
OpeningInventory (TB First), Cola, East, Actual, Qtr1(+), 50

Example of Time Balance as Last

Set the time balance as “last” when you want the parent value to represent the value of the last member in the branch (often at the end of a time period).

For example, assume that a member named EndingInventory represents the inventory at the end of the time period. If the time period is Qtr1, EndingInventory represents the inventory at the end of Mar; that is, the EndingInventory for Qtr1 is the same as the EndingInventory for Mar. For example, if you had 70 cases of Cola at the end of Mar, you also had 70 cases of Cola at the end of Qtr1.

Tag EndingInventory as last, as shown in the following example consolidation:

EndingInventory (TB Last), Cola, East, Actual, Jan(+),  50
EndingInventory (TB Last), Cola, East, Actual, Feb(+),  60
EndingInventory (TB Last), Cola, East, Actual, Mar(+),  70
EndingInventory (TB Last), Cola, East, Actual, Qtr1(+), 70

Example of Time Balance as Average

Set the time balance as “average” when you want the parent value to represent the average value of its children.

For example, assume that a member named AverageInventory represents the average of the inventory for the time period. If the time period was Qtr1, then AverageInventory represents the average of the inventory during Jan, Feb, and Mar.

Tag AverageInventory as average, as shown in the following example consolidation:

AverageInventory (TB Average), Cola, East, Actual, Jan(+),  60
AverageInventory (TB Average), Cola, East, Actual, Feb(+),  62
AverageInventory (TB Average), Cola, East, Actual, Mar(+),  67
AverageInventory (TB Average), Cola, East, Actual, Qtr1(+), 63

Setting Skip Properties

If you set the time balance as first, last, or average, set the skip property to tell Essbase what to do when it encounters missing values or values of 0.

Table 15 describes how each setting determines what Essbase does when it encounters a missing or zero value.

Table 15. Skip Properties

SettingEssbase Action

None

Does not skip data when calculating the parent value.

Missing

Skips #MISSING data when calculating the parent value.

Zeros

Skips data that equals zero when calculating the parent value.

Missing and Zeros

Skips #MISSING data and data that equals zero when calculating the parent value.

If you mark a member as last with a skip property of missing or missing and zeros, the parent of that time period matches the last nonmissing child. In the following example, EndingInventory is based on the value for Feb, because Mar does not have a value.

Cola, East, Actual, Jan, EndingInventory (Last),  60
Cola, East, Actual, Feb, EndingInventory (Last),  70
Cola, East, Actual, Mar, EndingInventory (Last),  #MI
Cola, East, Actual, Qtr1, EndingInventory (Last), 70

Setting Variance Reporting Properties

Variance reporting properties determine how Essbase calculates the difference between actual and budget data in a member with the @VAR or @VARPER function in its member formula. Any member that represents an expense to the company requires an expense property.

When you are budgeting expenses for a time period, the actual expenses should be less than the budget. When actual expenses are greater than budget expenses, the variance is negative. The @VAR function calculates Budget – Actual. For example, if budgeted expenses are $100, and you spend $110, the variance is -10.

When you are budgeting nonexpense items, such as sales, the actual sales should be more than the budget. When actual sales are less than budget, the variance is negative. The @VAR function calculates Actual – Budget. For example, if budgeted sales were $100, and you made $110 in sales, the variance is 10.

By default, members are nonexpense.

  To set variance reporting properties, see “Setting Variance Reporting Properties” in the Oracle Essbase Administration Services Online Help.

Setting Essbase Currency Conversion Properties

Currency conversion properties define categories of currency exchange rates. These properties are used only in currency databases on members of accounts dimensions. See Designing and Building Currency Conversion Applications.

  To set currency conversion properties, see “Assigning Currency Categories to Accounts Members” in the Oracle Essbase Administration Services Online Help.

Creating a Country Dimension

Use country dimensions to track business activities in multiple countries. If you track business activity in the U.S. and Canada, for example, the country dimension should contain states, provinces, and countries. If a dimension is tagged as country, you can set the currency name property. The currency name property defines what type of currency this market region uses.

In a country dimension, you can specify the currency used in each member. For example, in the Interntl application and database shipped with Essbase, Canada has three markets—Vancouver, Toronto, and Montreal—which use Canadian dollars.

This dimension type is used for currency conversion applications. See Designing and Building Currency Conversion Applications.

  To tag a dimension as country, see “Tagging a Country Dimension” in the Oracle Essbase Administration Services Online Help.

Creating Currency Partitions

Use currency partition members to separate local currency members from a base currency defined in the application. If the base currency for analysis is U.S. dollars, for example, the local currency members would contain values based on the currency type of the region, such as Canadian dollars.

This dimension type is used for currency conversion applications. See Designing and Building Currency Conversion Applications.

  To tag a dimension as currency partition, see “Creating a Currency Partition” in the Oracle Essbase Administration Services Online Help.

Creating Attribute Dimensions

Use attribute dimensions to report and aggregate data based on characteristics of standard dimensions. In the Sample.Basic database, for example, the Product dimension is associated with the Ounces attribute dimension. Members of the Ounces attribute dimension categorize products based on their size in ounces.

Review the rules for using attribute dimensions in Working with Attributes.

  To tag a dimension as an attribute, see “Tagging an Attribute Dimension” in the Oracle Essbase Administration Services Online Help.

Setting Member Consolidation

Member consolidation properties, which are listed in Table 16, Consolidation Operators, determine how children roll up into their parents. By default, new members are given the addition (+) operator, meaning that members are added. For example, Jan, Feb, and Mar figures are added and the result stored in their parent, Qtr1.

Note:

Essbase does not use consolidation properties with members of attribute dimensions. See Calculating Attribute Data.

Table 16. Consolidation Operators

Operator

Description

+

Adds the member to the result of previous calculations performed on other members. + is the default operator.

-

Multiplies the member by –1 and adds it to the sum of previous calculations performed on other members.

*

Multiplies the member by the result of previous calculations performed on other members.

/

Divides the member into the result of previous calculations performed on other members.

%

Divides the member into the sum of previous calculations performed on other members. The result is multiplied by 100 to yield a percentage value.

~

Does not use the member in the consolidation to its parent.

^

Does not use the member in any consolidation in any dimension.

  To set member consolidation properties, see “Setting Member Consolidation Properties” in the Oracle Essbase Administration Services Online Help.

Calculating Members with Different Operators

When siblings have different operators, Essbase calculates the data in top-down order. Consider the following members:

Parent1
  Member1 (+)  10
  Member2 (+)  20
  Member3 (-)  25
  Member4 (*)  40
  Member5 (%)  50
  Member6 (/)  60
  Member7 (~)  70

Essbase calculates Member1 through Member4 as follows:

(((Member1 + Member2) + (-1)Member3) * Member4) = X
(((10 + 20) + (-25)) * 40) = 200

If the result of this calculation is X, Member5 consolidates as follows:

(X/Member5) * 100 = Y
(200/50) * 100 = 400

If the result of the Member1 through Member4 calculation is Y, Member6 consolidates as follows:

Y/Member6 = Z
400/60 = 66.67

Because Member7 is set to No Consolidation(~), Essbase ignores Member7 in the consolidation.

Determining How Members Store Data Values

You can determine how and when Essbase stores the data values for a member. For example, you can tell Essbase to calculate the value for a member only when a user requests it, and then discard the data value. Table 17 describes each storage property.

Table 17. Choosing Storage Properties

Storage Property

Behavior

See

Store

Stores the data value with the member.

Understanding Stored Members

Dynamic Calc and Store

Does not calculate the data value until a user requests it, and then stores the data value.

Understanding Dynamic Calculation Members

Dynamic Calc

Does not calculate the data value until a user requests it, and then discards the data value.

Understanding Dynamic Calculation Members

Never share

Does not allow members to be shared implicitly.

Members tagged as Never share can only be explicitly shared. To explicitly share a member, create the shared member with the same name and tag it as shared.

Understanding Implied Sharing

Label only

Creates members for navigation only; that is, members that contain no data values.

Understanding Label Only Members

Shared member

Shares values between members. For example, in the Sample.Basic database, the 100-20 member is stored under the 100 parent and shared under Diet parent.

Understanding Shared Members

  To set member storage properties, see “Setting Member Storage Properties” in the Oracle Essbase Administration Services Online Help.

Understanding Stored Members

Stored members contain calculated values that are stored with the member in the database after calculation. By default, members are set as stored.

  To define a member as stored, see “Setting Member Storage Properties” in the Oracle Essbase Administration Services Online Help.

Understanding Dynamic Calculation Members

When a member is Dynamic Calc, Essbase does not calculate the value for that member until a user requests it. After the user views it, Essbase does not store the value for that member. If you tag a member as Dynamic Calc and Store, Essbase performs the same operation as for a Dynamic Calc member but then stores the data value. See Dynamically Calculating Data Values.

Essbase automatically tags members of attribute dimensions as Dynamic Calc. You cannot change this setting.

  To tag a member as Dynamic Calc, see “Setting Member Storage Properties” in the Oracle Essbase Administration Services Online Help.

Understanding Label Only Members

Label only members have no associated data. Use them to group members or to ease navigation and reporting from Smart View. Typically, you should give label only members the “no consolidation” property. See Setting Member Consolidation.

You cannot associate attributes with label only members. If you tag a base dimension member that has attribute associations as label only, Essbase removes the attribute associations and displays a warning message.

A descendent of a label only member cannot be tagged as Dynamic Calc. In the following example, when verifying the outline, Essbase issues an error message indicating that ChildB cannot be tagged as label only:

ParentA = Label Only
   ChildB = Label Only
      DescendantC = Dynamic Calc

Tagging DescendantC as Store Data or Dynamic Calc and Store resolves the issue.

  To tag a member as label only, see “Setting Member Storage Properties” in the Oracle Essbase Administration Services Online Help.

Understanding Shared Members

The data values associated with a shared member come from another member with the same name. The shared member stores a pointer to data contained in the other member, and the data is stored only once. To define a member as shared, an actual nonshared member of the same name must exist. For example, in the Sample.Basic database, the 100-20 member under 100 stores the data for that member. The 100-20 member under Diet points to that value.

Shared members typically are used to calculate the same member across multiple parents; for example, to calculate a Diet Cola member in both the 100 and Diet parents.

Using shared members lets you use members repeatedly throughout a dimension. Essbase stores the data value only once, but it displays in multiple locations. Storing the data value only once saves space and improves processing efficiency.

  To tag a member as shared, see “Setting Member Storage Properties” in the Oracle Essbase Administration Services Online Help.

Read the following sections to learn more about shared members.

Note:

Members with the same name may be duplicate members instead of shared members. See Creating and Working With Duplicate Member Outlines.

Understanding the Rules for Shared Members

Rules when creating shared members:

  • Shared members must be in the same dimension. For example, both 100-20 members in the Sample.Basic database are in the Product dimension.

  • Shared members cannot have children.

  • An unlimited number of shared members can have the same name.

  • UDAs or formulas cannot be assigned to shared members.

  • You can create a shared member for a member with a duplicate member name. Specify the duplicate member name for which you want to create the shared member. The qualified name of the duplicate member, on which the shared member is based, is displayed in the Member Properties dialog box. See “Defining Shared Members” in the Oracle Essbase Administration Services Online Help.

  • Attributes cannot be associated with shared members.

  • If accounts properties are assigned to shared members, the values for those accounts properties are taken from the base member, even if the accounts properties on the shared member are changed.

  • Aliases can be assigned to shared members.

  • An actual member must be located in a dimension before its shared member.

  • Avoid complex relationships between actual and shared members that will be part of an attribute calculation, or a calculation may return unexpected results. See Understanding Attribute Calculation and Shared Members.

Note:

You cannot create a shared member and the member on which it is based under the same parent. In a duplicate member outline, siblings must be unique.

Note:

In grid clients (for example, Smart View), shared members can easily be differentiated from their base members, because you can specify for them to be displayed with a qualified name (for example, [Parent].[Child]). Shared members can be displayed with qualified names even if you have not set the outline to enable duplicate member names. Additionally, you can use qualified member names to search for shared members in the grid or using member selection.

Understanding Shared Member Retrieval During Drill-Down

Essbase retrieves shared members during drill-down, depending on their location in the spreadsheet. Essbase follows three rules during this type of retrieval:

  • Essbase retrieves stored members (not their shared member counterparts) by default.

  • Essbase retrieves from the bottom of a spreadsheet first.

  • If the parent of a shared member is a sibling of the stored member counterpart of one of the shared members, Essbase retrieves the stored member.

Example of Shared Members from a Single Dimension

If you create a test dimension with all shared members based on the members of the dimension East from the Sample.Basic outline, the outline would be similar to the one shown in Figure 39, Shared Members from a Single Dimension:

Figure 39. Shared Members from a Single Dimension

This image shows an outline in which the test member consists of shared members from the East member. The test and East members contain the same siblings: New York, Massachusetts, Florida, Connecticut, and New Hampshire.

If you retrieve only the children of East, all results are from stored members because Essbase retrieves stored members by default.

If, however, you retrieve data with the children of test above it in the spreadsheet, Essbase retrieves the shared members:

New York
Massachusetts
Florida
Connecticut
New Hampshire
test

If you move test above its last two children, Essbase retrieves the first three children as shared members, but the last two as stored members. Similarly, if you insert a member in the middle of the list above which was not a sibling of the shared members (for example, California inserted between Florida and Connecticut), Essbase retrieves shared members only between the nonsibling and the parent (in this case, between California and test).

Example of Retrieval with Crossed Generation Shared Members

You can modify the Sample.Basic outline to create a shared member whose stored member counterpart is a sibling to its own parent, as shown in Figure 40, Retrieval with Crossed Generation Shared Members:

Figure 40. Retrieval with Crossed Generation Shared Members

This image shows an outline in which the test member consists of siblings that are shared members from the East member (New York, Massachusetts, Florida, Connecticut, and New Hampshire) and a shared member, west, whose stored member counterpart (West) is a sibling to its own parent (test).

If you create a spreadsheet with shared members in this order, Essbase retrieves all the shared members, except it retrieves the stored member West, not the shared member west:

West
New York
Massachusetts
Connecticut
New Hampshire
test

Essbase retrieves the members in this order because test is a parent of west and a sibling of west’s stored member counterpart, West.

Understanding Implied Sharing

The shared member property defines a shared data relationship explicitly. Some members are shared even if you do not explicitly set them as shared. These members are implied shared members.

Essbase assumes (or implies) a shared member relationship in the following situations:

  • A parent has only one child. In this situation, the parent and the child contain the same data. Essbase ignores the consolidation property on the child and stores the data only once—thus the parent has an implied shared relationship with the child. In the following example, the parent 500 has only one child, 500-10, so the parent shares the value of that child:

    500 (+)
      500-10 (+)
  • A parent has only one child that consolidates to the parent. If the parent has four children, but three are marked as no consolidation, the parent and child that consolidates contain the same data. Essbase ignores the consolidation property on the child and stores the data only once—thus the parent has an implied shared relationship with the child. In the following example, the parent 500 has only one child, 500‑10, that rolls up to it. The other children are marked as No Consolidate(~), so the parent implicitly shares the value of 500‑10.

    500 (+)
      500-10 (+)
      500-20 (~)
      500-30 (~)

If you do not want a member to be shared implicitly, mark the parent as Never Share so that the data is duplicated instead. See Understanding Shared Members for an explanation of how shared members work.

Setting Aliases

The information about aliases and alias tables applies to block storage and aggregate storage databases.

An alias is an alternate name for a member or shared member. For example, members in the Product dimension in the Sample.Basic database are identified both by product codes, such as 100, and by more descriptive aliases, such as Cola. Aliases, stored in alias tables, can improve the readability of outlines or reports.

You can set multiple aliases for a member using alias tables. For example, you can use different aliases for different kinds of reports—users may be familiar with 100-10 as Cola, but advertisers and executives may be familiar with it as The Best Cola. This list shows products in the Sample.Basic database that have two descriptive alias names:

Product  Default              Long Names
100-10   Cola                 The Best Cola
100-20   Diet Cola            Diet Cola with Honey
100-30   Caffeine Free Cola   All the Cola, none of the Caffeine

Essbase does not support aliases for Hybrid Analysis-enabled members.

Alias Tables

Aliases are stored in one or more tables as part of a database outline. An alias table maps a specific, named set of alias names to member names. When you create a database outline, Essbase creates an empty alias table named Default. If you do not create any other alias tables, the aliases that you create are stored in the Default alias table.

You can create an alias table for each set of outline members. When you view the outline or retrieve data, you can use the alias table name to indicate which set of alias names you want to see. Identifying which alias table contains the names that you want to see while viewing an outline is called making an alias table the active alias table. See Setting an Alias Table as Active.

For Unicode-mode applications, setting up a separate alias table for each user language enables users to view member names in their own language. See Understanding the Essbase Unicode Implementation.

Creating Aliases

You can provide an alias for any member. Alias names must follow the same rules as member names. See Naming Restrictions for Dimensions, Members, and Aliases.

You can use any of the following methods to create aliases in an existing alias table:

  To manually assign an alias to a member while editing an outline, see “Creating Aliases for Dimensions and Members” in the Oracle Essbase Administration Services Online Help.

  To use dimension build and a data source to add aliases to an alias table, see “Defining a Rules File for Adding Aliases” in the Oracle Essbase Administration Services Online Help.

  To import alias values from an alias table source file created in a predefined format, see Importing and Exporting Alias Tables.

Creating and Managing Alias Tables

Named alias tables enable you to display different aliases in different situations. See:

Creating an Alias Table

An alias table contains a list of aliases to use for outline members. These requirements apply:

  • You can create up to 32 alias tables for a block storage or aggregate storage outline.

  • The naming conventions for alias table names are the same as those for dimensions.

    See Naming Restrictions for Dimensions, Members, and Aliases.

  • Name-length restrictions depend on the Unicode-related mode of the application.

    See Limits.

  • The name of the system-generated default alias table, which is Default, cannot be changed; however, the name of alias tables that you create can be changed.

  • Optional. You can specify multiple language codes for an alias table. When you create an alias table, a language code is not specified. See Working with Alias Table Language Codes.

  To create an alias table, see “Creating Alias Tables” in the Oracle Essbase Administration Services Online Help.

A new alias table is empty. To add aliases to an alias table and assign them to members, see Creating Aliases.

To assign language codes to an alias table, see Working with Alias Table Language Codes.

Working with Alias Table Language Codes

You can specify multiple language codes for an alias table. When you create an alias table, a language code is not specified. To assign language codes to an alias table, use the C-API or VB-API versions of the SetAliasTableLanguage API. (You cannot set a language code for the default alias table.)

To get the set of language codes associated with an alias table, use the C-API or VB-API versions of the GetAliasTableLanguages API.

To clear the language codes associated with an alias table, use the C-API or VB-API versions of the ClearAliasTableLanguages API.

When clearing aliases from an alias table, language codes are removed from the alias table.

When copying an alias table, language codes are removed from the copied alias table.

When renaming an alias table, language codes are preserved in the renamed alias table.

See the Oracle Essbase API Reference.

Setting an Alias Table as Active

The active alias table contains the aliases that Essbase currently displays in the outline.

  To view a list of alias tables in the outline and to set the current alias table, use a tool:

Tool

Topic

Location

Administration Services

Setting the Active Alias Table for Outline Editor

Oracle Essbase Administration Services Online Help

MaxL

query database

alter database

Oracle Essbase Technical Reference

ESSCMD

LISTALIASES

SETALIAS

Oracle Essbase Technical Reference

Copying an Alias Table

To copy an alias table, the table must be persisted in the database directory. To copy artifacts that are not persisted in the database directory, use the EXPORT ESSCMD command.

When you copy an alias table, language codes associated with that alias table are removed from the copied alias table. See Working with Alias Table Language Codes.

  To copy alias tables, use a tool:

Tool

Topic

Location

Administration Services

Copying Alias Tables

Oracle Essbase Administration Services Online Help

MaxL

alter object

Oracle Essbase Technical Reference

ESSCMD

COPYOBJECT

Oracle Essbase Technical Reference

Renaming an Alias Table

When you rename an alias table, language codes associated with that table are preserved in the renamed alias table. See Working with Alias Table Language Codes.

  To rename an alias table, use a tool:

Tool

Topic

Location

Administration Services

Renaming Alias Tables

Oracle Essbase Administration Services Online Help

MaxL

alter object

Oracle Essbase Technical Reference

ESSCMD

RENAMEOBJECT

Oracle Essbase Technical Reference

Clearing and Deleting Alias Tables

You can delete an alias table from the outline, or you can clear all the aliases from an alias table without deleting the alias table itself. To clear or delete alias tables, see “Deleting and Clearing Alias Tables” in the Oracle Essbase Administration Services Online Help.

When you clear the aliases from an alias table, language codes associated with that alias table are removed. See Working with Alias Table Language Codes.

Importing and Exporting Alias Tables

You can import a correctly formatted text file into Essbase as an alias table. Alias table import files have the .alt extension. Alias table import files should have the following structure:

  • The first line in the file starts with $ALT_NAME. Add one or two spaces followed by the name of the alias table. If the alias table name contains a blank character, enclose the name in single quotation marks.

  • The last line of the file must be $END.

  • Each line between the first and the last lines contains two values separated by one or more spaces or tabs. The first value must be the name of an existing outline member; the second value is the alias for the member.

  • Any member or alias name that contains a blank or underscore must be enclosed in double quotation marks.

The following is an example of an alias table import file:

$ALT_NAME  'Quarters'
Qtr1  Quarter1
Jan   January
Feb   February
Mar   March
$END

You can also export an alias table from the Essbase outline to a text file. The export file contains aliases and the corresponding member names—qualified member names for duplicate members.

  To import or export alias tables, use a tool:

Tool

Topic

Location

Administration Services

Importing Alias Tables

Exporting Alias Tables

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

LOADALIAS

UNLOADALIAS

Oracle Essbase Technical Reference

Setting Two-Pass Calculations

By default, Essbase calculates outlines from the bottom up—first calculating the values for the children and then the values for the parent. Sometimes, however, the values of the children may be based on the values of the parent or the values of other members in the outline. To obtain the correct values for these members, Essbase must first calculate the outline and then recalculate the members that are dependent on the calculated values of other members. The members that are calculated on the second pass through the outline are called two-pass calculations.

See Using Bottom-Up Calculation.

For example, to calculate the ratio between Sales and Margin, Essbase needs first to calculate Margin, which is a parent member based on its children, including Sales. To ensure that the ratio is calculated based on a freshly calculated Margin figure, tag the Margin % ratio member as a two-pass calculation. Essbase calculates the database once and then calculates the ratio member again. This calculation produces the correct result.

Although two-pass calculation is a property that you can give to any nonattribute member, it works only on the following members:

  • Accounts dimension members

  • Dynamic Calc members

  • Dynamic Calc and Store members.

If two-pass calculation is assigned to other members, Essbase ignores it.

  To tag a member as two-pass, see “Setting Two-Pass Calculation Properties” in the Oracle Essbase Administration Services Online Help.

Creating Formulas

You can apply formulas to standard dimensions and members. You cannot set formulas for attribute dimensions and their members. The formula determines how Essbase calculates the outline data. See Developing Formulas for Block Storage Databases.

  To add formulas to a dimension or member, see “Creating and Editing Formulas in Outlines” in the Oracle Essbase Administration Services Online Help.

Naming Generations and Levels

You can create names for generations and levels in an outline, such as 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. See Dimension and Member Relationships.

Use generation and level names in calculation 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 calculation script to all members in a specific generation. See Developing Calculation Scripts for Block Storage Databases.

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

  To name generations and levels using Outline Editor, see “Naming Generations and Levels” in the Oracle Essbase Administration Services Online Help.

Creating UDAs

You can create user-defined attributes (UDA) for members. For example, you might create a UDA called Debit. Use UDAs in the following places:

  • Calculation scripts. After you define a UDA, you can query a member for its UDA in a calculation script. For example, you can multiply all members with the UDA Debit by –1 so that they display as either positive or negative (depending on how the data is currently stored). See Developing Calculation Scripts for Block Storage Databases.

  • Data loading. You can change the sign of the data as it is loaded into the database based on its UDA. See Flipping Field Signs.

To perform a calculation, selectively retrieve data based on attribute values, or provide full crosstab, pivot, and drill-down support in the spreadsheet, create attribute dimensions instead of UDAs. See Comparing Attributes and UDAs.

Note:

On aggregate storage databases, using UDAs to define member groups greatly decreases the execution speeds of Essbase functions. To avoid this performance loss, use attribute dimensions to define member groups.

Rules when creating UDAs:

  • You can define multiple UDAs per member.

  • You cannot set the same UDA twice for one member.

  • You can set the same UDA for different members.

  • A UDA name can be the same as a member, alias, level, or generation name. Follow the same naming rules as for members. See Naming Restrictions for Dimensions, Members, and Aliases.

  • You cannot create a UDA on shared members.

  • You cannot create a UDA on members of attribute dimensions.

  • A UDA applies to the specified member only. Descendants and ancestors of the member do not automatically receive the same UDA.

  To add UDAs to a member, see “Working with UDAs” in the Oracle Essbase Administration Services Online Help.

Adding Comments

You can add comments to dimensions and members. Comments can contain 255 characters maximum. Outline Editor displays comments to the right of the dimension or member in the following format:

/* comment */

  To add comments to a dimension or member, see “Setting Comments on Dimensions and Members” in the Oracle Essbase Administration Services Online Help.