Understanding Advanced Dimension Building Concepts

Understanding Advanced Dimension Building Concepts

In This Section:

Understanding Build Methods

Using Generation References

Using Level References

Using Parent-Child References

Adding a List of New Members

Building Attribute Dimensions and Associating Attributes

Building Shared Members by Using a Rules File

Building Duplicate Member Outlines

Understanding Build Methods

The build method that you select depends on the type of data in the data source and determines the algorithm that Essbase uses to add, change, or remove dimensions, members, and aliases in the outline.

Use the guidelines in Table 42 to select the appropriate build method for the data source:

Table 42. Build Method Guidelines

Type of Data in Each Record	Examples	Desired Operation	Build Method[a]	Field Type Information
Top-down data Each record specifies the parent’s name, the child’s name, the children of that child, and so on.	Year, Quarter, Month	Modify the properties of existing dimensions and members	Generation references	The generation number for each field.
Bottom-up data Each record specifies the name of the member, the name of its parent, the name of its parent’s parent, and so forth.	Month, Quarter, Year	Create shared members that roll up into different generations Modify the properties of existing dimensions and members	Level references	The level number for each field.
Parent followed by its child Each record specifies the name of the parent and the name of the new child member, in that order, although they can specify other information as well.	Cola, Diet Cola	Create shared members that roll up into different generations Share non-level 0 members Modify properties of existing dimensions and members	Parent-child references	Whether a field is parent or child. The field number is 0.
A list of new members Each data source lists new members; the data source does not specify where in the outline the members belong. Essbase provides algorithms that determine where to add these members.	Jan, Feb, Mar, April	Add all members as children of an existing parent (possibly a “dummy” parent)	Add as child of the specified parent
	800-10, 800-20	Add all members at the end of the dimension	Add as sibling at the lowest level
	800-10, 800-20	Add each new member to the dimension that contains similar members	Add as sibling to a member with a matching string
A list of base dimension members and their attributes	Cola 16oz Can, Root Beer 14oz Bottle	Add members to an attribute dimension and associate the added members with the appropriate members of the base dimension	Generation, level, or parent-child references, depending on the organization of the source data	The number for each field. The number is either the generation or level number of the associated member of the base dimension or zero.

[a] Using a level references build, you cannot create an alias that has the same name as its member. This restriction does not apply if you use other build methods, including the generation references build method.

Using Generation References

Top-down data sources are organized left to right from the highest level to the lowest level. Each record begins with the most general information and progresses to the most specific information. The name of the new member is at the end of the record. When using a top-down data source, use the generation references build method. In the rules file, specify the generation number and the field type of each field of the data source.

Essbase numbers members within a dimension according to the hierarchical position of the member within the dimension. The numbers are called generation references. A dimension is always generation 1. All members at the same branch in a dimension are called a generation. Generations are numbered top-down according to their position relative to the dimension; that is, relative to dimension 1.

For example, as illustrated in Figure 64, Generations, the Product dimension in the Sample.Basic database is generation 1. Product has a 100 member, which is generation 2. 100 has members, such as 100-10, which are generation 3. To use the generation references build method, specify the generation reference number in the rules file.

Figure 64. Generations

The top half of Figure 65, Rules File for Generation Build shows a top-down data source (GENREF.TXT). The data source is used to build the Product dimension. The bottom half shows the rules file for the data source (GENREF.RUL). The rules file specifies the generation number for each field in the data source. SeeSetting Field Type Information.

Figure 65. Rules File for Generation Build

Figure 66, Generation References shows the tree that Essbase builds from the GENREF.TXT data source and GENREF.RUL rules file:

Figure 66. Generation References

Dealing with Empty Fields

When you use the generation references build method, you can choose to process null values. Null processing specifies what actions Essbase takes when it encounters empty fields, also known as null fields, in the data source.

If null processing is not enabled, Essbase rejects all records with null values and writes an error to the error log.

If null processing is enabled, Essbase processes nulls as in the following ways:

• Missing field: If the null occurs where Essbase expects a GENERATION field, Essbase promotes the next GENERATION field to replace the missing field.
  In the following example, there is no field in the GEN3,Products column:
  

  GEN2,Products   GEN3,Products   GEN4,Products
  100                             100-10a

  When Essbase reads the record, it promotes the GEN4 field (100-10a) to GEN3, as if the data source looked like the following example:
  

  GEN2,Products   GEN3,Products   GEN4,Products
  100             100-10a

• Missing field before secondary field: If a null occurs directly before a secondary field, Essbase ignores the secondary field. (Secondary field types are alias, property, formula, duplicate generation, duplicate generation alias, currency name, currency category, attribute parent, UDA, and name of an attribute dimension.)
  In the following example, there is no field in the GEN2, Products or the ALIAS2,Products column:
  

  GEN2,Products   ALIAS2,Products   GEN3,Products   GEN4,Products
                  Cola              100-10          100-10a

  When Essbase reads the record, it ignores the ALIAS2 field and promotes the GEN3 field (100-10) to GEN2 and the GEN4 field (100-10a) to GEN3, as if the data source looked like the following example:
  

  GEN2,Products   ALIAS2,Products   GEN3,Products   GEN4,Products
  100-10          Cola              100-10a

• Missing secondary field: If the null occurs where Essbase expects a secondary field, Essbase ignores the secondary null field and continues loading.
  In the following example, there is no field in the ALIAS2, Products column:
  

  GEN2,Products   ALIAS2,Products   GEN3,Products   GEN4,Products
  100                               100-10          100-10a

  When Essbase reads the record, it ignores the ALIAS2 field and loads the other fields.

Using Level References

