Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter discusses dimension building.
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:
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.
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
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:
Figure 117: Missing Field in a Generation References Data Source
GEN2,Products GEN3,Products GEN4,Prod
ucts
100 100-10a
Figure 118: Missing Secondary Field in a Generation References Data Source
GEN2,Products ALIAS2,Products GEN3,Products GEN4,Products Cola 100-10 100-10a
Figure 119: Missing Secondary Field in a Generation References Data Source
GEN2,Products ALIAS2,Products GEN3,Products GEN4,Products 100 100-10 100-10a
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.
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:
Figure 124: Missing Field in a Level References Data Source
LEVEL0,Products LEVEL1,Products LEVEL2,Products 100-10 100
Figure 125: Missing Secondary Field in a Level References Data Source
LEVEL0,Products ALIAS0,Products LEVEL1,Products LEVEL2,Products Cola 100-10 100
Figure 126: Missing Secondary Field in a Level References Data Source
LEVEL0,Products ALIAS0,Products LEVEL1,Products LEVEL2,Products 100-10a 100-10 100
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
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.
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 |
---|---|---|
|
||
See Ignoring Fields. |
||
Select the "Add as sibling of matching string" 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
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 |
---|---|---|
|
||
See Ignoring Fields. |
||
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
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 |
---|---|---|
|
||
See Ignoring Fields. |
||
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
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:
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.
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
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.
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.
Analytic Services uses the field1 and field2 to build the attribute 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
In the current example, make a copy of field 1.
In the current example, move the new field to the right of the field Level0, Product.
In the current example, make a copy of field 2.
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
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.
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.
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.
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.
The following list describes a few areas unique to defining and associating attributes through dimension build.
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.
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.
Association to Control |
How to Control the Association |
---|---|
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. |
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.
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.
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:
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
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
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
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:
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.
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.
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:
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.
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.
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:
When you run the dimension build using the data in Figure 159, Analytic Services builds the following member tree:
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.
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:
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
![]() |