Introducing Dynamic Dimension Building

Some Hyperion Essbase OLAP Server dimensions, such as those related to product codes and customer numbers, contain hundreds or even thousands of members. It is more efficient to build, change, or remove these dimensions dynamically, using a data source and a rules file, than it is to add or change each member manually in Outline Editor.

• The data source can specify member names, their aliases, formulas and consolidation properties, the names of generations and levels, currency name and category, data storage properties, attributes, and user-defined attributes (UDAs).
• The rules file tells Hyperion Essbase which build method to use, specifies whether the data is in sorted or random order, and tells Hyperion Essbase how to transform the data before loading it. You use Data Prep Editor to create rules files. It is best to create a separate rules file for each dimension. For more information on data sources and rules files, see Introducing Data Loading.

This chapter provides a background on dynamic dimension building.

• Workflow for Creating Dimensions Using Rules Files
• Introduction to 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 Using a Rules File
• Security and Multi-User Considerations

For information on how to build dimensions dynamically, see Building Dimensions Using a Rules File.

Workflow for Creating Dimensions Using Rules Files

This section describes the process for building dimensions using a rules file. For more information on data sources and rules files, see Introducing Data Loading.

1. Determine whether to use the same rules file for dimension building and data loading.

   Use the same rules file to...	Use a different rules file to...
   Modify the outline based on the contents of the data source. Load the data source and build new dimensions at the same time.	Build an outline from scratch. Re-use the dimension build or data load separately. Perform different field operations. Use data sources that contain no data values, only dimensions.

   Note:

   You can neither merge nor separate rules files after you create them.

2. Use Data Prep Editor to create a dimension build rules file. Select View > Dimension Building Fields or click the Dimension Build button, , on the toolbar to put Data Prep Editor into dimension build mode.

   Set field and record operations in the rules file. The field and record operations determine how Hyperion Essbase transforms the data before building the dimensions. For more information on how to set field and record operations, see Setting up a Rules File to Manipulate Records.

3. Select Options > Dimension Build Settings to open the Dimension Build Settings dialog box. This dialog box contains three tabs, each of which sets up a different part of the rules file:
   • Use the Dimension Definition tab to name the dimension and define its properties.
   • Use the Dimension Build Settings tab to specify the build method and indicate how to sort the members.
   • Use the Global Settings tab to specify which alias table to update, whether Hyperion Essbase should select the dense/sparse configuration, and how to combine selection and rejection criteria.
4. Validate and save the rules file using the Options > Validate menu command. See Validating and Saving Data Load Rules.
5. Perform the dimension build. See Performing a Data Load.

Introduction to Build Methods

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

Each record in a data source defines a single member of a dimension. Hyperion Essbase can read data sources in a variety of formats:

• Top-down data sources beginning with the most general information and progress to the most specific. Each record specifies the parent's name, the child's name, the children of that child, and so forth.
• Bottom-up data sources provide a complete description of the member, beginning with the most specific information and progressing to the most general. Each record specifies the name of the member, followed by the name of its parent, the name of its parent's parent, and so forth.
• Parent/child data sources specify the name of the parent and the name of the new child member, in that order, although they can specify other information as well.
• Other data sources consist of lists of new members; they do not specify where in the outline these members belong. Hyperion Essbase provides different algorithms to determine where to add these members.

The following table provides guidelines to help you select the appropriate build method for your data source:

Using Generation References

Top-down data sources are organized from the highest level down. Each record begins with the most general information and progresses to the most specific. The name of the member is at the end of the record. When building a dimension with a top-down data source, use the generation references build type. Use the rules file to specify the generation number and field type for each field in the data source. For more information about creating a rules file, see Setting up a Rules File to Manipulate Records.

Hyperion Essbase numbers members within a dimension according to their hierarchical position in the dimension. These are called generation references. Dimensions are always generation 1. All members at the same branch in a dimension are called generations. Generations are numbered top-down according to their position relative to the dimension.

For example, the Measures dimension in the Sample Basic database is generation 1. It has a Profit member, which is generation 2. Profit has members for Margin and Total Expenses, which are generation 3. To build a dimension using the generation references build method, you must specify the generation reference number in the rules file.

Figure 13-1: Generations