In a bottom-up data source, each record defines a single member of a dimension. The definition begins with the most specific information about the member and provides progressively more general information. A typical record specifies the name of the new member, then the name of its parent, then its parent’s parent, and so forth.

Levels are defined from a bottom-up hierarchical structure. For example, in the outline in Figure 67, Generation and Level Numbers, the lowest-level members are at the bottoms of the branches of the Product dimension.

Figure 67. Generation and Level Numbers

To build the outline in Figure 67, Generation and Level Numbers, you can use the following bottom-up data source:

100-10-12 100-10 100
100-20-12 100-20 100

In a level reference build, the lowest-level members are sequenced left to right. Level 0 members are in the first field, level 1 members are in the second field, and so on. This organization is the opposite of how data is presented for generation references (top-down).

In the following example, the rules file uses the level reference build method to add members to the Product dimension of the Sample.Basic database. The rules file specifies the level number and the field type for each field of the data source (see Setting Field Type Information). The first column of the data source contains new members (600-10-11, 600-20-10, and 600-20-18). The second column contains the parents of the new members (600-10 and 600-20), and the third column contains parents of the parents (600).

Figure 68. Rules File for Level Build

For example, to build the tree in Figure 69, Levels, use Figure 68, Rules File for Level Build to set up the data source (LEVEL.TXT) and the rules file (LEVEL.RUL).

Figure 69. Levels

Dealing with Empty Fields

When you use the level references build method, you can choose to process null values. Null processing specifies what actions Essbase takes when it encounters empty fields, also know as null fields, in the data source.

If null processing is not enabled, Essbase rejects all records with null values and writes an error to the error log.

If null processing is enabled, Essbase processes nulls in the following ways:

• Missing field: If a null occurs where Essbase expects a LEVEL field, Essbase promotes the next LEVEL field to replace the missing field.
  In the following example, there is no field in the LEVEL0, Products column:
  

  LEVEL0,Products   LEVEL1,Products   LEVEL2,Products
                    100-10            100

  When Essbase reads the record, it promotes the LEVEL1 field (100-10) to LEVEL0 and the LEVEL2 field (100) to LEVEL1, as if the data source looked like the following example:
  

  LEVEL0,Products   LEVEL1,Products   LEVEL2,Products
  100-10            100

• Missing field before a secondary field: If a null occurs directly before a secondary field, Essbase ignores the secondary field. (Secondary field options are alias, property, formula, duplicate level, duplicate level alias, currency name, currency category, attribute parent, UDA, and a name of an attribute dimension.)
  In the following example, there is no field in the LEVEL0, Products column:
  

  LEVEL0,Products   ALIAS0,Products   LEVEL1,Products   LEVEL2,Products
                    Cola              100-10            100

  When Essbase reads the record, it ignores the ALIAS0 field and promotes the LEVEL1 field (100-10) to LEVEL0 and the LEVEL2 field (100) to LEVEL1, as if the data source looked like the following example:
  

  LEVEL0,Products   ALIAS0,Products   LEVEL1,Products   LEVEL2,Products
  100-10             Cola              100

• Missing secondary field: If a null occurs where Essbase expects a secondary field, Essbase ignores the secondary null field and continues loading.
  In the following example, there is no field in the ALIAS0, Products column:
  

  LEVEL0,Products   ALIAS0,Products   LEVEL1,Products   LEVEL2,Products
  100-10a           100-10            100

  When Essbase reads the record, it ignores the ALIAS0 field and loads the other fields.

Using Parent-Child References

Use the parent-child references build method when every record of the data source specifies the name of a new member and the name of the parent to which you want to add the new member.

Members in a database exist in a parent-child relationship. Figure 70, Parents and Children shows part of the Product dimension with its parent and children relationships identified.

Figure 70. Parents and Children

A parent-child data source must contain at least two columns: a parent column and a child column, in that order. The data source can include columns with other information (for example, the alias, the attributes, or the properties of the new member). A record within a parent-child data source cannot specify more than one parent or more than one child and cannot reverse the order of the parent and child columns.

In a parent-child build, the rules file specifies which column is the parent and which column is the child. For example, the top half of Figure 71, Rules Files for Parent-Child Build shows a data source (PARCHIL.TXT), in which each record specifies the name of a parent and the name of its child, in that order. The bottom half of the figure shows the rules file (PARCHIL.RUL) that specifies which column is the parent and which column is the child. Additionally, this example associates aliases with the child field.

Figure 71. Rules Files for Parent-Child Build

Figure 72, Parents and Children shows the tree that Essbase builds from this data source and rules file.

Figure 72. Parents and Children

Note:

For duplicate member situations, the parent field must contain the qualified member name. See Building Qualified Member Names Through the Rules File.

Adding a List of New Members

If a data source consists of a list of new members and does not specify their ancestors, Essbase must decide where in the outline to add them. Essbase provides the following build methods for this type of data source:

• Add each new member as a sibling of the existing member whose text most closely matches its own.
  See Adding Members Based On String Matches.
• Add each new member as a sibling of the lowest-level existing member.
  See Adding Members as Siblings of the Lowest Level.
• Add all new members as children of a specified parent (generally a “dummy” parent).
  See Adding Members to a Specified Parent.

After Essbase adds all new members to the outline, it may be necessary to move the new members into their correct positions using Outline Editor. SeePositioning Dimensions and Members.

Note:

Essbase does not support concurrent attribute association with the Add as build methods.

Adding Members Based On String Matches

You can add new members from a data source to an existing dimension by matching strings with existing members. When Essbase encounters a new member in a data source, it scans the outline for a member name with similar text and adds the new member as a sibling of the member with the closest string match.

