Understanding Advanced Dimension Building Concepts Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Understanding Advanced Dimension Building Concepts


This chapter discusses dimension building.

Understanding Build Methods

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

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


Table 22: Build Method Guidelines  

Type of Data in Each Record
Examples
Desired Operation
Build Method
Field Type Information

Top-down data: Each record specifies the parent's name, the child's name, the children of that child, and so forth.

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-leaf 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. Analytic Services 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.



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.

Analytic Services 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 114, 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, you must specify the generation reference number in the rules file.

Figure 114: Generations

The top half of Figure 115 shows a top-down data source GENREF.TXT. The data source is used to build the Product dimension. The bottom half of Figure 115 shows the rules file for the data source, GENREF.RUL. The rules file specifies the generation number for each field in the data source. For information on setting field types and references to pertinent topics, see Setting Field Type Information.

Figure 115: Rules File for Generation Build

Figure 116 shows the tree that Analytic Services builds from this data source and rules file:

Figure 116: Generation References

Dealing with Empty Fields

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

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

If null processing is enabled, Analytic Services processes nulls as follows:

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. In the outline in Figure 120, for example, the lowest level members are at the bottoms of the branches of the Product dimension.

Figure 120: Generation and Level Numbers

To build the outline in Figure 120, you can use the bottom-up data source shown in Figure 121.

Figure 121: 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).

The rules file in Figure 122 uses the level reference build method to add members to the Product dimension of the Sample Basic database. 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).

The rules file specifies the level number and the field type for each field of the data source. For more information on setting field types and references to pertinent topics, see Setting Field Type Information. To build the tree in Figure 123, for example, use Figure 122 to set up the data source, LEVEL.TXT, and the rules file, LEVEL.RUL.

Figure 122: Rules File for Level Build

Figure 123 shows the tree that Analytic Services builds from the data source and rules file of Figure 122.

Figure 123: Levels

Dealing with Empty Fields

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

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

If null processing is enabled, Analytic Services processes nulls as follows:

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

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 to one another. Figure 127 shows part of the Product dimension with its parent and children relationships identified.

Figure 127: 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 general information on setting field types and references to pertinent topics, see Setting Field Type Information. For example, the top half of Figure 128 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. In addition to identifying parent and child fields, this example associates aliases with the child field.

Figure 128: Rules Files for Parent-Child Build

Figure 129 shows the tree that Analytic Services builds from this data source and rules file.

Figure 129: Parents and Children

Adding a List of New Members

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

Note: Analytic Services does not support concurrent attribute association with the Add as build methods.

After Analytic Services adds all new members to the outline, it may be necessary to move the new members into their correct positions using Outline Editor. For a brief discussion and references to pertinent topics, see Positioning Dimensions and Members.

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 Analytic Services encounters a new member in a data source, it scans the outline for a member name with similar text. Analytic Services then adds the new member as a sibling of the member with the closest string match.

For example, the data source in Figure 130, SIBSTR.TXT, contains two new members to add to the Product dimension in the Sample Basic database, 100-11 and 200-22. The new members are similar to strings in the Product dimension in that they contain 3 digits, 1 dash, and 2 digits.

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


In the rules file
Perform the following task
For brief discussions and references to pertinent topics

Select 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 129.

See Setting Field Type Information.

Select field 2 through field 6.

Ignore the fields.

See Ignoring Fields.

Select the Product dimension.

Select the "Add as sibling of matching string" build method.

See Selecting a Build Method.



Figure 130: Rules File Fields Set to Add Members as Siblings with String Matches

Figure 131 shows the tree that Analytic Services builds from this data source and rules file.

Figure 131: 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, that is, at the leaf branch. When Analytic Services encounters a new member in a data source, it scans the outline for the leaf branch of members. Analytic Services adds the new member as a sibling of these members.

Note: If the outline contains more than one group of members at this level, Analytic Services 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 132 contain new members (A100-10 and A100-99) to add to the Measures dimension of the Sample Basic database.

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

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


In the rules file
Perform the following task
For brief discussions and references to pertinent topics

Select 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 132.

See Setting Field Type Information.

Select fields 1, 2, 4, 5, 6.

Ignore the fields.

See Ignoring Fields.

Select the Measures dimension.

Select the "Add as sibling of lowest level" build method.

See Selecting a Build Method.



Figure 133 shows the tree that Analytic Services builds from this data source and rules file.

Figure 133: 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 Analytic Services adds all new members to the outline, review the added members and move or delete them in Outline Editor.

When Analytic Services 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 134, 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 134: 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 following values in the rules file:


In the rules file
Perform the following task
For brief discussions and references to pertinent topics

Select 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 134.

See Setting Field Type Information.

Select fields 2 through 6.

Ignore the fields.

See Ignoring Fields.

Select the Product dimension.

Select the "Add as child of" build method.

See Selecting a Build Method. Type NewProducts in the Add as Child of text box.



Figure 135 shows the tree that Analytic Services builds from this data source and rules file.

Figure 135: 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 three following operations:

You can use any of three approaches to perform these operations:

The following sections describe how to build attribute dimensions:

Building Attribute Dimensions

Before you build any attribute dimensions in a database, you must define the attribute member name formats for the outline. For a comprehensive discussion of assigning attribute member names, see Setting Member Names in Attribute Dimensions.

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

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

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.

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. For a complete example of a multilevel situation, 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 136, 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, Analytic Services associates the attribute 64 with the 500-10 member.

Figure 136: Rules File for Associating Attributes

You can have Analytic Services 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, for the base dimension, clear the Do Not Create Mbrs option. For more information, see "Setting Member Properties" in the 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. For a discussion and example of using separate steps, see Working with Numeric Ranges.

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

Updating Attribute Associations

You can also use the rules file shown in Figure 136 to change attribute associations. Make sure that you allow association changes. In Data Prep Editor, in the Dimension Build Settings tab of the Dimension Build Settings dialog box, check "Allow Association Chgs" for the base dimension. For more information, see "Setting Member Properties" in the Essbase Administration Services Online Help.

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.

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.

Note: For brief discussions of and references to topics pertinent to the following steps, see Setting Field Type Information, Copying Fields, and Moving Fields.

  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 type (level or generation), number, and dimension name.

    Analytic Services uses the 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 138 shows the fields of the rules file at this stage.

    Figure 138: 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 Analytic Services 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 Analytic Services 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 Analytic Services 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 139, 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 139: Source Data and Rules File for Building a Multilevel Attribute Dimension

When you run a dimension build with the data shown in Figure 139, Analytic Services builds the Size attribute dimension and associates its members with the appropriate members of the base dimension. Figure 140 shows the updated outline.

Figure 140: 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 the appropriate members of the base dimension:

The Population attribute dimension shown in Figure 141 demonstrates both situations. Population is a multilevel, numeric attribute dimension with level 0 members representing ranges of different sizes.

Figure 141: 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, be sure to specify the following:

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 141, 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 142.

Figure 142: Rules File for Building a Numeric Attribute Dimension with Ranges

Figure 142 also shows how you can associate aliases with attributes.

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 Analytic Services uses to associate the appropriate Population attribute.

Define the rules file as shown in Figure 143.

Figure 143: Rules File for Associating Numeric Range Attributes

When you define the association field (for example, Population3, Market) be sure to place the attribute members within a range. In Data Prep Editor, in the Field Properties dialog box, on the Dimension Building Properties tab, click the Ranges button. Select "Place attribute members within a range."