The top half of Figure 13-2 shows a top-down data source used to build the Product dimension, GENREF.TXT. The bottom half of Figure 13-2 shows the rules file for this data source, GENREF.RUL. The rules file specifies the generation number for each field in the data source. For more information, see Step 4: Setting Rules File Field Types.

Figure 13-2: Rules File for Generation Build

Figure 13-3 shows the tree Hyperion Essbase builds from this data source and rules file:

Figure 13-3: Generation References

Null Processing with Generation References

When you use the generation references build method, you can also choose to use null processing. When null processing is enabled, Hyperion Essbase processes nulls as follows:

• If the null occurs where Hyperion Essbase expects a GENERATION field, Hyperion Essbase promotes the next GENERATION field in place of the missing one. For example:

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

  
  

  When Hyperion Essbase reads the above file, it promotes the GEN4 field (100-10a) to GEN3.

• If a null occurs directly before a secondary field, Hyperion Essbase ignores the secondary field. Secondary field options are alias, property, formula, duplicate generation, duplicate generation alias, currency name, currency category, attribute parent, UDA, and a name of an attribute dimension. For example:

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

  
  

  When Hyperion Essbase reads the above file, it ignores the ALIAS2 field and promotes the GEN3 field (100-10) to GEN2 and the GEN4 field (100-10a) to GEN3.

• If the null occurs where Hyperion Essbase expects a secondary field, Hyperion Essbase ignores that field and continues loading. For example:

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

  
  

  When Hyperion Essbase reads the above file, it ignores the ALIAS2 field.

Using Level References

In bottom-up data sources, 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 would specify the member itself, then the name of its parent, then its parent's parent, and so forth.

Levels are defined from a bottom-up hierarchical structure. In the outline in Figure 13-4, for example, the lowest level members are at the bottoms of the branches in the Product dimension.

Figure 13-4: Generation and Level Numbers

To build the outline in Figure 13-4, you can use the bottom-up data source shown in Figure 13-5.

Figure 13-5: Bottom-up Data Source

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 next field, and so on. This is the opposite of how data is specified for generation references (top-down).

The rules file in Figure 13-6 uses the level reference build method to add members to the Product dimension in the Sample Basic database. The first column of the data source contains new members (100-10-12, 100-10-16, 100-20-12, and 100-20-16). Subsequent columns contain their parents. (Family-18 oz., and Family-24 oz.)

The rules file specifies the level number and field type for each field in the data source. For more information, see Step 4: Setting Rules File Field Types. To build the tree in Figure 13-7 for example, use Figure 13-6 to set up the data source, LEVEL.TXT, and rules file, LEVEL.RUL to match.

Figure 13-6: Rules File for Level Build

Figure 13-7 shows the tree Hyperion Essbase builds from this data source and rules file.

Figure 13-7: Levels

Null Processing with Level References

When you use the level references build method, you can also choose to use null processing. When null processing is enabled, Hyperion Essbase processes nulls as follows:

• If the null occurs where Hyperion Essbase expects a LEVEL field, Hyperion Essbase promotes the next LEVEL field in place of the missing one. For example:

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

  
  

  When Hyperion Essbase reads the above file, it promotes the LEVEL1 field(100-10) to LEVEL0 and the LEVEL2 field(100) to LEVEL1.

• If a null occurs directly before a secondary field, Hyperion 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.

  For example:

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

  
  

  When Hyperion Essbase reads the above file, it ignores the ALIAS0 field and promotes the LEVEL1 field(100-10) to LEVEL0, the LEVEL2 field(100) to LEVEL1.

• If the null occurs where Hyperion Essbase expects a secondary field, Hyperion Essbase ignores that field and continues loading. For example:

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

  
  

  When Hyperion Essbase reads the above file, it ignores the ALIAS0 field.

Using Parent/Child References

Use the parent/child references build method with data sources in which each record specifies the name of the new member and the name of the parent to which you want to add it.

Members in a database exist in parent/child relationships to one another. Figure 13-8 shows part of the Product dimension with its parent and children relationships identified.

Figure 13-8: Parents and Children