For example, the data source in Figure 73, Rules File Fields Set to Add Members as Siblings with String Matches (SIBSTR.TXT) contains two new members (100-11 and 200-22) to add to the Product dimension in the Sample.Basic database. The new members are similar to strings in the Product dimension: they contain three digits, one dash, and two digits.

To add the example members to the database, set the values in Table 43 in the rules file:

Table 43. Example of Adding Members Using String Matches

Field	Value	See
Field 1 (Product)	Do not select a field type for the field Set the dimension for the field to Product (field 1 is displayed as Product, as shown in Figure 72, Parents and Children)	Setting Field Type Information
Fields 2 through 6	Ignore the fields	Ignoring Fields
Product dimension	Select the “Add as sibling of matching string” build method	Selecting a Build Method

Figure 73. Rules File Fields Set to Add Members as Siblings with String Matches

Figure 74, Tree for Adding Members as Siblings with String Matches shows the tree that Essbase builds from this data source and rules file.

Figure 74. Tree for Adding Members as Siblings with String Matches

Adding Members as Siblings of the Lowest Level

You can add new members from a data source as siblings of members that reside at the lowest level of a dimension—at the level 0 branch. When Essbase encounters a new member in a data source, it scans the outline for the level 0 branch of members and adds the new member as a sibling of these members.

Note:

If the outline contains more than one group of members at this level, Essbase adds the new member to the first group of members that it encounters.

For example, the data source (SIBLOW.TXT) and the rules file (SIBLOW.RUL) in Figure 75, Rules File Fields Set to Add Members as Siblings of the Lowest Levelcontain new members (A100-10 and A100-99) to add to the Measures dimension of the Sample.Basic database.

Figure 75. Rules File Fields Set to Add Members as Siblings of the Lowest Level

To add the example members dynamically to the database, set the values shown in Table 44 in the rules file:

Table 44. Example of Adding Members as Siblings of the Lowest Level

Field	Value	See
Field 3 (Measures)	Do not select a field type for the field Set the dimension for the field to Measures (field 3 is displayed as Measures, as shown in Figure 75, Rules File Fields Set to Add Members as Siblings of the Lowest Level)	Setting Field Type Information
Fields 1, 2, 4, 5, and 6	Ignore the fields	Ignoring Fields
Measures dimension	Select the “Add as sibling of lowest level” build method	Selecting a Build Method

Figure 76, Tree for Adding Members as Siblings of the Lowest Level shows the tree that Essbase builds from this data source and rules file.

Figure 76. Tree for Adding Members as Siblings of the Lowest Level

Adding Members to a Specified Parent

You can add all new members as children of a specified parent, generally a “dummy” parent. After Essbase adds all new members to the outline, review the added members and move or delete them in Outline Editor.

When Essbase encounters a new member in the data source, it adds the new member as a child of the parent that you define. The parent must be part of the outline before you start the dimension build.

For example, the data source in Figure 77, Rules File Fields Set to Add Members as a Child of a Specified Parent (SIBPAR.TXT) contains two new members (600-54 and 780-22) for the Product dimension (field 1). Assume that you previously added a member called NewProducts under the Products dimension.

Figure 77. Rules File Fields Set to Add Members as a Child of a Specified Parent

To add the example members to the database under the NewProducts member, set the values shown in Table 45 in the rules file:

Table 45. Example of Adding Members as a Child of a Specified Parent

Field	Value	See
Field 1 (Product)	Do not select a field type for the field Set the dimension for the field to Product (field 1 is displayed as Product, as shown in Figure 77, Rules File Fields Set to Add Members as a Child of a Specified Parent)	Setting Field Type Information
Fields 2 through 6	Ignore the fields	Ignoring Fields
Product dimension	Select the “Add as child of” build method	Selecting a Build Method Enter NewProducts in the Add as Child of text box.

Figure 78, Tree for Adding Members as a Child of a Specified Parent shows the tree that Essbase builds from this data source and rules file.

Figure 78. Tree for Adding Members as a Child of a Specified Parent

Building Attribute Dimensions and Associating Attributes

When a data source contains attribute information, you must use one or more rules files to build attribute dimensions and to associate attributes with members of their base dimensions.

You can use rules files to build attribute dimensions dynamically, to add and delete members, and to establish or change attribute associations.

Working with attributes involves the following operations:

• If the base dimension does not exist, you must build it.
• You must build the attribute dimension.
• You must associate members of the base dimension with members of the attribute dimension.

You can use any of the following approaches to perform these operations:

• Build the base and attribute dimensions and perform the associations all at once. Doing so, you use a single rules file to build the base dimension and one or more attribute dimensions to associate each attribute with the appropriate member of the base dimension. Because this approach uses a single rules file, it can be the most convenient. Use this approach if the base dimension does not exist and each source data record contains all attribute information for each member of the base dimension.
• Build the attribute dimension and perform the associations in one rules file. Assuming that the base dimension is built in a separate step or that the base dimension already exists, you can build an attribute dimension and associate the attributes with the members of the base dimension in one step. You need only to define the attribute associations in the rules file. See Associating Attributes.
• Build the attribute dimension and then perform the associations using separate rules files. Assuming that the base dimension is built in a separate step or that the base dimension already exists, you can build an attribute dimension and associate the attributes with the members of the base dimension in separate steps. Build the attribute dimension, and then associate the attribute members with members of the base dimension. Use this approach when you build numeric attribute dimensions that are multilevel or that have members that represent different-sized ranges.

The following sections describe how to build attribute dimensions.

Building Attribute Dimensions

Before you build attribute dimensions in a database, you must define the attribute member name formats for the outline. See Setting Member Names in Attribute Dimensions.

