After you create and organize your Essbase OLAP Server 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 each dimension and member property and how to set them in the Outline Editor in Application Manager.
Note: For information on setting Dynamic Time Series members, see Calculating Time Series Data.
When you tag a dimension as a specific type, it 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. The two primary dimension types are time and accounts. This means that Essbase calculates dimensions tagged as time and accounts before other dimensions in the database. By default, all dimensions are tagged as None.
Note: The time and accounts properties are inherited by all members that are in these dimensions. The Country and Currency properties are not inherited by their members.
The following sections describe how to tag dimensions:
You can optionally use dimensions tagged as time to describe how often you collect and update data. The time dimension enables several accounts dimension functions, such as first and last time balances. 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).
Follow these rules when tagging a dimension as time:
To use a button to tag a dimension as time:
To tag a dimension as time using the Dimension Properties dialog box:
You can optionally tag a dimension as accounts if it contains items that you want to measure, such as profit or inventory.
Follow these rules when tagging an accounts dimension:
The following sections describe built-in functionality for accounts dimensions:
To perform the tasks in the following sections, the Measures dimension in the Sample Basic database must be tagged as accounts.
To use a button to tag a dimension as accounts:
To tag a dimension as accounts using the Dimension Properties dialog box:
When you set a time balance property on a member in an accounts dimension, 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, 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.
These sections explain examples of setting Time Balance:
Example of Setting the Time Balance as None
This 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-a parent's value is based on the formulas and consolidation properties of its children.
Example of Setting the 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, let's 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 you had at the beginning of Jan. When you ask for the OpeningInventory for Qtr1, you want it to be the same as the OpeningInventory for Jan. That is, 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 do this, tag OpeningInventory as first. Now Essbase calculates the value of OpeningInventory for Qtr1 as the same as the OpeningInventory for Jan. Figure 73 shows this sample consolidation.
Figure 73: 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 Setting the 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, let's 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 you had at the end of Mar. When you ask for the EndingInventory for Qtr1, you want it to be the same as the EndingInventory for Mar. That is, 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 do this, tag EndingInventory as last. Now Essbase calculates the value of EndingInventory for Qtr1 as the same as the EndingInventory for Mar. Figure 74 shows this sample consolidation.
Figure 74: 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 Setting the 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, let's 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 you had during Jan, Feb, and Mar.
To do this, tag AverageInventory as average. Now Essbase calculates the value of AverageInventory for Qtr1 as the average of the values for Jan, Feb, and Mar. Figure 75 shows this sample consolidation.
Figure 75: 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
If you set the time balance as first, last, or average, you must set the skip property to tell Essbase what to do when it encounters missing values or values of 0.
The following table describes how each setting determines what Essbase does when it encounters a missing or zero value.
Setting |
Action Essbase Takes |
---|---|
Skips data that equals zero when calculating the parent value. |
|
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 76, for example, EndingInventory is based on the value for Feb, because Mar does not have a value.
Figure 76: 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
To use buttons to set a time balance property:
To tag a dimension as accounts using the Dimension Properties dialog box:
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 the 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 use a button to set an expense property:
To tag an accounts member as expense or non-expense using the Member Properties dialog box:
Currency conversion properties define categories of currency exchange rates. These properties are used only in currency databases. For more information on currency properties, see Designing and Building Currency Conversion Applications.
To set currency conversion properties in the Outline Editor:
Use country dimensions to track business activities in multiple countries. If you track business activity in the United States and Canada, for example, your 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 Essbase, 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 more information, see Designing and Building Currency Conversion Applications.
To tag a dimension as country:
Figure 77: Country Dimension Tagged in Dimension Properties Dialog Box
Use currency partition members to separate local currency members from a base currency defined in your application. If your 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 more information, see Modify the Scenario Dimension.
For more information about designing and implementing currency applications, see Designing and Building Currency Conversion Applications.
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. For information about attribute dimensions, see Working with Attributes.
Keep in mind the following information about attribute dimensions when you tag a dimension as attribute:
To use a button to tag a dimension as attribute:
Outline Editor displays "Attribute" next to the dimension name.
To tag a dimension as attribute using the Dimension Properties dialog box:
Attributes have a text, Boolean, date or numeric type property. Although assigned at the dimension level, the type applies only to the level 0 members of the dimension. Essbase sets text as the default attribute dimension type. See Attribute Types for more information.
To use buttons to set the attribute type:
To set the attribute type in the Dimension Properties dialog box:
Note: To enable the drop-down list box, select the Attribute option.
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 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, 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 produces the correct result.
Note: Even though two-pass calculation is a property that you can give to any non-attribute dimension member, it works only on members of accounts dimensions, Dynamic Calc members, and Dynamic Calc And Store members. If two-pass calculation is assigned to other members, Hyperion Essbase ignores it.
To set a member to be calculated on the second pass in the Outline Editor:
To set a member to be calculated on the second pass using the Member Properties dialog box:
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: Essbase does not use consolidation properties with members of attribute dimensions. The Attribute Calculations dimension provides consolidation totals for attribute dimensions. See Calculating Attribute Data.
Table 12 describes each operator.
When siblings have different operators, Essbase calculates the data in top-down order. The following section describes how Essbase calculates the members in Figure 78.
Parent1 Member1 (+) 10 Member2 (+) 20 Member3 (-) 25 Member4 (*) 40 Member5 (%) 50 Member6 (/) 60 Member7 (~) 70
Essbase calculates Member1 through Member4 in Figure 78 using this precedence:
Figure 79: Sample Roll Up for Members 1 through 4
(((Member1 + Member2) + (-1)Member3) * Member4) = X (((10 + 20) + (-25)) * 40) = 200
If the result of Figure 79 is X, then Member5 consolidates thus:
Figure 80: Sample Roll Up for Member 5
(X/Member5) * 100 = Y (200/50) * 100 = 400
If the result of Figure 80 is Y, then Member6 consolidates thus:
Figure 81: Sample Roll Up for Member 6
Y/Member6 = Z 400/60 = 66.67
Because it is set to No Consolidation(~), Essbase ignores Member7 in the consolidation.
Note: Consolidation properties do not apply to members of attribute dimensions.
To set the consolidation property for a member using the toolbar in the Outline Editor:
To set the consolidation property for a member using the Member Properties dialog box:
Figure 82: Setting Consolidation Properties
You can determine how and when Essbase stores the data values for a member. For example, you can tell Essbase to only calculate the value for a member when a user requests it and then discard the data value. Table 13 lists each storage property and tells you when to set it and where to go to learn how to set it.
By default, Essbase stores each data value with the associated member. For example, if 50 cases of Cola were sold in January in Massachusetts, Essbase stores 50 at the intersection of Cola, Jan, Massachusetts.
To use a button to tag a member as stored:
To tag a member as stored using the Member Properties dialog box:
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, except that Essbase does store the data value for that member after the user views it.
For more information on Dynamic Calc or Dynamic Calc And Store members, see Dynamically Calculating Data Values.
Note: Essbase automatically tags members of attribute dimensions as Dynamic Calc. You cannot change this setting.
To use buttons to tag a member as Dynamic Calc or Dynamic Calc And Store:
To tag a member as Dynamic Calc or Dynamic Calc And Store using the Member Properties dialog box:
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 on the no consolidation property, see Setting Member Consolidation Properties.
Note: You cannot associate attributes with label only members. If you tag as label only a base dimension member that has attributes associated with it, Essbase removes the attribute associations and displays a warning message.
To use a button to tag a member as label only:
To tag a member as label only using the Member Properties dialog box:
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. Essbase stores the data value only once, but it displays in multiple locations. This offers considerable space saving as well as processing efficiency.
Use these sections to understand and create shared members:
Follow these rules when creating shared members:
Essbase retrieves shared members during drill-down, depending on their location in the spreadsheet. Essbase follows three rules during this type of retrieval:
Example: 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 Sample, your outline would be similar to this:
If you retrieved just the children of East, all results would be from stored members because Essbase retrieves stored members by default.
If, however, you retrieved data with test's children above it in the spreadsheet, Essbase would retrieve the shared members:
New York Massachusetts Florida Connecticut New Hampshire test
If you moved test above its last two children, Essbase 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 Essbase would retrieve shared members only between the non-sibling and the parent (in this case, between California and test).
Example: 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, Essbase 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
This happens because test is a parent of west and a sibling of west's stored member counterpart, West.
The shared member property defines a shared data relationship explicitly. Some members are shared even if you don't explicitly set them as shared. These members are said to be implied shared members.
Essbase assumes (or implies) a shared member relationship in the following situations:
Figure 83: Implied Sharing of a Parent with One Child
Figure 84: Implied Sharing of a Parent with Multiple Children
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 Setting the Shared Member Property.
To tag a member as shared in the Outline Editor:
You can create your own user-defined attributes for members. A user-defined attribute (UDA) is a word or phrase about the member. For example, you might create a UDA called Debit. Use UDAs in:
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 Differences Between Attributes and UDAs.
Follow these rules when creating UDAs:
To create a UDA in the Outline Editor:
You can add comments to dimensions and members. The Outline Editor displays these comments to the right of the dimension or member in the following format:
/* comment */
To add comments to dimensions or members:
Figure 86: Comments in the Dimension Properties Dialog Box
The comment is listed next to the dimension or member in the Outline Editor:
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. For more information about formulas, see Developing Formulas.
To add a formula to a dimension or member:
For more information about using the Formula Editor, see Example: Creating a Simple Formula.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |