Setting Dimension and Member Properties Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Setting Dimension and Member Properties


After you create and organize the outline, as described in Creating and Changing Database Outlines, you are ready to specify how the dimensions and members in the outline behave. This chapter describes dimension and member properties and how to set properties.

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 contains the following topics:

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. Analytic Services 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 different dimension types:

To set a dimension type, see "Setting the Dimension Type" in the 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.

Follow these rules when tagging a dimension as time:

To tag a dimension as time, see "Tagging a Time Dimension" in the 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.

Follow these rules when tagging a dimension as accounts:

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

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

Setting Time Balance Properties

If a member of the accounts dimension uses the time balance property, it affects how Analytic Services 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 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, Analytic Services 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 you have a member named OpeningInventory that represents the inventory at the beginning of the time period. If the time period was Qtr1, then 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.

To accomplish this task, tag OpeningInventory as first. Figure 48 shows this sample consolidation.

Figure 48: Consolidation of OpeningInventory Tagged as First

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 you have a member named EndingInventory that represents the inventory at the end of the time period. If the time period was Qtr1, then 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.

To accomplish this task, tag EndingInventory as last. Figure 49 shows this sample consolidation.

Figure 49: Consolidation of EndingInventory Tagged as Last

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 you have a member named AverageInventory that 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.

To accomplish this task, tag AverageInventory as average. Figure 50 shows this sample consolidation.

Figure 50: Consolidation of AverageInventory Tagged as Average

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, you must set the skip property to tell Analytic Services what to do when it encounters missing values or values of 0.

The following table describes how each setting determines what Analytic Services does when it encounters a missing or zero value.


Setting
Action Analytic Services Takes

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 both #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, then the parent of that time period matches the last non-missing child. In Figure 51, for example, EndingInventory is based on the value for Feb, because Mar does not have a value.

Figure 51: Example of Skip Property

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 Analytic Services 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 lower than the budget. When actual expenses are greater than budget, the variance is negative. The @VAR function calculates Budget - Actual. For example, if budgeted expenses were $100, and you actually spent $110, the variance is -10.

When you are budgeting non-expense items, such as sales, the actual sales should be higher 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 actually made $110 in sales, the variance is 10.

By default, members are non-expense.

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

Setting Analytic Services 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. For a comprehensive discussion of currency conversion, see Designing and Building Currency Conversion Applications.

To set currency conversion properties, see "Assigning Currency Categories to Accounts Members" in the 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 United States 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 type of currency used in each member. For example, in the Interntl application and database shipped with Analytic Services, Canada has three markets-Vancouver, Toronto, and Montreal. They use the same currency, Canadian dollars.

This dimension type is used for currency conversion applications. For a comprehensive discussion of currency conversion, see Designing and Building Currency Conversion Applications.

To tag a dimension as country, see "Tagging a Country Dimension" in the 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 US 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. For information about how to create and use currency partitions, see Designing and Building Currency Conversion Applications.

For complete information about how to design and implement currency applications, see Designing and Building Currency Conversion Applications.

To tag a dimension as currency partition, see "Creating a Currency Partition" in the 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.

Be sure to review the rules for using attribute dimensions in Working with Attributes.

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

Setting Member Consolidation

Member consolidation properties 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: Analytic Services does not use consolidation properties with members of attribute dimensions. See Calculating Attribute Data for an explanation of how the attribute dimensions works.

Table 10 describes each operator.


Table 10: 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 then 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.



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

Calculating Members with Different Operators

When siblings have different operators, Analytic Services calculates the data in top-down order. The following section describes how Analytic Services calculates the members in Figure 52.

Figure 52: Sample Roll Up

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

Analytic Services calculates Member1 through Member4 in Figure 52 as follows:

Figure 53: Sample Roll Up for Members 1 through 4

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

If the result of Figure 53 is X, then Member5 consolidates as follows:

Figure 54: Sample Roll Up for Member 5

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

If the result of Figure 54 is Y, then Member6 consolidates as follows:

Figure 55: Sample Roll Up for Member 6

Y/Member6 = Z
400/60 = 66.67 
 

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

Determining How Members Store Data Values

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


Table 11: Choosing Storage Properties  

Storage Property
When to Use
For More Information

Store

Store the data value with the member.

Understanding Stored Members

Dynamic Calc and Store

Not calculate the data value until a user requests it, and then store the data value.

Understanding Dynamic Calculation Members

Dynamic Calc

Not calculate the data value until a user requests it, and then discard the data value.

Understanding Dynamic Calculation Members

Never share

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

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

Understanding Label Only Members

Shared member

Share 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 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 Essbase Administration Services Online Help.

Understanding Dynamic Calculation Members

When a member is Dynamic Calc, Analytic Services does not calculate the value for that member until a user requests it. After the user views it, Analytic Services does not store the value for that member. If you tag a member as Dynamic Calc and Store, Analytic Services performs the same operation as for a Dynamic Calc member, except that Analytic Services stores the data value for that member after the user views it.

For a comprehensive discussion of Dynamic Calc and Dynamic Calc and Store members, see Dynamically Calculating Data Values.

Analytic Services 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 Essbase Administration Services Online Help.

Understanding Label Only Members

Label only members have no data associated with them. Use them to group members or to ease navigation and reporting from the Spreadsheet Add-in. Typically, you should give label only members the "no consolidation" property. For more information about member consolidation, see Setting Member Consolidation.

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