You can build attribute dimensions in one of the following ways:

• The same way in which you build standard dimensions.
  SeeProcess for Data Loading and Dimension Building.
• At the same time as you associate attributes with members of the base dimension.
  See Associating Attributes.

Essbase does not support concurrent attribute association with the Add as build methods.

When you define the rules file for building attribute dimensions, specify the base dimension and the name of the attribute dimension file.

Associating Attributes

Whether you build the attribute dimension and associate the attribute members with the members of the base dimension in one step or in separate steps, define the fields as described in this section.

Note:

If you are working with a multilevel attribute dimension or with an attribute dimension of the type numeric, Boolean, or date, the rules file requires an additional field. See Working with Multilevel Attribute Dimensions.

Every record of the source data must include at least two columns: one for the member of the base dimension and one for the attribute value of the base dimension member. In the same source data record you can include additional columns for other attributes that you want to associate with the member of the base dimension. You must position the field for the member of the base dimension before any of the fields for the members of the attribute dimension.

Define the field type for the attribute dimension member as the name of the attribute dimension, use the generation or level number of the associated member of the base dimension, and specify the base dimension name. For example, as shown in the ATTRPROD.RUL file in Figure 79, Rules File for Associating Attributes, the field definition Ounces3,Product specifies that the field contains members of the Ounces attribute dimension. Each member of this field is associated with the data field that is defined as the generation 3 member of the base dimension Product. Based on this field definition, Essbase associates the attribute 64 with the 500-10 member.

Figure 79. Rules File for Associating Attributes

You can have Essbase use the attribute columns to build the members of the attribute dimensions. In Data Prep Editor, in the Dimension Build Settings tab of the Dimension Build Settings dialog box, clear the “Do not create members” option for the base dimension. See “Setting Member Properties” in the Oracle Essbase Administration Services Online Help.

When you are working with numeric ranges, you may need to build attribute dimensions and perform associations in separate steps. See Working with Numeric Ranges.

The Caffeinated3,Product field in Figure 79, Rules File for Associating Attributes shows how to associate attributes from additional single-level attribute dimensions. Because the base dimension is already specified, you need only to define an additional field for each attribute that you want to associate with the member of the base dimension.

The file in Figure 79, Rules File for Associating Attributes associates attributes as shown in the outline in Figure 80, Associating Attributes. The members 500, 500-10, and 500-20 are new members of the base dimension, Product. The member 64 is a new member of the Ounces attribute dimension.

Figure 80. Associating Attributes

Updating Attribute Associations

You can also use the rules file shown in Figure 79, Rules File for Associating Attributes to change attribute associations. Ensure that you allow association changes. In Data Prep Editor, on the Dimension Build Settings tab of the Dimension Build Settings dialog box, check “Allow association changes” for the base dimension. See “Setting Member Properties” in the Oracle Essbase Administration Services Online Help.

Note:

For duplicate member situations, the field to which the attribute is associated must contain the qualified member name. See Building Qualified Member Names Through the Rules File.

Removing Attribute Associations

To remove attribute associations, use the same process as for updating them, plus the following steps:

• In the Dimension Build Properties tab of the Field Properties dialog box, select “Delete when the field is empty” for the attribute field. (This option is ignored if “Allow association changes” is not selected.)
• Leave the field empty or NULL in the data source.

Working with Multilevel Attribute Dimensions

Multilevel, numeric, Boolean, and date attribute dimensions can have duplicate level 0 members. For example, associated with a Product dimension, you can have a Size attribute dimension with two levels. Level 1 categorizes sizes by men or by women. The level 0 members (attributes) are the actual sizes. You can have a member named 8 under Women and member named 8 under Men.

When an attribute is part of a multilevel numeric, Boolean, or date attribute dimension, the source data must include columns for all generations or levels of the attribute dimension. In the rules file, you must make copies of all fields that comprise the levels of the attribute dimension. Define the first set of attribute fields to build the attribute dimension. Define the second set of attribute fields to associate the attributes with the appropriate base dimension members. To ensure association with the correct attribute, indicate the parent field for the attribute field by making a copy of the parent field and setting the copy of the parent field as the field type Attribute Parent.

The position of the fields in the rules file is important.

• Place the copied attribute dimension field or fields that define the association immediately to the right of the field for the members of the base dimension.
• For a multilevel attribute dimension, place the attribute parent field immediately to the left of the field that is the child of the attribute parent.

The following steps describe how to define the fields in the rules file to build a multilevel attribute dimension and associate its members with members of its base dimension. This example uses the level references build method.

1. In the rules file, in field 1 and field 2, define the attribute dimension fields in the same way in which you define standard dimensions; specify type (level or generation), number, and dimension name.
   Essbase uses field1 and field2 to build the attribute dimension.
2. Define the fields for building the base dimension.
   In the following example, you are defining the level 0 and level 1 fields for the Product dimension. Figure 81, Defining Multilevel Attribute Dimensions Before Adding the Association Fields shows the fields of the rules file at this stage.
   
   Figure 81. Defining Multilevel Attribute Dimensions Before Adding the Association Fields

   
3. To define the association, make a copy of the field that contains the level 0 attribute.
   In the current example, make a copy of field 1.
   
   1. Use the attribute dimension name as the field type and specify the generation or level number of the member of the base dimension with which Essbase associates the attribute; for example, Size0.
   2. Specify the base dimension; for example, Product.
   3. Move the new field immediately to the right of the field for the base dimension with which Essbase associates the attribute.
      In the current example, move the new field to the right of the field Level0, Product.
4. Make a copy of the field containing the parent of the attribute field.
   In the current example, make a copy of field 2.
   
   1. Set the field type of the new field as Attribute Parent and specify the generation or level number of the base member with which you want Essbase to associate the attribute; for example, ATTRPARENT0.
   2. Specify the attribute dimension; for example, Size.
   3. Move the ATTRPARENT field immediately to the left of the attribute association field that you created in step 3.

As shown in Figure 82, Source Data and Rules File for Building a Multilevel Attribute Dimension, the rules file now contains the field definitions to build the attribute dimension Size and to associate the members of Size with the appropriate members of the base dimension Product.

Figure 82. Source Data and Rules File for Building a Multilevel Attribute Dimension

When you run a dimension build with the data shown in Figure 82, Source Data and Rules File for Building a Multilevel Attribute Dimension, Essbase builds the Size attribute dimension and associates its members with the appropriate members of the base dimension. Figure 83, Multilevel Attribute Dimension shows the updated outline.

Figure 83. Multilevel Attribute Dimension

Working with Numeric Ranges

In many cases, you can use one rules file in a dimension build operation to dynamically build attribute dimensions for numeric ranges and to associate the members of the base dimension with the ranges. In the following situations, however, you must use two rules files: one to build the attribute dimension and one to associate the attributes with the appropriate members of the base dimension:

• When the range size is different for different members.
  For example, you can define small ranges for towns and cities with smaller populations, larger ranges for mid-sized cities, and ranges greater than 1,000,000 for cities with large populations.
• When the ranges are members of a multilevel attribute dimension.
  For example, the Population attribute dimension can have level 1 members that categorize the population ranges as Towns, Cities, and Metropolitan Areas.

The Population attribute dimension shown in Figure 84, Numeric Attribute Dimension with Different-Sized Ranges demonstrates both situations. Population is a multilevel, numeric attribute dimension with level 0 members representing ranges of different sizes.

Figure 84. Numeric Attribute Dimension with Different-Sized Ranges

You must use one rules file to build the Population dimension and another rules file to associate the Population dimension members as attributes of members of the base dimension.

Building Attribute Dimensions that Accommodate Ranges

First, create a rules file that uses the generation, level, or parent-child build method to build the attribute dimension. In the rules file, specify the following information:

• The name of the attribute dimension and its associated base dimension.
• The fields for building the attribute dimension.
  See Setting Field Type Information.

The source data must be in attribute sequence, in ascending order. If ranges have different sizes, the source data must include a record for every attribute range.

Note:

In later builds, you cannot insert attribute members between existing members.

To use the generation method to build the outline in Figure 84, Numeric Attribute Dimension with Different-Sized Ranges, you must sequence the source data in ascending sequence, based on the numeric attribute value. Define the fields in a rules file as shown in Figure 85, Rules File for Building a Numeric Attribute Dimension with Ranges. Additionally, Figure 85, Rules File for Building a Numeric Attribute Dimension with Ranges shows how to associate aliases with attributes.

Figure 85. Rules File for Building a Numeric Attribute Dimension with Ranges

Associating Base Dimension Members with Their Range Attributes

After you build the numeric attribute dimension ranges, you need a rules file to associate the members of the base dimension with their attributes. The source data includes fields for the members of the base dimension and fields for the data values that Essbase uses to associate the appropriate Population attribute.

Define the rules file as shown in Figure 86, Rules File for Associating Numeric Range Attributes.

Figure 86. Rules File for Associating Numeric Range Attributes

When you define the association field (for example, Population3, Market), place the attribute members within a range. In Data Prep Editor, on the Dimension Build Properties tab of the Field Properties dialog box, select “Place attribute members within a numeric range.”

Note:

Figure 86, Rules File for Associating Numeric Range Attributes includes a city, Boston, whose population of 3,227,707 is outside the ranges of the attribute dimension in Figure 84, Numeric Attribute Dimension with Different-Sized Ranges, where the ranges extend only to 3,000,000. To allow for values in the source data that are outside the ranges in the attribute dimension, enter a range size, such as 1000000. Essbase uses the range size to add members to the attribute dimension above the existing highest member or below the existing lowest member, as needed.

Caution!

After you associate members of the base dimension with members of the attribute dimension, if you manually insert new members into the attribute dimension or rename members of the attribute dimension, you may invalidate existing attribute associations. Consider an example where numeric range attributes are defined as “Tops of ranges” and an attribute dimension contains members 100, 200, 500, and 1000. A base dimension member with the value 556 is associated with the attribute 1000. If you rename a attribute dimension member from 500 to 600, the base dimension member with the value 556 now has an invalid association. This base member is still associated with the attribute 1000 when it should be associated with the attribute 600. If you manually insert new members or rename existing members, to ensure that associations are correct, rerun the dimension build procedure and associate the base members with the changed attribute dimensions. For example, rerunning the attribute association procedure correctly associates the member of the base dimension with the value 556 with the new attribute 600.

Ensuring the Validity of Associations

To ensure the validity of attribute associations, you must select the correct dimension building options and perform the builds in the proper sequence.

• Adding or Changing Members of the Attribute Dimension: After you associate members of a base dimension with their numeric attribute ranges, if you manually insert new members or rename existing members in the attribute dimension, ensure that associations between attributes and base members are correct by performing one of the following tasks:
  
  • Rerun the dimension build procedure that associates the base members with the changed attribute dimension.
  • Use Outline Editor to manually review and fix, as needed, the associations of all base dimensions.
