Creating Hierarchies

This chapter provides overviews of the relationship of PeopleSoft trees to analytic models, Business Analysis Modeler (BAM) total members, dimension members, the calculation of aggregate data, and the persistence of aggregate data, and discusses how to work with overrides.

See Also

PeopleTools 8.52: PeopleSoft Tree Manager

Understanding the Relationship of PeopleSoft Trees to Analytic Models

This section discusses:

• The purpose of PeopleSoft trees and analytic model hierarchies.

• PeopleCode usage with PeopleSoft trees and analytic models.

Purpose of PeopleSoft Trees and Analytic Model Hierarchies

Analytic Calculation Engine uses trees to establish hierarchies of a dimension's parent-child relationships. Analytic Calculation Engine uses these hierarchies to:

• Calculate and display aggregated data to end users.

• Enable end users to navigate through data by performing such actions as expanding and collapsing nodes.

• Enable end users to drill down and drill up through data.

It is important to understand that PeopleSoft trees and hierarchies differ in the following manner: You create one tree for each dimension that requires a hierarchy; the analytic model uses that tree to create one hierarchy for one dimension.

Before loading the analytic model into the analytic server, the application uses the AttachTree method to attach the tree to its corresponding dimension. Next, the analytic model creates its own hierarchy by reading the parent-child relationships that are defined by that tree. During the remainder of the user session, the analytic model uses its own hierarchy, and no longer uses the original tree. For this reason, when the application adds a new dimension member during runtime, the member is actually added to the analytic model's hierarchy; the original tree is not modified.

Note. If a tree is not attached to a dimension, you can create a basic hierarchy for that dimension by specifying a total member name for the dimension.

See Defining Dimension Properties.

You can use the Analytic Model Viewer to view the properties of the trees that you are using with your analytic model.

See Viewing Dimension Properties.

PeopleCode Usage with PeopleSoft Trees and Analytic Models

Use the AttachTree and DetachTree methods to work with PeopleSoft trees and analytic models.

AttachTree Method

Use the AttachTree method to:

• Attach a tree to its corresponding dimension.

  Analytic Calculation Engine attaches the tree to the dimension and then creates and displays the hierarchy.

• Make changes to the tree.

• Create a record that uses PSACETREEOVRD as a subrecord, then attach the new record to the dimension members to associate the member override function with the hierarchy.

Be aware of these restrictions:

• Because the AttachTree method attaches a specific tree to an analytic model, the system throws an error if the tree's name, setID, or effective date is incorrect.

• You can attach only one tree to a dimension.

• If the analytic model is already loaded into an analytic server, the tree is not attached until the next time that the analytic model is reloaded.

DetachTree Method

Use the DetachTree method to detach the tree from the dimension.

Note. If the application loads the analytic model after the tree is detached, the analytic model does not create a hierarchy for the dimension.

If the analytic model is already loaded into an analytic server, the DetachTree method is not applied to the tree until the next time the application loads the analytic model.

Updating a Tree at Runtime

To update a tree at runtime, perform these steps:

1. Unload the analytic model.

   See Unload.

2. Use the DetachTree method to detach the tree from the analytic model.

3. Use the AttachTree method's parameters to update the tree with the changes.

   Note. Be aware of the details start level and tree discard level before making any changes to the tree.

   See Purpose of Node Levels in Creating Hierarchies.

4. Reload the analytic model.

See Also

AttachTree

DetachTree

Understanding BAM Model Total Members

PeopleSoft BAM models often contain total members. A dimension in a BAM model may contain a total member to provide a simple aggregation of the other members of that dimension.

If you want to convert a BAM 8.8 model into an analytic model, you must understand how Analytic Calculation Engine handles the total members from BAM models, and the relationship between BAM total members and the hierarchies and dimension members of analytic models.

See Defining Dimension Properties.

Understanding Dimension Members

This section discusses:

• Types of dimension members.

• Purpose of node levels in creating hierarchies.

• Creation of new members at runtime.

Types of Dimension Members

Analytic Calculation Engine contains many different types of dimension members. The type of member that exists within a dimension is determined by:

• Whether a tree is attached to the dimension.

• The types of leaves or nodes that are mapped to the dimension members.

Note. Two dimension members should never share the same name unless one member is a detail member and one member is an aggregate member.

Detail Members and Leaf Members

