Understanding the Persistence of Aggregate 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.

    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.

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.

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))

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.