Parent/child data sources must contain at least two columns: the parent column and the child column, in that order. They can include columns with other information as well (for example, the alias of the new member, its attributes or its properties). The 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 the child. For more information, see Step 4: Setting Rules File Field Types. For example, the top half of Figure 13-9 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 is the child. In addition to parent and child fields, this example demonstrates associating aliases with the parent field.

Figure 13-9: Rules Files for Parent/Child Build

Figure 13-10 shows the tree that Hyperion Essbase builds from this data source and rules file.

Figure 13-10: Parents and Children

Adding a List of New Members

If a data source consists of a list of new members, without specifying their ancestors, Hyperion Essbase must decide where in the outline to add the new members. Hyperion Essbase provides three different build methods for this type of data source. Each uses a different algorithm to determine where to add the new members.

Depending upon which build method you specify, Hyperion Essbase can:

• Add each new member as a sibling of the existing member whose text most closely matches its own.
• Add each new member as a sibling of the lowest existing member.
• Add all new members as children of a specified parent (generally a "dummy" parent).

After you select your build method, you must specify the dimension to which each field maps. See Step 4: Setting Rules File Field Types.

After Hyperion Essbase has added all the new members to the outline, use Outline Editor to examine the outline. If necessary, move the new members into their correct positions. See Creating and Changing Database Outlines.

Adding Members Based Upon String Matches

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

For example, the data source in Figure 13-11, SIBSTR.TXT, contains two new members to add to the Product dimension in the Sample Basic database, 100-11 and 200-22. They are similar to strings in the Product dimension (they contain 3 digits, a dash, and two digits).

To add these members dynamically to the database, set the following values to match the rules file, SIBSTR.RUL, in Figure 13-11.

• For the Product column (field 1) on the Dimension Building Properties tab of the Field Properties dialog box:
  • 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 13-11.
• For the other data fields that are irrelevant to this operation (fields 2 through 6), on the Global Properties tab of the Field Properties dialog box, select "Ignore field during dimension build."
• In the Dimension Build Settings dialog box, for the Product dimension, select the "Add as sibling of matching string" method.

  Figure 13-11: Rules File Fields Set to Add Members as Siblings with String Matches

  

When you load the data in Figure 13-11, Hyperion Essbase builds the following member tree:

Figure 13-12: Adding Members as Siblings with String Matches Tree

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, that is, the lowest branch. When Hyperion Essbase encounters a new member in a data source, it scans the outline for the lowest branch of members. Hyperion Essbase adds the new member as a sibling of these members.

For example, the data source, SIBLOW.TXT, and rules file, SIBLOW.RUL, in Figure 13-13 contain new members (A100-10 and A100-99) to add to the Measures dimension in the Sample Basic database.

To add these members dynamically to the database, set the following values in the rules file:

• For the Measures column (field 3) on the Dimension Building Properties tab of the Field Properties dialog box:
  • 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 13-13.
• For the other data fields that are irrelevant to this operation (fields 1, 2, 4, 5, and 6), on the Global Properties tab of the Field Properties dialog box, select "Ignore field during dimension build."
• In the Dimension Build Settings dialog box, for the Measures dimension, select the "Add as sibling of lowest level" build method.

  Figure 13-13: Rules File Fields Set to Add Members as Siblings of the Lowest Level

  

When you load the data in Figure 13-13, Hyperion Essbase builds the following member tree:

Figure 13-14: Adding Members as Siblings of the Lowest Level

Adding All New Members to a Specified Parent

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

When Hyperion Essbase encounters a new member in the data source, it adds the new member as a child of the parent defined in the Dimension Build Settings dialog box. This parent must be part of the outline before you start the dimension build.

For example, the data source in Figure 13-15, SIBPAR.TXT, contains new members, 600-54 and 780-22, for the Product dimension (field 1). Assume that you have already added a new member called NewProducts to the outline under the Products dimension.

To have Hyperion Essbase add these members dynamically to the database under the NewProducts member, set the following values:

• For the Product column (field 1) on the Dimension Building Properties tab of the Field Properties dialog box:
  • 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 13-15.
• For the other data fields that are irrelevant to this operation (fields 2 through 6), on the Global Properties tab of the Field Properties dialog box, select "Ignore field during dimension build."
• In the Dimension Build Settings dialog box, for the Product dimension, select the "Add as child of build" method and type NewProducts in the associated text box.

  Figure 13-15: Rules File Fields Set to Add Members as a Child of a Specified Parent

  

