1. Database Administrator's Guide for Oracle Essbase
  2. Setting Dimension and Member Properties
  3. Setting Dimension Types

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

To set a dimension type, see Setting Information Properties.

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. The time dimension also enables several functions on account dimension members, 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 Understanding the Essbase.Cube Worksheet.

Creating an Accounts Dimension

Tag a dimension as accounts if it contains facts that you want to analyze, 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 Understanding the Essbase.Cube Worksheet.

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 Understanding Dimension Worksheets.

Example 6-1 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 6-2 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 6-3 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 6-4 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 6-1 Skip Properties

Setting Essbase 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

To set skip properties, see Understanding Dimension Worksheets.

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 Information Properties.

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 Understanding the Essbase.Cube Worksheet.