• Deleting Members from the Attribute Dimension: You can delete all members of an attribute dimension so that you can rebuild the dimension with new data. In Data Prep Editor, on the Dimension Building Properties tab on the Field Properties dialog box, click the Ranges button and select “Delete all members of this attribute dimension.” Essbase uses the start value and range size value to rebuild the attribute dimension. To ensure proper attribute association, on the Dimension Build Settings tab of the Dimension Build Settings dialog box, you must select the “Allow association changes” option for the base dimension.
• Adding Members to the Base Dimension: You can use the same rules file to add new members to the base dimension and to associate the new members with their numeric range attributes simultaneously. Provide a value for the range size. In Data Prep Editor, on the Dimension Build Properties tab in the Field Properties dialog box, click the Ranges button and specify the range size for the attribute dimension.
  If Essbase encounters a base dimension value that is greater than the highest attribute member by more than the range size or is lower than the lowest attribute member by more than the range size, it creates members in the attribute dimension to accommodate the out-of-range values.
  For example, in Figure 87, Dynamically Adding Attribute Range Members, the numeric range attributes are defined as “Tops of ranges.” The highest value member of the Population attribute dimension is 3000000. If the source data includes a record with the population 4,420,000, and the range size is 1000000, Essbase adds two members to the attribute dimension, 4000000 and 5000000, and associates the base member with the 5000000 attribute.
  
  Figure 87. Dynamically Adding Attribute Range Members

  

When you add range members and base dimension members at the same time, Essbase does not create aliases for the new members of the attribute dimension. If you want aliases that describe the range values for the new members of the attribute dimension, you must add the aliases in a separate operation.

Reviewing the Rules for Building Attribute and Base Dimensions

The information in this section describes areas unique to defining and associating attributes through a dimension build.

Getting Ready

• Before running a dimension build, you must define the attribute member name formats for the outline.
  See Setting Member Names in Attribute Dimensions.
• Defining new attribute dimensions in a rules file is different from defining new standard dimensions in a rules file.

Defining Fields in Rules Files

Rules files that are used to build single-level attribute dimensions require fewer field types than rules files that build and associate members of multilevel attribute dimensions.

• For single-level attribute dimensions, define the field that contains the attribute values as the field to be associated with the members of the base dimension. A dimension build uses the defined field to add new members to the attribute dimension.
  See Associating Attributes.
• For multilevel attribute dimensions, Essbase requires fields that define each generation or level in the attribute dimension and fields that define the associations. Use the new field type, Attribute Parent, to identify fields that are parent members for the attribute members being associated.
  See Working with Multilevel Attribute Dimensions.

Controlling Adding New Attribute Members

When Essbase encounters attribute data values that are not members of the attribute dimension, it automatically adds the values as new members. To prevent adding new members to attribute dimensions, in the Dimension Build Settings tab of the Dimension Build Settings dialog box, select the “Do not create members” option for the attribute dimension.

See “Setting Member Properties” in the Oracle Essbase Administration Services Online Help.

Controlling Associations

You can control the following associations:

• Making changes to attribute associations
  In Data Prep Editor, in the Dimension Build Settings tab of the Dimension Build Settings dialog box, select the “Allow association changes” option for the attribute dimension.
  See “Setting Member Properties” in the Oracle Essbase Administration Services Online Help.
• Enabling automatic association of base members with attributes that represent ranges of values
  In Data Prep Editor, on the Dimension Building Properties tab of the Field Properties dialog box, click the Ranges button and define the size of the range.
  See Setting Field Type Information.
• Concurrent attribute associations
  Use any build method except the “Add as build” methods.
  See Understanding Build Methods.

Note:

Because attributes are defined only in the outline, the data load process does not affect them.

Building Shared Members by Using a Rules File

The data associated with a shared member comes from an actual member with the same name as the shared member. Because the shared member stores a pointer to data contained in the actual member, the data is shared between the members and is stored only once.

For example, in the Sample.Basic database, the 100-20 (Diet Cola) member rolls up into the 100 family and into the Diet family.

Figure 88. Shared Members in the Sample.Basic Database

You can share members among as many parents as you want. Diet Cola has two parents (100 and Diet), but you can define it to roll up into more parents.

You can share members at multiple generations in the outline. In Figure 88, Shared Members in the Sample.Basic Database, Diet Cola is shared by two members at generation 2 in the outline, but it can be shared by a member at generation 3 and a member at generation 4 as shown in Figure 96, Members Shared at Different Generations.

Creating shared members at different generations in the outline is easy in Outline Editor; creating shared members using dimension build is more difficult. You must pick the build method and format the data source carefully.

The following sections describe how to build shared members in the outline by using a data source and a rules file.

Note:

You should not create an outline in which a shared member is located before the actual member with which it is associated.

Sharing Members at the Same Generation

Members that are shared at the same generation roll up into the same branch. In the Sample.Basic database, 100-20 (Diet Cola) is shared by two parents (100 and Diet). Both parents roll up into the same branch (the Product dimension), and both parents are at generation 2, as shown in the following figure:

Figure 89. Members Shared at the Same Generation

This scenario is the simplest way to share members. You can share members at the same generation by using any of the build methods discussed in the following sections.

Using Generation References to Create Same Generation Shared Members

To create shared member parents at the same generation by using the generation references build method, define the field type for the parent of the shared members as DUPGEN. A duplicate generation is a generation with shared members for children. Use the same GEN number as the primary member.

For example, to create the Diet parent and share the 100-20, 200-20, 300-20, and 400-20 members, use the sample data source file (SHGENREF.TXT) and set up the rules file so that the fields look like the sample rules file (SHGENREF.RUL) shown in Figure 90, Sample Generation Shared Member Rules File. 100 is the Cola family, 200 is the Root Beer family, 300 is the Cream Soda family, and the -20 after the family name indicates a diet version of the soda.

Figure 90. Sample Generation Shared Member Rules File

The data source and rules file illustrated in Figure 90, Sample Generation Shared Member Rules File build the following tree:

Figure 91. Sample Generation Shared Member Rules Tree

Using Level References to Create Same Generation Shared Members

To create shared members of the same generation by using the level references build method, first ensure that the primary and any secondary roll-ups are specified in one record. You can specify as many secondary roll-ups as you want, as long as they are all in one record.

Define the field type for the shared member as LEVEL. Then enter the level number. To create a shared member of the same generation, set the level number of the secondary roll-up to have the same number of levels as the primary roll-up. While processing the data source, Essbase creates a parent at the specified level and inserts the shared members under it.

For example, to create the shared 100-20 (Diet Cola), 200-20 (Diet Root Beer), 300-20 (Diet Cream Soda), and 400-20 (Diet Fruit Soda) members in the Sample.Basic database, use the sample data source file (SHLEV.TXT) and set up the rules file so that the fields look like the sample rules file (SHLEV.RUL) shown in Figure 92, Sample Level Shared Member Rules File.

Figure 92. Sample Level Shared Member Rules File

The data source and rules file illustrated in Figure 92, Sample Level Shared Member Rules File build the following tree:

Figure 93. Sample Level Shared Member Rules Tree

Using Parent-Child References to Create Same Generation Shared Members

To create shared members of the same generation by using the parent-child references build method, define the PARENT and CHILD field types. Ensure that Essbase is set up to allow sharing (in the Dimension Build Settings tab of the Dimension Build Settings dialog box, clear the “Do not share” option). When sharing is enabled, Essbase automatically creates duplicate members under a new parent as shared members.

Figure 94. Sample Parent-Child Shared Members Rules File

The data source and rules file illustrated in Figure 94, Sample Parent-Child Shared Members Rules File build the following tree:

Figure 95. Sample Parent-Child Shared Member Rules Tree

Sharing Members at Different Generations

Sometimes you want shared members to roll up into parents that are at different generations in the outline. For example, in Figure 96, Members Shared at Different Generations, the shared members roll up into parents at generation 2 (Diet) and at generation 3 (TBC and Grandma’s). This outline assumes that TBC (The Beverage Company) buys some beverages from outside vendors: it buys 200-20 (Diet Root Beer) from a vendor named Grandma’s.

Figure 96. Members Shared at Different Generations

To share members across parents at different generations in the outline, use one of the following build methods.

Using Level References to Create Different Generation Shared Members

To create shared members of different generations by using the level references build method, ensure that primary and secondary roll-ups are specified in one record. You can specify as many secondary roll-ups as you want, as long as they are all in one record.

Define the field type for the shared member as LEVEL. Then enter the level number. While processing the data source, Essbase creates a parent at the specified level and inserts the shared members under it.

For example, to share the products 100-20, 200-20, and 300-20 with a parent called Diet and two parents called TBC and Grandma’s, use the sample data file and the rules file shown in Figure 97, Level References Sample Rules File for Shared Members at Different Generations:

Figure 97. Level References Sample Rules File for Shared Members at Different Generations

The data source and rules file illustrated in Figure 97, Level References Sample Rules File for Shared Members at Different Generations build the tree illustrated inFigure 96, Members Shared at Different Generations.

Using Parent-Child References to Create Different Generation Shared Members

To create shared members at the different generation using the parent-child references build method, define the PARENT and CHILD field types. Ensure that Essbase is set up to allow sharing (on the Dimension Build Settings tab of the Dimension Build Settings dialog box, clear the “Do not share” option). When sharing is enabled, Essbase automatically creates duplicate members under a new parent as shared members.

Figure 98. Parent-Child References Sample Rules File for Shared Members at Different Generations

The data source and rules file illustrated in Figure 98, Parent-Child References Sample Rules File for Shared Members at Different Generations build the tree illustrated in Figure 96, Members Shared at Different Generations.

Sharing Non-Level 0 Members

Sometimes you want to share non-level 0 members (members that are not at the lowest generation). For example, in Figure 99, Non-Level 0 Members Shared at Different Generations, 100, 200, and 300 are shared by TBC and Grandma’s. This outline assumes that TBC buys some of its product lines from outside vendors: it buys 200 (all root beer) from a vendor named Grandma’s.

Figure 99. Non-Level 0 Members Shared at Different Generations

To share non-level 0 members, use one of the following build methods:

Using Level References to Create Non-Level 0 Shared Members

To create shared non-level 0 members by using the level references build method, ensure that primary and secondary roll-ups are specified in one record. You can specify unlimited secondary roll-ups, as long as they are all in one record.

Define the field type for the parent of the shared member as duplicate level (DUPLEVEL), and then enter the level number. To create a shared member of the same generation, set the level number of the secondary roll-up to have the same number of levels as the primary roll-up. While processing the data source, Essbase creates a parent at the specified level and inserts the shared members under it.

For example, to share the product lines 100, 200, and 300 with a parent called Soda and parents called TBC and Grandma’s, use the sample data file and rules file shown in Figure 100, Level References Sample Rules File for Non-Level 0 Shared Members at Different Generations. This data source and rules file work only if the Diet, TBC, and Grandma’s members exist in the outline. The DUPLEVEL field is always created as a child of the dimension (at generation 2), unless the named level field already exists in the outline.

Figure 100. Level References Sample Rules File for Non-Level 0 Shared Members at Different Generations

The data source and rules file illustrated in Figure 100, Level References Sample Rules File for Non-Level 0 Shared Members at Different Generations build the tree illustrated in Figure 99, Non-Level 0 Members Shared at Different Generations.

Using Parent-Child References to Create Non-Level 0 Shared Members

The parent-child references build method is the most versatile for creating shared members. It does not have any restrictions on the position of the shared members in the outline, unlike the generation references and level references build methods.

To create shared non-level 0 members at the same generation using the parent-child references build method, define the PARENT and CHILD field types. Ensure that Essbase is set up to allow sharing (clear “Do Not Share” in the Dimension Build Settings tab of the Dimension Build Settings dialog box). When sharing is enabled, Essbase automatically creates duplicate members under a new parent as shared members.

Figure 101. Parent-Child Sample Rules File for Non-Level 0 Shared Members

The data source and rules file illustrated in Figure 101, Parent-Child Sample Rules File for Non-Level 0 Shared Members build the tree illustrated in Figure 99, Non-Level 0 Members Shared at Different Generations.

Building Multiple Roll-Ups by Using Level References

To enable the retrieval of totals from multiple perspectives, you can also put shared members at different levels in the outline. Use the level references build method. For example, the rules file (LEVELMUL.RUL) shown in Figure 102, Rules File Fields Set to Build Multiple Roll-Ups Using Level References specifies build instructions for levels in the Product dimension:

Figure 102. Rules File Fields Set to Build Multiple Roll-Ups Using Level References

Because the record is so long, this second graphic shows the rules file scrolled to the right to show the extra members:

Figure 103. Scrolled Window

When you run the dimension build using the data in Figure 103, Scrolled Window, Essbase builds the following member tree:

Figure 104. Multiple Roll-Ups

This example enables analysis not only by package type (Cans), but also by packaging material (comparing sales of aluminum cans and steel cans).

Because Product is a sparse dimension, you can use an alternative outline design to enable retrieval of the same information. For example, consider creating a multilevel attribute dimension for package type with Steel and Aluminum as level 0 members under Can. For outline design guidelines, see Analyzing Database Design.

Creating Shared Roll-Ups from Multiple Data Sources

In many situations, the data for a dimension is in multiple data sources. If you are building dimensions from more than one data source and want to create multiple roll-ups, load the first data source using the most appropriate build method, and then load all other data sources using the parent-child references build method. Ensure that Essbase is set up to allow sharing (clear “Do Not Share” on the Dimension Build Settings tab of the Dimension Build Settings dialog box).

For example, using the following Product data source:

"Soft Drinks"   Cola
"Soft Drinks"   "Root Beer"
Cola            TBC
"Root Beer"     Grandma’s

Essbase builds the tree illustrated in Figure 105, Soft Drinks Tree:

Figure 105. Soft Drinks Tree

Then load the second data source below to relate the products to the vendors using the parent-child build method. Ensure that Essbase is set up to allow sharing.

Vendor   TBC
Vendor   Grandma’s

Essbase builds the tree illustrated in Figure 106, Shared Roll-Ups Tree:

Figure 106. Shared Roll-Ups Tree

Building Duplicate Member Outlines

Duplicate member outlines contain multiple members with the same name, where the values are not shared. In unique member outlines, only shared members can have the same name. See Creating and Working With Duplicate Member Outlines.

The rules file enables you to set whether dimensions, levels, and generations in a duplicate member outline are unique or can include duplicate members.

  To set dimension uniqueness during a dimension build, see “Setting Dimension Properties” in the Oracle Essbase Administration Services Online Help.

In the rules file, generation or level uniqueness is set on the Generation/Level Names tab of the Dimension Properties dialog box.

  To set generation or level uniqueness during a dimension build, see “Setting Dimension Properties” in the Oracle Essbase Administration Services Online Help.

Uniquely Identifying Members Through the Rules File

To ensure that duplicate member outline hierarchies are built correctly, use qualified member names in the data source or use the rules file to construct qualified member names from fields in the data source.

In most situations, the reference method and arrangement of fields provide enough information for Essbase to map data source columns to members in a duplicate member outline. The dimension build rules file for duplicate member is similar to the rules file for unique member outlines.

The following operations require more complex rules files for qualified member names:

• Parent-child dimension builds: The parent-child build method requires two fields, one for parent and one for child. For duplicate member situations the parent field must contain the qualified member name. Parent-child dimension builds on duplicate member outlines do not support attribute association. To ensure that attributes are associated with the correct members, perform the associations in a separate pass, using a generation-reference or level-reference rules file.
• Association of attributes to existing members in the outline: For duplicate member situations, the field to which the attribute is associated must contain the qualified member name.

Building Qualified Member Names Through the Rules File

If the data source does not contain the qualified member name as a field, you can use the rules file to edit and join multiple fields resulting in qualified member names.

To create qualified member names, use the Field Edits tab of the Data Source Properties dialog box to copy, move, and join fields, and to create brackets and periods. For example, you can assign population attributes to existing city members in a duplicate member dimension. You can use move, join, and create operations to build the qualified name.

For example, the cities in the following data source already exist in the outline. You want to associate with the cities the population attributes in the last column of this four-column data source:

Central "Kansas City" Kansas     706010
Central "Kansas City" Missouri   1070052
East    "New York"    "New York" 8104079

Editing this source through the rules file to build qualified names and sequence the field columns properly involves the following edits:

• Using Create using text operations, create one field each for the following text elements:
  
  • [
  • ].[
  • ].[
  • ]
• Using Move operations, move the fields to the following sequence:
  

  1   2         3     4        5    6            7   8 
  [   Central   ].[   Kansas   ].[  Kansas City  ]   706010

• Using Join operations, join together fields 1 2 3 4 5 6 7 to create the single field to which the attributes are to be associated: [Central].[Kansas].[Kansas City]. The rules file now shows two fields:
  

  1                                 2
   [Central].[Kansas].[Kansas City]  706010