When you load the data in Figure 13-15, Hyperion Essbase builds the following member tree:

Figure 13-16: Adding Members as a Child of a Specified Parent

Building Attribute Dimensions and Associating Attributes

This section shows examples of rules files that are used 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 three 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 three approaches to perform these operations.

When you use an all-at-once approach, you use a single rules file to build the base dimension and one or more attribute dimensions and to associate the attributes with members 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 yet and each source data record contains all attribute information for each member of the base dimension.

If the base dimension is built in a separate step or already exists, you can build an attribute dimension and associate the attributes with the members of the base dimension in either of two ways:

• In a single step. You need only to define the attribute associations in the rules file. See Associating Attributes.
• In separate steps. Build the attribute dimension, and then associate the attribute members with members of the base dimension. You must use this approach when you build numeric attribute dimensions that are multilevel or that have members that represent different-sized ranges.

Building Attribute Dimensions

Before you build any attribute dimensions in a database, you must define the attribute member name formats for the outline. See Working with the Names of Members of Attribute Dimensions.

You can build attribute dimensions in either of the following two ways:

• The same way that you build standard dimensions, as described in Workflow for Creating Dimensions Using Rules Files.
• At the same time as you associate attributes with members of the base dimension, as described in Associating Attributes.

Hyperion Essbase does not support concurrent attribute association with the following three build methods: "Add as sibling of mbr with matching string," "Add as sibling of lowest level," and "Add as child of."

When you define the rules file for building attribute dimensions, be sure to specify the base dimension and the name of the attribute dimension file, as described in Naming New Attribute Dimensions.

Associating Attributes

Whether you build the attribute dimension at the same time as you associate its members with members of the base dimension or perform these tasks in separate steps, define the fields as described in this section.

However, when 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. For a complete example of a multilevel situation, see Working with Multilevel Attribute Dimensions.

Each record in the source data must include at least a column for the member of the base dimension and a column for the member's attribute value. 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 13-17, the field definition Ounces3,Product specifies that the field contains members of the Ounces attribute dimension. Each member in 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, Hyperion Essbase associates the attribute 64 with product 500-10.

Figure 13-17: Rules File for Associating Attributes

To have Hyperion Essbase use the data attribute columns to build members in the attribute dimensions, on the Dimension Build Settings tab in the Dimension Build Settings dialog box, for the base dimension, leave the Do Not Create Mbrs option clear. See Step 3: Specifying Changes to Dimensions.

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

The Caffeinated3,Product field in the example in Figure 13-17 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 13-17 associates attributes as shown in the resulting outline in Figure 13-18. 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 13-18: Associating Attributes

Updating Attribute Associations

You can also use the rules file shown in Figure 13-17 to change attribute associations. Make sure that you select "Allow Association Chgs" for the base dimension in the Dimension Build Settings tab of the Dimension Build Settings dialog box. See Step 3: Specifying Changes to Dimensions.

Working with Multilevel Attribute Dimensions

Multilevel, numeric, Boolean, or 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 the 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 the attribute fields to build the attribute dimension. Define the second set of attribute fields to associate the attributes with the base dimension member. To ensure association with the correct attribute, to indicate the parent field for the attribute field, make a copy of the parent field and set 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 procedure describes how to define the fields in the rules file to build this 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 that you define standard dimensions; specify level or generation type and number and dimension name.

   Hyperion Essbase uses these two fields to build the attribute dimension.

2. Define the fields for building the base dimension, in this example, the level 0 and level 1 fields for the Product dimension.

   Figure 13-19 shows the fields of the rules file at this stage.

   Figure 13-19: Defining Multilevel Attribute Dimensions Before Adding the Association Fields

   

3. To define the association, select Field > Create Using Join to make a copy of the field that contains the level 0 attribute. In this example, make a copy of field 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 Hyperion Essbase associates the attribute; for example, Size0.
   • Specify the base dimension; for example, Product.
   • Select Field > Move to move the new field immediately to the right of the field for the base dimension with which Hyperion Essbase associates the attribute. In this example, move it to the right of the field Level0,Product.
4. Make a copy of the field containing the parent of the attribute field; in this example, make a copy of field 2.
   • Set the field type of this new field as Attribute Parent and specify the generation or level number of the base member with which you want Hyperion Essbase to associate the attribute; for example, ATTRPARENT0.
   • Specify the attribute dimension; for example, Size.
   • Move the ATTRPARENT field immediately to the left of the attribute association field that you created in step 3.

As shown in Figure 13-20, the rules file now contains the field definitions to build the attribute dimension Size and to associate the members of Size with members of the base dimension Product.

Figure 13-20: Attribute Dimension

When you run a dimension build with the data shown in Figure 13-20, Hyperion Essbase builds the Size attribute dimension and associates its members with the members of the base dimension. Figure 13-21 shows the updated outline.

Figure 13-21: Multilevel Attribute Dimension

Working With Numeric Ranges

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

• When the size of the range is different for different members. For example, you can define several shorter ranges for towns and cities with smaller populations, larger ranges for mid-sized cities, and ranges of 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 13-22 demonstrates both situations. Population is a multilevel, numeric attribute dimension with level 0 members representing ranges of different sizes.

Figure 13-22: Numeric Attribute Dimension with Different-Sized Ranges

You must use one rules file to build this Population dimension and a second rules file to associate its members as attributes of members of the base dimension.

Building the Attribute Dimension

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

• The name of the attribute dimension and its associated base dimension. See Naming New Attribute Dimensions.
• The fields for building the attribute dimension.

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

To use the generation method to build the outline in Figure 13-22, 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 13-23.

Figure 13-23: Rules File for Building a Numeric Attribute Dimension with Ranges

Figure 13-23 also shows how you can associate aliases with attributes.

Associating the 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 a field for the data values that Hyperion Essbase uses to associate the appropriate Population attribute.

Define the rules file as shown in Figure 13-24.

Figure 13-24: Rules File for Associating Numeric Range Attributes

When you define the association field (for example, Population3,Market) be sure to click the Ranges button to open the Numeric Range Rules dialog box. Select "Place attribute members within a range."

CAUTION:

After you associate members of the base dimension with members of the attribute dimension, be aware that if you manually insert new members into the attribute dimension or rename existing 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 member of the attribute dimension 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 now be associated with the attribute 600. If you manually insert new members or rename existing members, to ensure associations are correct, you should rerun the dimension build procedure that associates the base members with the changed attribute dimension. For example, rerunning the attribute association procedure would correctly associate the member of the base dimension with the value 556 with the new attribute 600.

Working With Ranges

To ensure the validity of the attribute associations, you must be careful to select the correct dimension building options and to 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, you should make sure that associations between attributes and base members are correct. To ensure that the associations are correct, you can do one of the following:

• Rerun the dimension build procedure that associates the base members with the changed attribute dimension.
• Through Outline Editor, manually review and fix, as needed, the associations of all base dimensions.

Deleting Members from the Attribute Dimension: You can delete all existing members of an attribute dimension so you can rebuild the dimension with the new data. Select "Delete all members of this attribute dimension" in the Numeric Range Rules dialog box. Hyperion 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 in the Dimension Build Settings dialog box, for the base dimension you must select the "Allow Association Chgs" option.

Adding Members to the Base Dimension: You can use the same rules file to add new members simultaneously to the base dimension and to associate the new members with their numeric range attributes. In the Numeric Range Rules dialog box for the attribute dimension, be sure to provide a value for the range size.

If Hyperion Essbase encounters a new 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 for any intermediate ranges.

Consider the example, in Figure 13-22, where 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, Hyperion Essbase adds two members to the attribute dimension, 4000000 and 5000000, and associates the base member with the 5000000 attribute.

Figure 13-25: Dynamically Adding Attribute Range Members

When you add range members and base dimension members at the same time, Hyperion 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.

Rules Summary for Building Attribute and Base Dimensions

The following list describes a few areas unique to defining and associating attributes through dimension build.