Note: Figure 143 includes a city, Boston, whose population of 3,227,707 is outside the ranges of the attribute dimension in Figure 141. (The ranges in Figure 141 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. Analytic Services 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, be aware that 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 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 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.

Assuring the Validity of Associations

To ensure the validity of 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:

Deleting Members from the Attribute Dimension: You can delete all members of an attribute dimension so you can rebuild the dimension with new data. In Data Prep Editor, on the Dimension Building Properties tab in the Field Properties dialog box, click the Ranges button. Select "Delete all members of this attribute dimension." Analytic Services 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, 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 to the base dimension and to associate the new members with their numeric range attributes simultaneously. Be sure to provide a value for the range size. In Data Prep Editor, on the Dimension Building Properties tab in the Field Properties dialog box, click the Ranges button and specify the range size for the attribute dimension.

If Analytic Services 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.

Consider the example, in Figure 141, 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, Analytic Services adds two members to the attribute dimension, 4000000 and 5000000, and associates the base member with the 5000000 attribute.

Figure 144: Dynamically Adding Attribute Range Members

When you add range members and base dimension members at the same time, Analytic Services 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 following list describes a few areas unique to defining and associating attributes through dimension build.

Getting Ready
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.

Controlling Adding New Attribute Members

When Analytic Services 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, do either of the following:

In the Dimension Build Settings dialog box, select the Do Not Create Mbrs option for the attribute dimension. For more information, see "Setting Member Properties" in the Essbase Administration Services Online Help.

Controlling Associations


Association to Control
How to Control the Association

Making changes to attribute associations

In Data Prep Editor, on the Dimension Build Settings tab of the Dimension Build Settings dialog box, select the Allow Association Chgs option for the attribute dimension. For more information, see "Setting Member Properties" in the 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 in the Field Properties dialog box, click the Ranges button and define the size of the range. For a brief discussion of field types and references to pertinent topics, see Setting Field Type Information.

Concurrent attribute associations

Use any build method except the Add as build methods. For information about each build method, see Table 22.



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 a real member with the same name as the shared member. The shared member stores a pointer to data contained in the real member; thus the data is shared between the members and is stored only one time.

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

Figure 145: 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 145, 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 in Figure 153.

Creating shared members at different generations in the outline is easy in Outline Editor. However, creating shared members using dimension build is a little 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. Both parents roll up into the same branch, that is, the Product dimension, and both parents are at generation 2.

Figure 146: 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 these build methods. These methods are 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 file, SHGENREF.TXT, and set up the rules file so that the fields look like SHGENREF.RUL, shown in Figure 147. 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 147: Sample Generation Shared Member Rules File

The data source and rules file illustrated in Figure 147 build the following tree:

Figure 148: 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 make sure 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 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, Analytic Services 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 149.

Figure 149: Sample Level Shared Member Rules File

The data source and rules file illustrated in Figure 149 build the following tree:

Figure 150: 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. Make sure that Analytic Services 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, Analytic Services automatically creates duplicate members under a new parent as shared members.

Figure 151: Sample Parent-Child Shared Members Rules File

The data source and rules file illustrated in Figure 151 build the following tree:

Figure 152: 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. In Figure 153, for example, the shared members roll up into parents at generation 2 and at generation 3. 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 153: Members Shared at Different Generations

To share members across parents at different generations in the outline, use one of these build methods. The methods are described in the following sections:

Using Level References to Create Different Generation Shared Members

To create shared members of different generations by 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, Analytic Services 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 the rules file in Figure 154.

Figure 154: Level References Sample Rules File for Shared Members at Different Generations

The data source and rules file illustrated in Figure 154 build the tree illustrated in Figure 153.

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. Make sure that Analytic Services 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, Analytic Services automatically creates duplicate members under a new parent as shared members.

Figure 155: Parent-Child References Sample Rules File for Shared Members at Different Generations

The data source and rules file illustrated in Figure 155 build the tree illustrated in Figure 153.

Sharing Non-Leaf Members

Sometimes you want to share non-leaf members (members that are not at the lowest generation). In Figure 156 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 156: Non-Leaf Members Shared at Different Generations

To share non-leaf members, use one of these build methods. These methods are described in the following sections:

Using Level References to Create Non-Leaf Shared Members

To create shared non-leaf members by 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 parent of the shared member 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, Analytic Services 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 157. 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 (that is, at generation 2), unless the named level field already exists in the outline.

Figure 157: Level References Sample Rules File for Non-Leaf Shared Members at Different Generations

The data source and rules file illustrated in Figure 157 build the tree illustrated in Figure 156.

Using Parent-Child References to Create Non-Leaf Shared Members

To create shared non-leaf members at the same generation using the parent-child references build method, define the PARENT and CHILD field types. Make sure that Analytic Services 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, Analytic Services automatically creates duplicate members under a new parent as 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.

Figure 158: Parent-Child Sample Rules File for Non-Leaf Shared Members

The data source and rules file illustrated in Figure 158 build the tree illustrated in Figure 156.

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. The rules file, LEVELMUL.RUL, in Figure 159 specifies an example of build instructions for levels in the Product dimension.

Figure 159: 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 160: Scrolled Window

When you run the dimension build using the data in Figure 159, Analytic Services builds the following member tree:

Figure 161: 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 cans and steel cans.

Because 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. For a discussion of 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 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 and then load all other data sources using the parent-child references build method. Make sure that Analytic Services is set up to allow sharing (clear Do Not Share in the Dimension Build Settings tab of the Dimension Build Settings dialog box).

For example, using the Product data source in Figure 162:

Figure 162: Soft Drinks Data Source

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

Analytic Services builds the tree illustrated in Figure 163:

Figure 163: Soft Drinks Tree

Then load the second data source, illustrated in Figure 164, to relate the products to the vendors using the parent-child build method. Make sure that Analytic Services is set up to allow sharing.

Figure 164: Second Shared Roll-Ups Data Source

Vendor   TBC
Vendor   Grandma's 
 

Analytic Services builds the tree illustrated in Figure 165:

Figure 165: Shared Roll-Ups Tree



Hyperion Solutions Corporation link