If a tree is not attached to a dimension, Analytic Calculation Engine creates detail members for each value of the field to which the dimension is mapped.

If a tree is attached to a dimension, Analytic Calculation Engine creates detail members out of the tree's detail values to establish a dimension's parent and child relationships (in a tree, detail values can serve as children and parents).

See Using Detail Values (Leaves).

Note. When detail values serve as parents, they are also referred to as detail nodes because they do not display aggregated data. Instead, detail nodes usually display the key values of regular transactional tables.

When detail members serve as parents, they do not display aggregated data. Rather, they enable end users to navigate through the hierarchy.

Be aware of these characteristics of detail members' relationship to the main record:

• Detail member names are either read from the main record or generated from the tree's data.

• Navigation related functions such as PREV, NEXT, and PREVSELF operate on detail member names that are persisted in the main record.

  These functions do not use trees to determine the order of members.

  See PREV, NEXT, PREVSELF.

A leaf member is a special type of detail member that does not have children.

For example, suppose an end user enters 20040101 as a new detail value. Analytic Calculation Engine generates a new 20040101 leaf member. This is a leaf member because its corresponding detail value does not have any children.

Aggregate Members

Aggregate members are mapped to the nodes of a tree that have either children or leaf ranges. Aggregate members display a grouping of data, rather than a specific discrete value.

For example, suppose an analytic model's DATE dimension is mapped to a tree that contains 20040101 as a leaf node and Q12004 as a branch node. Analytic Calculation Engine generates the Q12004 aggregate member out of the branch node.

The analytic calculation engine creates aggregate members out of any tree elements that remain after it creates the hierarchy's detail members.

Hierarchy Root Member

You can map the hierarchy root member to any node that you want to serve as the root of the hierarchy. All sibling nodes or nodes at a higher level of the tree are not used to create the hierarchy. You map the hierarchy root member by using the NodeName parameter of the AttachTree method.

Note. Only one hierarchy root member can exist per dimension.

Consider this example of a tree's parent-child relationships:

Example of a tree's parent-child relationships

Even though the highest level node is GBL, which is the root node of the tree, you can select the India node to serve as the hierarchy root member for this dimension. When you create the hierarchy root member out of the India node, only the children of India exist in the hierarchy.

If you have not attached a tree to the dimension, a hierarchy root member still exists for that dimension if you specified a root member name for that dimension. If you have not attached a tree to the dimension and you have not entered a value in the Total Member Name field, neither a hierarchy root member nor a hierarchy exists for that dimension.

See Defining Dimension Properties.

Orphan Members

An orphan member is any member that does not map to a child of a parent node in the tree.

For each orphan member, Analytic Calculation Engine:

• Adds each orphan member to the hierarchy root member.

• Adds each orphan member's value to the hierarchy root value's member.

• Generates a message with the ID of 123 and stores it in the Messages property for the analytic instance.

  You must write PeopleCode to iterate over the messages in the analytic instance and search for message 123, and then take any necessary further action.

  See Error Handling.

Blank Members

A blank member is a member that has no value. Blank members are created out of either an empty detail in a tree or a null cell in the main record. To create blank members:

• When the analytic model learns of a new empty detail in the tree, it adds the blank member to the appropriate parent member.

• When the analytic model learns of a null cell in the main record, it adds the blank member as a child of the hierarchy root.

• When the AddMember method adds a member with a blank member name ( ), a blank member is added as a child of the hierarchy root.

Note. When blank members are mapped to date fields, they are written to the database as values of 1/1/1900.

See Also

Understanding the Elements of Rules

Purpose of Node Levels in Creating Hierarchies

Use node levels to create leaf, detail, and aggregate members out of tree nodes and leaves. Use the parameters of the AttachTree method to set the node levels.

Details Start Level

The details start level determines the type of dimension members that Analytic Calculation Engine creates out of the nodes and leaves of a tree. Use either the parameters of the AttachTree method or the Analytic Instance Load/Unload page to set the details start level. The details start level is a required parameter. The default value is 0. The root level is 1.

See Loading and Unloading Analytic Instances.

Note. If you specify a nonzero details start level, you must specify the strictly enforced method to the tree in PeopleSoft Tree Manager. The strictly enforced method ensures that all members that are created out of one level are created as the same member type.

See Defining Basic Attributes.