To tag a member as label only, see "Setting Member Storage Properties" in the 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 only stored once. To define a member as shared, there must be an actual non-shared member of the same name. 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 are typically used to calculate the same member across multiple parents. For example, you might want to calculate a Diet Cola member in both the 100 and Diet parents.

Using shared members lets you use members repeatedly throughout a dimension. Analytic Services stores the data value only once, but it displays in multiple locations. Storing the data value only once offers considerable space saving as well as processing efficiency.

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

Use these sections to learn more about shared members:

Understanding the Rules for Shared Members

Follow these rules when creating shared members:

Understanding Shared Member Retrieval During Drill-Down

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

Example of Shared Members from a Single Dimension

If you created 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 following:

If you retrieved just the children of East, all results would be from stored members because Analytic Services retrieves stored members by default.

If, however, you retrieved data with the children of test above it in the spreadsheet, Analytic Services would retrieve the shared members:

New York
Massachusetts
Florida
Connecticut
New Hampshire
test

If you moved test above its last two children, Analytic Services would retrieve the first three children as shared members, but the last two as stored members. Similarly, if you inserted 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), then Analytic Services would retrieve shared members only between the non-sibling and the parent (in this case, between California and test).

Example of Retrieval with Crossed Generation Shared Members

You could modify the Sample Basic outline to create a shared member whose stored member counterpart was a sibling to its own parent:

If you created a spreadsheet with shared members in this order, Analytic Services would retrieve all the shared members, except it would retrieve the stored member West, not the shared member west:

west
New York
Massachusetts
Connecticut
New Hampshire
test

Analytic Services 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 said to be implied shared members.

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

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

Setting Aliases

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. Alias are stored in alias tables. Aliases can improve the readability of an outline or a report.

You can set more than one alias for a member using alias tables. For example, you could 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 some 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 
 

For a comprehensive discussion of alias tables, see Alias Tables.

The following sections describe aliases:

Analytic Services 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, Analytic Services creates an empty alias table named Default. If you don't create any other alias tables, the aliases that you create are stored in the Default alias table.

If you want to create more than one set of aliases for outline members, create a new alias table for each set. 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 further information.

For Unicode-mode applications, setting up a separate alias table for each user language enables different users to view member names in their own language. For additional information about the relevance of alias tables and Unicode, see About the Analytic Services Implementation of Unicode.

Creating Aliases

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

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 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 Essbase Administration Services Online Help.

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

Creating and Managing Alias Tables

Named alias tables enable you to display different aliases in different situations. For general information about alias tables, see Alias Tables. While working with alias tables, you can perform the following actions:

Creating a New Alias Table

An alias table contains a list of aliases to use for members in the outline.The following restrictions apply to alias tables:

To create a new alias table, see "Creating Alias Tables" in Essbase Administration Services Online Help.

When you first create an alias table, it is empty. For information about adding aliases to an alias table and assigning them to members, see Creating Aliases.

Setting an Alias Table as Active

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

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


Tool
Topic
Location

Administration Services

Setting the Active Alias Table for Outline Editor

Essbase Administration Services Online Help

MaxL

query database

alter database

Technical Reference

ESSCMD

LISTALIASES

SETALIAS

Technical Reference



Copying an Alias Table

To copy alias tables, use any of the following methods:


Tool
Topic
Location

Administration Services

Copying Alias Tables

Essbase Administration Services Online Help

MaxL

alter object

Technical Reference

ESSCMD

COPYOBJECT

Technical Reference



Renaming an Alias Table

To rename an alias table, use any of the following methods:


Tool
Topic
Location

Administration Services

Renaming Alias Tables

Essbase Administration Services Online Help

MaxL

alter object

Technical Reference

ESSCMD

RENAMEOBJECT

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 Essbase Administration Services Online Help.

Importing and Exporting Alias Tables

You can import a correctly formatted text file into Analytic Services as an alias table. Alias table import files have the extension .ALT. As shown in Figure 58, alias table import files should have the following structure:

Figure 58: Sample 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 Analytic Services outline to a text file. The alias table contains all the member names with aliases and the corresponding aliases.

To import or export alias tables, use any of the following methods:


Tool
Topic
Location

Administration Services

Importing Alias Tables

Exporting Alias Tables

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference

ESSCMD

LOADALIAS

UNLOADALIAS

Technical Reference



Setting Two-Pass Calculations

By default, Analytic Services 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, Analytic Services must first calculate the outline and then re-calculate 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.

For more information on bottom-up calculations, see Using Bottom-Up Calculation.

For example, to calculate the ratio between Sales and Margin, Analytic Services 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. Analytic Services calculates the database once and then calculates the ratio member again. This calculation produces the correct result.

Even though two-pass calculation is a property that you can give to any non-attribute member, it works only on the following members:

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

To tag a member as two-pass, see "Setting Two-Pass Calculation Properties" in the 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 Analytic Services calculates the outline data. For more a comprehensive discussion about formulas, see Developing Formulas.

To add formulas to a dimension or member, see "Creating and Editing Formulas in Outlines" in the 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. For information about generations and levels, 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 information about developing calculation scripts.

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 Understanding the Rules for Naming Dimensions and Members.

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

Creating UDAs

You can create your own user-defined attributes for members. A user-defined attribute (UDA) is a word or phrase about a member. For example, you might create a UDA called Debit. Use UDAs in the following places:

If you want 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.

Follow these rules when creating UDAs:

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

Adding Comments

You can add comments to dimensions and members. A comment can be up to 255 characters long. 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 Essbase Administration Services Online Help.



Hyperion Solutions Corporation link