Getting Ready

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

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 members of the base dimension. Dimension build also uses this field to add new members to the attribute dimension. See Associating Attributes.
• For multilevel attribute dimensions, Hyperion Essbase requires fields that define each generation or level in the attribute dimension in addition to 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 Hyperion 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, you must select the Do Not Create Mbrs option for the attribute dimension in the Dimension Build Settings dialog box. See Step 3: Specifying Changes to Dimensions.

Controlling Associations

• Hyperion Essbase does not make changes to attribute associations unless you select the Allow Association Chgs option for the attribute dimension in the Dimension Build Settings page of the Dimension Build Settings dialog box. See Step 3: Specifying Changes to Dimensions.
• To enable automatic association of base members with attributes that represent ranges of values, make sure you have defined the size of the range in the Advanced Numeric Rules dialog box. See Setting Field Information.
• Hyperion Essbase does not support concurrent attribute association with the two Add as Sibling methods and the Add as Child build method.

Building Shared Members Using a Rules File

The data associated with shared members comes from another real member with the same name. The shared member stores a pointer to data contained in the real member; thus the data is shared between the members and is only stored one time.

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

Figure 13-26: Shared Members in the Sample Basic Database

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

You can share members at multiple generations in the outline. In Figure 13-26, Diet Cola is shared by two members at generation 2 in the outline, but it could be shared by a member at generation 3 and a member at generation 4 as in Figure 13-34.

While creating shared members at different generations in the outline is easy in Outline Editor, they are a little more difficult to create using dynamic dimension building. You must pick your build method and format your data source carefully. The following sections describe how to build shared members in the outline using a data source and rules file.

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. Both parents roll up into the same branch, that is, the Product dimension, and both parents are at generation 2.

Figure 13-27: Members Shared at the Same Generation

This is the simplest way to share members. You can share members at the same generation using the following build methods:

• Creating Shared Members Using Generation References
• Creating Shared Members Using Level References
• Creating Shared Members Using Parent/Child References

Creating Shared Members Using Generation References

To create shared member parents at the same generation using the generation references build method, define the field type for the parent of the shared member 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 file, SHGENREF.TXT, and set up the rules file so that the fields look like SHGENREF.RUL, shown in Figure 13-28. Remember 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 13-28: Sample Generation Shared Member Rules File

This builds the following tree:

Figure 13-29: Sample Generation Shared Member Rules Tree

Creating Shared Members Using Level References

To create shared members at the same generation using the level references build method, first make sure that both 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 the roll-ups 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, Hyperion 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 (Fruit Soda) members in the Sample Basic database, use the sample file, SHLEV.TXT, and set up the rules file so that the fields look like SHLEV.RUL shown in Figure 13-30.

Figure 13-30: Sample Level Shared Member Rules File

This builds the following tree:

Figure 13-31: Sample Level Shared Member Rules Tree

Creating Shared Members Using Parent/Child References

To create shared members at the same generation using the parent/child references build method, define the PARENT and CHILD field types. Make sure that Do Not Share is cleared in the Dimension Build Settings page of the Dimension Build Settings dialog box. When Do Not Share is cleared, Hyperion Essbase automatically creates duplicate members under a new parent as shared members.

Figure 13-32: Sample Parent/Child Shared Members Rules File

This builds the following tree:

Figure 13-33: Sample Parent/Child Shared Member Rules Tree

Sharing Members at Different Generations

Sometimes you want shared members to roll up into parents at different generations in the outline. In Figure 13-34, for example, the shared members roll up into parents at generation 2 and generation 3 in the outline. This outline assumes that The Beverage Company (TBC) buys some of its beverages from outside vendors. In this case, it buys 200-20 (Diet Root Beer) from a vendor named Grandma's.

Figure 13-34: Members Shared at Different Generations

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

• Creating Shared Members Using Level References
• Creating Shared Members Using Parent/Child References

Creating Shared Members Using Level References

To create shared members at different generations using the level references build method, first make sure that both primary and secondary roll-ups are specified in one record. You can specify as many secondary roll-ups as you want, as long as the roll-ups 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, Hyperion 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 (The Beverage Company) and Grandma's, use the sample data file and rules file in Figure 13-35.

Figure 13-35: Level References Sample Rules File for Shared Members at Different Generations

This builds the tree in Figure 13-34.

Creating Shared Members Using Parent/Child References

To create shared members at the same generation using the parent/child references build type, define the PARENT and CHILD field types. Make sure that Do Not Share is not selected in the Dimension Build Settings page of the Dimension Build Settings dialog box so that Hyperion Essbase creates duplicate members as shared when it encounters them under a new parent.

Figure 13-36: Parent/Child References Sample Rules File for
Shared Members at Different Generations

This builds the tree in Figure 13-34.

Sharing Members with Branches

Sometimes you want to share non-leaf members (members that are not at the lowest generation). In Figure 13-37 for example, 100, 200, and 300 are shared by TBC and Grandma's. This outline assumes that TBC (The Beverage Company) buys some of its product lines from outside vendors. In this case, it buys 200 (all root beer) from a vendor named Grandma's.

Figure 13-37: Members with Branches Shared at Different Generations

To share members in the outline with branches below them, use the following build methods:

• Creating Shared Members Using Level References
• Creating Shared Members Using Parent/Child References

Creating Shared Members Using Level References

To create shared non-leaf members using the level references build method, first make sure that both primary and secondary roll-ups are specified in one record. You can specify as many secondary roll-ups as you want, as long as the roll-ups are all in one record.

Define the field type for the shared member's parent as duplicate level (DUPLEVEL). 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, Hyperion 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 two parents called TBC and Grandma's, use the sample data file and rules file shown in Figure 13-38. This data source and rules file only work if the Diet, TBC, and Grandma's members exist in the outline. The DUPLEVEL field is always created as a child of the dimension (that is, at generation 2), unless the named level field already exists in the outline.

Figure 13-38: Level References Sample Rules File for Shared Members at Different Generations with Branches

This builds the tree in Figure 13-37.

Creating Shared Members Using Parent/Child References

To create shared members at the same generation using the parent/child references build type, define the PARENT and CHILD field types. Make sure that Do Not Share is not selected in the Dimension Build Settings page of the Dimension Build Settings dialog box so that Hyperion Essbase creates duplicate members as shared when it encounters them under a new parent.

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.

Figure 13-39: Parent/Child Sample Rules File for Shared Members at Different Generations with Branches

This builds the tree in Figure 13-37.

Building Multiple Roll-Ups Using Level References

To enable retrieving totals from multiple perspectives, you can also put shared members at different levels in the outline. Use the level references build method. The rules file, LEVELMUL.RUL, in Figure 13-40 specifies an example of build instructions for levels in the Product dimension.

Figure 13-40: 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 after it has been scrolled to the right to show the extra members:

Figure 13-41: Scrolled Window

When you run the dimension build using the data in Figure 13-40, Hyperion Essbase builds the following member tree:

Figure 13-42: Multiple Roll-Ups

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

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

Creating Shared Roll-Ups from Multiple Data Sources Using Parent/Child References

In many situations, the data for a dimension is in two or more 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. Load all other data sources using the parent/child references build method. Make sure that "Do Not Share" is not selected so that Hyperion Essbase creates duplicate members as shared when it encounters them under a new parent.

For example, from the Product data source in Figure 13-44:

Figure 13-43: Soft Drinks Data Source

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

Hyperion Essbase builds the following tree:

Figure 13-44: Soft Drinks Tree

A second data source relates the products to the vendors:

Figure 13-45: Second Shared Roll-Ups Data Source

Vendor   TBC
Vendor   Grandma's

To create a member tree where the products are shared by the Vendor member, load the second data source using the parent/child build method. Make sure that "Do Not Share" is not selected so that Hyperion Essbase creates duplicate members as shared when it encounters them under a new parent.

Hyperion Essbase builds the following tree:

Figure 13-46: Shared Roll-Ups Tree

Security and Multi-User Considerations

Hyperion Essbase supports concurrent multiple users reading and updating the database. This means that users can use the database while you are dynamically building dimensions, loading data, or calculating the database.

• Security Issues: The security system prevents unauthorized users from changing the database. Only users with write access to a database can add dimensions to the database. Write access can be provided as global write access or by using filters.
• Multi-User Issues: You cannot build dimensions while other users are reading or writing to the database. After you build dimensions, Hyperion Essbase restructures the outline and locks the database for the duration of the restructure operation.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.