This table describes the members that Analytic Calculation Engine creates, depending on whether the details start level is specified:

Tree Discard Level

The tree discard level determines the level from which Analytic Calculation Engine does not attach any more of the tree to the dimension. Use either the parameters of the AttachTree method or the Analytic Instance Load/Unload page to set the tree discard level.

See Loading and Unloading Analytic Instances.

Analytic Calculation Engine does not create members out of nodes or leaves that are either at this level or lower than this level. You must specify a details start level to every tree for which you want to specify a tree discard level. The default value is 0. If the tree discard level is anything other than Level 0, then the tree discard level must be at a lower level than the details start level.

The analytic calculation engine ignores the tree discard level if:

• The details start level is 0.

• The tree discard level is either equal to or higher than the details start level.

Creation of New Members at Runtime

Analytic Calculation Engine can create new dimension members during runtime by using:

• Data from the main record.

• Application data that is added at runtime.

Relationship of Leaf Ranges to New Members

If you map a dimension to a tree that includes leaf ranges, Analytic Calculation Engine adds a new dimension member to the appropriate parent in the hierarchy when the application adds a new leaf that falls within a leaf range of the tree. Use the AddMember method to add new members to the dimension.

Note. Analytic Calculation Engine ignores any new leaves that do not fall within the values of a leaf range.

For example, suppose a tree contains a node called 2003Q1 that includes a leaf range of 2003-01-01 to 2003-03-31. During runtime, Analytic Calculation Engine reads the main record data and recognizes that the application has added 20030204 as a new leaf that exists within the 2003Q1 leaf range. Analytic Calculation Engine creates the 20030204 member and adds it as a child to the 2003Q1 member.

Understanding the Calculation of Aggregate Data

This section discusses:

• Dimension order impact on calculation.

• Logic for determining the order of members.

See Also

Understanding Override Order of Precedence

Dimension Order Impact on Calculation

The order of dimensions in the analytic model determines which member the analytic calculation engine uses to calculate the data cube aggregate value that exists at an intersection of two or more aggregate members. When evaluating the data cube's value at this intersection, the analytic calculation engine uses the aggregate member of the dimension that appears as first in the order of dimensions in the part browser.

See Example: Creating Overrides.

See Also

Changing the Order of Dimensions in the Part Browser

Logic for Determining the Order of Members

User functions often refer to dimension members to calculate data cubes. For this reason, you must understand the factors that affect the way in which the analytic calculation engine orders dimension members:

Note. This section describes the member order as it exists within the analytic calculation engine. This internal order may differ from the member order that is ultimately displayed to the end user.

• If the dimension is mapped to a tree, the analytic calculation engine first determines the member order by the order of the detail values in the tree.

  Next, the analytic calculation engine determines the member order from the order of the values as they exist in the database.

  For example, suppose a dimension is attached to a GBL tree and uses the United Kingdom node as its hierarchy root member. This node contains detail values in this order: Cardiff, Liverpool, London, Manchester. This dimension is also mapped to the UNITED_KINGDOM field in the database, which contains the Edinburgh and Glasgow values. The analytic calculation engine creates this member order in the hierarchy:

  1. Cardiff

  2. Liverpool

  3. London

  4. Manchester

  5. Edinburgh

  6. Glasgow

     Note. The next time the analytic calculation engine creates these members (for example, when the application loads the analytic model), the analytic calculation engine employs the existing member order, even if it recognizes a new database value that matches the name of an existing member.
     
     For example, suppose the analytic model has already established the above hierarchy before the application adds the Manchester value to the UNITED_KINGDOM field. When the application reloads the analytic model, the member order in this hierarchy remains; for this reason, Manchester retains its fourth member position.

• If the dimension is not mapped to a tree, the member order is determined by the order of the members' values as they exist in the field that is mapped to the dimension.

Understanding the Persistence of Aggregate Data

This section discusses:

• Persistence of aggregate and detail data.

• Aggregate record properties.

• Pushed down data.

• Data type considerations.

Persistence of Aggregate and Detail Data

Assuming that you selected a main record and aggregate record in the cube collection, Analytic Calculation Engine uses these records to persist aggregate data and detail data:

• Tree data.

  Tree data includes:

  • Aggregate data:

    Analytic Calculation Engine persists aggregate data in the aggregate record.

    See Understanding the Persistence of Aggregate Data.

    Note. Records that are used as aggregate records should be read after records that are used as main records.

    See Synchronization Order.

  • Pushed down data.

    Analytic Calculation Engine creates detail data out of pushed down aggregate data. For this reason, Analytic Calculation Engine persists this data in the main record.

    See Pushed Down Data.

• Detail data.

  Detail data is data that cannot be broken down any further. Analytic Calculation Engine persists detail data to the main record.

  A detail member is generated out of one value of detail data in the database.

Aggregate Record Properties

On a case-by-case basis, you must determine which aggregates you want to save for each dimension in the cube collection. You can select either ALL, NONE, or ROOT in the Persist Aggregate field of the Edit Cube Collection Dimension dialog box. Here are explanations for these selections:

• ALL: Select to persist all of the dimension member aggregate values to the database.

• NONE: Select to persist none of the dimension member aggregate values to the database.

• ROOT: Select to persist only the value of the hierarchy root member to the database.

See Defining Additional Cube Collection Dimension Properties.

Pushed Down Data

Sometimes a parent member in a hierarchy may contain aggregate data that is not derived by aggregating the parent member's children. When this is the case, you may want to break down the parent member's value to generate the detail data for the parent member's children. You create user functions that employ the PARENT, CHILDCOUNT, and FORCHILDREN built-in functions to push down aggregate data.

These user function examples push down parent member data:

AT (DIMENSION, Parent(DIMENSION), THISCUBE() * 0.2)

And:

AT (DIMENSION, Parent(DIMENSION), THISCUBE() / CHILDCOUNT(DIMENSION, #DIRECT))

See Also

PARENT

CHILDCOUNT

FORCHILDREN

Data Type Considerations

Aggregate members can have different data types than their child detail members, even though they both display data for the same dimension. When this is the case, you must reconcile the situation if you want to persist aggregates.

For example, suppose the PRODUCTS dimension is mapped to a numeric field in the main record and contains members from this tree:

ALL_PRODUCTS
	Release Less than 8
				<Leaf Range (Low = 0, High= 799)
	Release 8
				<Leaf Range (Low = 800, High= 899)

Notice that the Release 8 member is not totally numeric; instead, it is a string that contains letters and a number. If the detail value 846 is added to the tree, the member 846 (which is numeric) is added to the parent member Release 8 (which is a string). To reconcile this situation, you should persist the aggregates for this dimension to a field with a data type of String and a length of at least 20.

Note. It is not necessary for the main record's fields to have the same data types as the aggregate record's fields.

Working with Overrides

This section provides overviews of default aggregation, override order of precedence, and the PSACETREEOVRD subrecord and discusses how to:

• Use default aggregation.

• Create overrides.

• Create a hierarchy with mixed aggregate and detail members.

Understanding Default Aggregation

By default, Analytic Calculation Engine sums all of the values of a parent member's direct children to calculate the value of the parent member. Analytic Calculation Engine executes this default aggregation by iterating over all child members and applying the plus operator. The default aggregation operates on all children, even if the child member itself is an aggregate value.

Note. This default aggregation is not used if you specified a cube dimension override user function, a member override user function, a dimension override user function, or do not have any aggregates in the relevant part of the analytic model.

This is an example of Analytic Calculation Engine's default aggregation:

2003  (170)
     Q1  (80)
        Jan  (10)
        Feb  (20)
        Mar  (50)
     Q2 (90)
        Apr   (20)
        May   (30)
        Jun   (40)

In this example, 2003, Q1, and Q2 are nonleaf members—that is, aggregates. The numbers in parentheses to the right of the aggregate members represent their aggregate values. The numbers in parentheses to the right of the nonaggregate members represent their nonaggregated values.

Understanding Override Order of Precedence

You must understand default aggregation and the order of precedence that the analytic calculation engine uses to override the default aggregation. When calculating aggregate members, Analytic Calculation Engine begins with the most specific override available, and then proceeds to more general overrides. The analytic calculation engine uses this order of precedence to evaluate aggregate members:

1. If the dimension does not contain any aggregate members, use the data cube's formula.

   If the dimension does contain aggregate members, perform one of these actions:

   • If aggregate calculation is not enabled for the data cube, do not perform any more calculation of aggregates.

     Note. The analytic calculation engine generates an error when the analytic model is loaded.

   • If the Calculate Aggregates option is selected for the data cube, perform step 2.

2. Perform one of these actions:

   Note. In either of these cases, the analytic calculation engine loads initial values from the aggregate record when the analytic model is loaded, but overwrites the initial values upon recalculation.

   • Use the cube dimension override user function if it exists.

     This override operates on all of a dimension's aggregate members for the dimension as it is attached to a specific data cube.

     For example, you can create one cube dimension user function to operate on the PRODUCTS dimension when it is attached to the SALES data cube, and another cube dimension user function to operate on the PRODUCTS dimension when it is attached to the COST_OF_GOODS data cube.

     To set a cube dimension override user function, create a user function in the analytic model, and then select the user function in the Aggregate Rule column in the Dimensions tab of the data cube's properties.

   • If the cube dimension user function does not exist, perform step 3.

3. Perform one of these actions:

   • Use the member override user function.

     This override operates on specific members of a dimension. You create the member override user function in the analytic model. However, because members are often instantiated at runtime, you use the PSACETREEOVRD subrecord to assign the member override user function to the dimension rather than assign the member override user function to specific members within the analytic model.

     For example, if the end user enters aggregate data, you can write a member override function that uses the INPUT built-in function to capture the user input, and use the PARENT and CHILDCOUNT built-in functions to push down the aggregate data and create new members. Then you can assign the member override user function to the appropriate dimension in the PSACETREEORRD subrecord.

     Note. If the member override user function does not contain a value, Analytic Calculation Engine applies the default aggregation (the plus operator) to the dimension members.

   • If the member override function does not exist, perform step 4.

4. Perform one of these actions:

   • Use the dimension override function.

     The analytic calculation engine uses this override user function to calculate all of a dimension's aggregates as they apply to all dimensions.

     For example, suppose you create a dimension override function for the PRODUCTS dimension. If this dimension is attached to both the SALES and COST_OF_GOODS dimensions, the dimension override function applies to the aggregates for both data cubes.

     To set a dimension override user function, create a user function and select the user function in the Aggregate Function field of the dimension's properties.

     Note. This override function does not apply to leaf members or detail members.

   • If the dimension override function does not exist, perform step 5.

5. Aggregate the values of the child members by using the operators that are attached to each child.

   The analytic calculation engine iterates over each value to evaluate the aggregate.

   The default operator for each member is the plus operator.

   You set the operators in the OPERATION field of the PSACETREEOVRD subrecord. When the default sum operator is used, the actual value in the OPERATION field is null.

   If you do not want to use the default sum aggregation, you can populate the OPERATION field with one of these values:

   • MIN.

     Use this value for the analytic calculation engine to use subtraction aggregation. The analytic calculation engine iterates over each member and applies the subtraction operator to each iteration. The aggregate value is the resulting value after the final iteration.

   • IGN.

     Use this value for the analytic calculation engine to ignore the OPERATION field.

See Also

Example: Using Default Aggregation

Example: Creating a Hierarchy with Mixed Aggregate and Detail Members

Understanding the PSACETREEOVRD Subrecord

You use the PSACETREEOVRD subrecord to assign an override to a tree. To use the subrecord, you must first create a record definition. This record definition can contain additional fields. After you create the record definition, insert the PSACETREEOVRD subrecord into the record definition.

To attach the PSACETREEOVRD subrecord to a tree, specify the name of the override record as the RecordName parameter of the AttachTree method.

This table describes the PSACETREEOVRD subrecord:

Note. The PSACETREEOVRD subrecord must contain a value in either or both of the OPERATION or ACERULEID fields. If both of these fields are null, the analytic calculation engine ignores the row.

Example: Using Default Aggregation

This section provides an example of how to create an analytic model that uses default aggregation.

Requirements for Analytic Model

This table provides an example of a table named MainData, which you specified as the main record of a cube collection:

You want the analytic model to:

• Calculate aggregates for the NUM_SALES field.

• Save the aggregates for the NUM_SALES field.

• Establish hierarchies for the DEPT and SOMEDATE dimensions.

• Refrain from calculating aggregates for the PRICE_PER_UNIT field.

• Save all aggregates for the DEPT dimension.

• Refrain from saving aggregates for the SOMEDATE dimension.

Creating the Aggregates

To fulfill the requirements of the analytic model, perform these steps:

1. Create these dimensions:

   • DEPARTMENT

   • DATE

2. Create these data cubes:

   • SALES

   • PRICE_PER_UNIT

3. Enable the Calculate Aggregates field for the SALES data cube, because you want to calculate aggregates for this data cube.

4. Ensure that the Calculate Aggregates field is disabled for the PRICE_PER_UNIT data cube, because you do not want to calculate aggregates for this data cube.

5. Create a cube collection called SALES.

6. Select MainData as the main record for the SALES cube collection.

7. Map the data cubes and dimensions within the SALES cube collection to fields in the main record.

   This table provides the mappings:

   Data Cube or Dimension to Map	Field in Main Record
   DEPARTMENT dimension	DEPARTMENT field Data type: Number
   SOMEDATE dimension	SOMEDATE field Data type: Date
   SALES data cube	NUM_SALES field Data type: Number
   PRICE_PER_UNIT data cube	PRICE_PER_UNIT field Data type: Number

8. Select the AGGRDATE record as the aggregate record.

   The AGGRDATE database record currently contains no data. This table describes the fields within the record:

   Field Name	Data Type
   DEPARTMENT	String
   TREE_DATE	String
   NUM_SALES	Number

   Notice that even though the DEPARTMENT and TREE_DATE fields are of the String data type, none of the main record's fields are of this same data type. The DEPARTMENT and TREE_DATE fields are of the String data type because the hierarchy's members display strings, not dates or numbers. The data types of the aggregate record's fields must match the data types and lengths of the hierarchy's aggregate members. However, the data types of the aggregate record's fields do not need to match the data types of the main record's fields.

   Note. If you design a tree's nodes so that the fields of the nodes are of the same data type as the fields of the detail members, you can use the same data type for both the dimension's aggregate record fields and main record fields.

9. Map dimensions and data cubes to fields in the aggregate record.

   This table provides the mappings:

   Data Cube or Dimension to Map	Field in Aggregate Record
   DEPARTMENT dimension	DEPARTMENT field
   SOMEDATE dimension	TREE_DATE field
   SALES data cube	NUM_SALES field

10. Use PeopleSoft Tree manager to create two trees:

    Note. In the two tables, italicized children represent detail values. Remember, Analytic Calculation Engine creates detail members out of the tree's detail values to establish a dimension's parent-child relationships (in a tree, detail values can serve as children and parents).

    • DEPT_TREE

      Parents	Children
      (no parent root)	GBL
      GBL	US
      GBL	LAT AM
      US	101
      US	102
      US	103
      LAT AM	201
      LAT AM	202

    • DATE_TREE

      Parents	Children
      (no parent root)	CAL2004
      CAL2004	JAN
      CAL2004	FEB
      JAN	20040101
      JAN	20040102
      FEB	20040201

11. Consider these scenarios for default sum aggregation:

    • If you select to persist all aggregates of both dimensions on the Dimensions tab of the cube collection's properties, the following rows are persisted in the aggregate record:

      Note. Italicized values are the actual persisted aggregate members. Zero (0) values in this table are not persisted. The Fully Qualified Member Name field is not a database value.

      Fully Qualified Member Name	DEPARTMENT	TREE_DATE	NUM_SALES
      GBL CAL2004.JAN. 20040101	GBL	20040101	13
      GBL CAL2004.JAN.20040102	GBL	20040102	2
      GBL CAL2004.FEB.20040201	GBL	20040201	16
      GBL.US CAL2004.JAN.20040101	US	20040101	5
      GBL.US CAL2004.JAN.20040102	US	20040102	2
      GBL.US CAL2004.FEB.20040201	US	20040201	0
      GBL.LAT AM CAL2004.JAN.20040101	LAT AM	20040101	8
      GBL.LAT AM CAL2004.JAN.20040102	LAT AM	20040102	0
      GBL.LAT AM CAL2004.FEB.20040201	LAT AM	20040201	16
      GBL.US.101 CAL2004	101	CAL2004	1
      GBL.US.102 CAL2004	102	CAL2004	2
      GBL.US.103 CAL2004	103	CAL2004	4
      GBL LAT AM.201 CAL2004	201	CAL2004	8
      GBL LAT AM.202 CAL2004	202	CAL2004	16
      GBL.US.101 CAL2004.JAN	101	JAN	1
      GBL.US.102 CAL2004.JAN	102	JAN	2
      GBL.US.103 CAL2004.JAN	103	JAN	4
      GBL.LAT AM.201 CAL2004.JAN	201	JAN	8
      GBL.LAT AM.202 CAL2004.JAN	202	JAN	0
      GBL.US.101 CAL2004.FEB	101	FEB	0
      GBL.US.102 CAL2004.FEB	102	FEB	0
      GBL.US.103 CAL2004.FEB	103	FEB	0
      GBL. LAT AM.202 CAL2004.FEB	201	FEB	0
      GBL. LAT AM.203 CAL2004.FEB	202	FEB	16
      GBL CAL2004	GBL	CAL2004	31
      GBL CAL2004.JAN	GBL	JAN	15
      GBL CAL2004.FEB	GBL	FEB	16
      GBL.US CAL2004	US	CAL2004	7
      GBL.US CAL2004. JAN	US	JAN	7
      GBL.US CAL2004.FEB	US	FEB	0
      GBL.LAT AM CAL2004	LAT AM	CAL2004	24
      GBL.LAT AM CAL2004.JAN	LAT AM	JAN	8
      GBL. LAT AM CAL2004.FEB	LAT AM	FEB	16

    • If you select to persist all aggregates of the DEPT_TREE dimension and to persist none of the aggregates of the DATE_TREE dimension on the Dimensions tab of the cube collection's properties, the following rows are persisted in the aggregate record:

      Note. Italicized values are the actual persisted aggregate members. Zero (0) values in this table not persisted. The Fully Qualified Member Name field is not a database value.

      Fully Qualified Member Name	DEPARTMENT	TREE_DATE	NUM_SALES
      GBL CAL2004.JAN. 20040101	GBL	20040101	13
      GBL CAL2004.JAN.20040102	GBL	20040102	2
      GBL CAL2004.FEB.20040201	GBL	20040201	16
      GBL.US CAL2004.JAN.20040101	US	20040101	5
      GBL.US CAL2004.JAN.20040102	US	20040102	2
      GBL.US CAL2004.FEB.20040201	US	20040201	0
      GBL.LAT AM CAL2004.JAN.20040102	LAT AM	20040102	8
      GBL.LAT AM CAL2004.JAN.20040102	LAT AM	20040102	0
      GBL.LAT AM 20040201	LAT AM	20040201	16

    • If you select to persist only the root aggregations of the DEPT_TREE dimension and to persist none of the aggregates of the DATE_TREE dimension on the Dimensions tab of the cube collection's properties, the following rows are persisted in the aggregate record:

      Note. Italicized values are the actual persisted aggregate members. The Fully Qualified Member Name field is not a database value.

      Fully Qualified Member Name	DEPARTMENT	TREE_DATE	NUM_SALES
      GBL CAL2004.JAN. 20040101	GBL	20040101	13
      GBL 20040102	GBL	20040102	2
      GBL 20040201	GBL	20040201	16

Example: Creating Overrides

This section provides an example of creating overrides and discusses the affect of dimension order on calculation.

The following table describes the three dimensions used in this example. The first column lists the names of the dimensions. The second column lists the dimension order, which determines calculation priority. The third column lists the dimension override functions that are used if member override functions do not exist for the children of the parents in the dimension:

The following table describes the hierarchy of the ACCT dimension that is associated with the AcctTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child:

Note. Overrides are not performed on cells denoted (leaf) or <none>.

The following table describes the hierarchy of the TRANS_DATE dimension that is associated with the QrtrlyTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child:

Note. Overrides are not performed on cells denoted (leaf) or <none>.

The following table describes the hierarchy of the DEPT dimension that is created from the DeptTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child:

Note. Overrides are not performed on cells denoted (leaf) or <none>.

This example uses the <RED_HERRING> child node override as incorrect data. Leaf nodes do not have aggregations.

Assume that a SALES data cube exists in the cube collection, and the three dimensions of this example are attached to this data cube.

Read the instructions carefully before analyzing the following table; the table describes two methods that the analytic calculation engine can use to calculate hierarchies.

• When you analyze only the first four columns of the table (ignore the fifth column), the basic analytic model does not contain any cube dimension overrides.

  The first column displays the row numbers. The second, third, and fourth columns list the members of each of the three dimensions.

• When you analyze all five rows of the table, the basic analytic model does contain cube dimension overrides.

  The first column displays the row numbers. The second, third, and fourth columns list the members of each of the three dimensions. The fifth column—where it applies—lists the override that the analytic calculation engine uses to calculate the row.

  For example, if the developer applies the SALES_CUBE_OVERRIDE cube dimension override to the TRANS_DATE dimension as it is attached to the SALES data cube, the Cube Dimension Override User Function column indicates where the override is applied.

Italicized values in the table indicate aggregate members. A row may contain more than one aggregate member. If a row contains one or more aggregate members, the table denotes the winning aggregate member along with the method that the analytic calculation engine uses to calculate the member. If a row does not contain any aggregate members, the analytic calculation engine uses the data cube's rule to calculate values.

The analytic calculation engine used this logic to determine which cell of the row it finally used to calculate the aggregate:

• Aggregation for row 25:

  The analytic calculation engine used the 120 value from the TRANS_DATE dimension because this dimension was the only dimension that contained an aggregate member. Understand that if either of the two other dimensions contained an aggregate member, the analytic calculation engine would still select the 120 value because the TRANS_DATE dimension is first in priority. The analytic calculation engine used the <ACCT_DIM_DEFAULT_FORMULA> dimension override because neither a cube dimension user function nor a member override user function existed for this member.

• Aggregation for row 37:

  The analytic calculation engine used the Q1 value from the TRANS_DATE dimension because this was the only dimension that contained an aggregate member. The analytic calculation engine used the default sum aggregation because neither a member override user function existed for this member nor a dimension override user function existed for this dimension.

• Aggregation for row 41:

  The analytic calculation engine used the USA value from the DEPT dimension because this was the only dimension that contained an aggregate member. The analytic calculation engine used <DEPT_DIM_DEFAULT_FORMULA> because neither a cube dimension user function nor a member override user function existed for this member.

Example: Creating a Hierarchy with Mixed Aggregate and Detail Members

In this example, the analytic model contains a BONUS cube collection that calculates the bonus for a group of employees. The BONUS cube collection uses the main record, as described in this table:

This example uses the following tree, named BUS1:

VP - Vice president
   D1 - Director 1
      M1 - Manager 1
   D2 - Director 2
      M2 - Manger 2
      M3 - Manager 3

The hierarchical relationships in the BUS1 tree are:

• D1 and D2 are directors who report to VP.

• M1 is a manager who reports to D1.

• M2 and M3 are managers who report to D2.

The BONUS cube collection contains a data cube called EMPLOYEE_BONUS, to which the EMPLOYEE dimension is attached.

You do not want to calculate the vice president's bonus by summing the bonuses of all of the vice president's children. The VP node should not exist as an aggregate member of the hierarchy; it should exist instead as a detail member. For this reason, do not calculate aggregates for the EMPLOYEE_BONUS data cube.

To create the correct members to the nodes of this tree, specify the details start level as level two (because the root level is level one). With this specification, detail members are created out of every tree node at the VP level and any level lower than the VP level. End users use the hierarchy for navigating throughout the tree. Remember that you can create hierarchies that are only used for navigation.

Make this data available to the end user:

• The bonus for every employee.

• The total bonuses for each employee, plus the total bonuses for each employee who reports to him or her.

To achieve these results, perform these steps:

1. Create a data cube called TOTAL_BONUS.

2. Attach the EMPLOYEE_BONUS dimension to the TOTAL_BONUS data cube.

3. Add the TOTAL_BONUS data cube to the BONUS cube collection.

In this example's hierarchy, all the members are detail members except for the hierarchy root member. Because aggregate user functions are only used to calculate aggregate members, you should create a regular formula for the TOTAL_BONUS data cube to calculate its aggregates. Use the FORCHILDREN built-in function to sum the value of the EMPLOYEE_BONUS data cube plus all of the children of the member. For example:

FORCHILDREN(DIMENSION, MEMBER, EXPRESSION)

Because you specified level two as the details start level, the root member is calculated as an aggregate. The analytic calculation engine calculates both the TOTAL_BONUS and EMPLOYEE_BONUS data cubes by using the sum default, because you did not create and select an aggregate function for this purpose.

This table displays the calculation results of all members that are attached to the EMPLOYEE_BONUS data cube: