Performing Custom Calculations and Allocations on Aggregate Storage Databases

In This Section:

Performing Custom Calculations and Allocations on Aggregate Storage Databases

Custom Calculations on Aggregate Storage Databases

Allocations on Aggregate Storage Databases

Understanding Data Load Buffers for Custom Calculations and Allocations

Understanding Offset Handling for Custom Calculations and Allocations

Understanding Credit and Debit Processing for Custom Calculations and Allocations

The information in this chapter applies only to aggregate storage databases.

For information about performing custom calculations on block storage databases, see:

For information about performing allocations on block storage databases, see:

For more information about APIs and configuration settings discussed in this chapter, see the Oracle Essbase API Reference and the Oracle Essbase Technical Reference, respectively.

Performing Custom Calculations and Allocations on Aggregate Storage Databases

Essbase provides separate API functions and structures for performing custom calculations (see Custom Calculations on Aggregate Storage Databases) and allocations (see Allocations on Aggregate Storage Databases) on aggregate storage databases. However, both features share the following common functionality:

Custom Calculations on Aggregate Storage Databases

Custom calculations extend the analytical capabilities of Essbase by enabling the execution of recurring calculations on aggregate storage databases.

You can write custom calculations for aggregate storage databases that update target level 0 cells. Custom calculation scripts are expressed in MDX.

Using custom calculations, you can do basic math on account balances in a general ledger and write the results to targeted level 0 members of an Essbase aggregate storage database. You can perform calculations on account balances or on fixed amounts and can be scheduled to repeat every accounting period.

Custom calculations on aggregate storage databases can be useful when the database is used for general ledger reporting, where double-entry accounting is in effect. Debit items, such as assets and expenses, must balance with credit items, such as equity and revenue.

Use the following workflow to create and execute custom calculations:

  • Create a calculation script expressed in MDX.

    See Writing Custom Calculations.

  • Select an area of the database where the calculation will be executed. You provide the area at execution time using the target and POV (point of view) parameters.

  • If you use debit and credit processing, select the debit and credit members in the outline to write the positive and negative values. You provide these parameters at execution time.

    See Understanding Credit and Debit Processing for Custom Calculations and Allocations.

  • If you use offsetting entries, select the area where offsetting entries should be made. You provide this parameter at execution time using an MDX tuple. If an offset is not specified or is empty, the offset calculation is not performed.

    Note:

    In general ledger bookkeeping, an offsetting entry is a counterbalancing measure on the opposite side of the ledger; for example, a $100 credit in January may have a $100 offset added to the debit side of the ledger, so the ledger can be balanced in preparation for an upcoming expense of that amount.

    See Understanding Offset Handling for Custom Calculations and Allocations.

  • Execute the custom calculation script using the Essbase API.

    See Executing Custom Calculations.

List of Custom Calculations Criteria

Custom calculation functionality depends on a variety of specified criteria. Table 209 provides a brief description of the criteria used to define custom calculations. Review these terms before continuing.

Table 209. Description of Custom Calculations Criteria

Criteria

Description

POV

A symmetric region in the database that describes the context in which custom calculations are performed.

Attribute members cannot be used for this argument.

Calculation script

A calculation script expressed in MDX.

Attribute members cannot be used in the left side of the equation.

Target

A tuple argument expressed in MDX that defines the region in the database where calculation results are written. This argument is combined with left side of each formula and the offset to determine where the results and offset values are written.

Attribute members cannot be used for this argument.

(Optional) Offset

The location in the database where an offsetting value for each source amount is written.

Attribute members cannot be used for this argument.

(Optional) Credit and debit members

In double-entry accounting, balancing journal entries for one transaction. Both are MDX member expressions. The debit member indicates a member to which positive result values are written, and the credit member indicates a member to which negative result values are written.

Attribute members cannot be used for this argument.

Source region

An MDX set expression specifying the region of the cube referred to by the formulas in the script.

Writing Custom Calculations

A custom calculation script is a file that you create and store with a .csc extension. Create the custom calculation script with one or a series of tuple-expression pairs in MDX, terminated by semicolons. The syntax:

tuple := numeric_value_expression;

The tuple is an MDX specification of one or more members where no two members can be from the same dimension. The tuple must be on the left side of the equation and is the primary factor in determining where results of the custom calculation are written.

Only member names are allowed in the tuple expression. The use of member functions is not supported for custom calculation scripts.

Note:

The secondary factor determining the target for results is the target parameter, and the third factor is the POV parameter. You specify the second and third parameters at calculation execution time, rather than as part of the calculation script.

The numeric_value_expression is a simple MDX numeric value expression, such as a number or an arithmetic operation. The expression must be on the right side of the equation. Only arithmetic operators are permitted. An error is returned if non arithmetic operators (such as AND, OR, or IF statements) are used.

Member names can be used in the numeric value expression, but the use of member functions is not supported for custom calculation scripts.

Attribute members cannot be used on the left side of the equation in a custom calculation script.

You must also define the source region, which serves as a performance hint for Essbase. Essbase pre-fetches the data specified in the source region, and uses that to perform the calculation specified in the script.

For an example of a custom calculation script, and more information about defining the source region, see Sample Use Case for Custom Calculations.

Executing Custom Calculations

You can execute custom calculations using the MaxL execute calculation statement (aggregate storage version).

You can execute custom calculation scripts using the Essbase API. Use EssPerformCustomCalcASO to perform or verify a custom calculation on an aggregate storage database. Provide the information for the custom calculation in the ESS_PERF_CUSTCALC_T structure.

You can also use Oracle Hyperion Calculation Manager to design a custom calculation and deploy it for execution to Enterprise Scheduling Services.

Sample Use Case for Custom Calculations

Consider an outline with the following dimensions:

  • Company, containing CompanyA, CompanyB, and other children.

  • Department, containing numbered departments such as 101, 102, 103.

  • Account, in which Account 5740 is a rent expense account and SQFT is a statistical account used to record square footage for each department.

  • Scenario, in which the Actual member is where data is posted, and the Allocation member is where allocations and custom calculations are stored. The Scenario member is a parent that aggregates the child members Actual and Allocation.

  • Year, a time dimension organized by months and quarters.

  • Geography, a dimension organized by states and cities.

  • AmountType, in which Debit is the target, and Credit is the offset.

  • Project, a dimension containing projects such as Proj1, Proj2.

The POV is an MDX set expression indicating where the custom calculation should be executed. It is specified as follows:

CrossJoin( { ( [Company], [101], [Jan], [Scenario] ) },
           Descendants( Geography, Geography.Levels(0)) )

The DebitMember is an MDX member expression indicating a debit member to which positive result values should be written. It is specified as [BeginningBalance_Debit].

The CreditMember is an MDX member expression indicating a credit member to which negative and offsetting result values should be written. It is specified as [BeginningBalance_Credit].

Note:

The offset is written to the debit member in the case that the sum of all result values is negative.

The offset is an MDX tuple expression indicating where offsetting entries should be made. It is specified as ([Account_NA], [Project_NA]).

The offset expression is combined with Target and POV to determine the location where offsetting entries are made. If dimensions overlap, the order for resolving the offset location is the offset, the target, and the POV, in that order.

The target is an MDX tuple expression indicating where to write the results of the custom calculation. It is specified as(Allocation).

The target expression is combined with POV, and the left side of each line in the custom calculation script, to determine the location where results are written. If dimensions overlap, the order for resolving the target location is the left side of the equations, the target, and the POV, in that order. In this example, results are written to the Allocation member, because the target overrides the Scenario member specified in the POV.

The following is an example of a custom calculation script:

(AccountA,Proj1) := 100;
([AccountB], [Proj1]) := ([AccountB], [Proj1]) * 1.1;
(AccountC,Proj1) := 
	((AccountB,Proj1,2007) + (AccountB, Proj1)) / 2;
(AccountA,Proj2) := 
	((AccountD,Proj1) + 
	  (AccountB,Proj2)) / 2;

For each combination in the POV,

  • The calculation script is executed in the context of the current POV combination.

  • One offset value is written to the target location.

Note:

Each formula (line in the calculation script) is executed simultaneously, rather than sequentially. Therefore, you cannot use the result of one formula in a subsequent formula.

To define the source region, examine the custom calculation script and determine which members are referenced on the right sides of equations. At a minimum, the source region should include all members from the right sides of the assignment statements in the custom calculation script.

Define the source region as a single MDX set. If the members on the right sides of the equations are from more than one dimension, you can use CrossJoin to create the set from two sets. CrossJoin only accepts two sets, so you may have to use nested CrossJoins.

The source region for the above custom calculation script is:

Crossjoin(
 {[AccountB], [AccountD]}, 
   Crossjoin(
    {[Proj1], [Proj2]}, {[2007]}
   )
)

It is not necessary to include any members in the source region that are not assigned in the script. For example, if you added to the source region an [AccountC], which is not used in the script, then it would be ignored, and could cause a slight detriment to performance.

It is not necessary to account for numbers in the source region. For example, the following assignment in a custom calculation script requires nothing to be added to source region: ([Bud Var]):=10.

Allocations on Aggregate Storage Databases

Allocations are used in the budgeting process to distribute revenues or costs.

The allocations feature allows you to allocate a given source amount to a target range of cells in an aggregate storage database. The source amount can be allocated to the target proportionately, based on a given basis, or the source amount can be spread evenly to the target.

You can perform aggregate storage allocations using the MaxL execute allocation statement.

You can also perform aggregate storage allocations by using the EssPerformAllocationAso API function and providing the information about the allocation in the ESS_PERF_ALLOC_T API structure.

A single allocation, which corresponds to a single call to the EssPerformAllocationAso API, has its own POV (point of view), range, amount, basis, target, and, optionally, offset. To perform allocations with different sets of values for these parameters, you must make a sequence of individual API calls.

Allocations are performed in the allocation engine and then written back to the aggregate storage database using temporary data load buffers that Essbase creates. For general information about data load buffers, see Loading Data into Aggregate Storage Databases. For information specific to allocations and custom calculations, see Understanding Data Load Buffers for Custom Calculations and Allocations.

List of Allocation Criteria

Allocations are based on a variety of specified criteria. Table 210 provides a brief description of the criteria used to define allocations. Review these terms before continuing.

Table 210. Description of Allocation Criteria

Criteria

Description

See

POV

A symmetric region in the database that describes the context in which allocations are performed

Setting the POV

Range

A symmetric region in the database in which allocated values are calculated and written

Setting the Range

(Optional) Excluded range

Locations in the range where you do not want allocation values written

Setting the Range

Amount

The amount to be allocated

Setting the Amount

(Optional) Amount context

Additional context, or specificity, for the amount

Setting the Amount

(Optional) Amount time span

One or more time periods to be considered for the amount

Setting the Amount

(Optional) Zero amount options

Treatment of zero or #MISSING amount values

Setting the Amount

Basis

When combined with the range, defines the location of basis values that determine how the amount is allocated

Setting the Basis

(Optional) Basis time span

One or more time periods to be considered for the basis

Setting the Basis

(Required if basis time span is set)

Basis time span option

Method for calculating the basis across the basis time span:

  • Combine

  • Split

Setting the Basis

Zero basis options

Treatment of zero basis values

Setting the Basis

(Optional) Negative basis options

Treatment of negative basis values

Setting the Basis

Target

When combined with the range, defines the region in the database where allocation values are written

Setting the Target

(Optional) Target time span

One or more time periods to be considered for the target

Setting the Target

(Required if target time span is set)

Target time span option

Method for allocating values across the target time span:

  • Divide the amount

  • Repeat the amount

Setting the Target

Allocation method

Method for allocating the amount:

  • Share: Allocates the amount proportionately to the basis values

  • Spread: Allocates the amount evenly

Setting the Allocation Method

(Optional) Spread skip options

For spread allocation method, whether to skip basis values in the range that are zero, #MISSING, or negative

Setting the Allocation Method

Rounding method

Whether to round allocated values.

If you choose to round, specifies the method for handling rounding errors:

  • Discard rounding errors

  • Add the total rounding error to:

    • The greatest allocated value

    • The lowest allocated value

    • A specific location

Setting the Rounding Method

(Optional) Round digits

The number of decimal places to which allocation values are rounded:

  • To the nearest integer

  • To a specified number of decimal places

  • To a power of 10

Setting the Rounding Method

(Required if rounding method is set to a specific location)

Round to location

The location to which to add the total rounding error

Setting the Rounding Method

(Optional) Offset

The location in the database where an offsetting value for each source amount is written

Setting the Offset

(Optional) Credit and debit members

In double-entry accounting, balancing journal entries for one transaction

Balancing Allocations

Understanding Regions

Essbase uses various regions in the database when performing allocations. Each region consists of at least one member from each dimension defined in the region.

Table 211. List of Regions Used in Allocations

Region Name

Region Definition

Description

Source

(POV X amount [X amount context] X [amount time span])

The region containing the amount values that are to be allocated.

The source region and target region cannot overlap.

Target

(POV X target X debit member/credit member X range X [target time span])

The region containing the locations to which allocated values are written.

The source region and target region cannot overlap.

The target region need not need be empty before performing an allocation. Essbase overwrites non-empty cells either with allocation data or with zeros. For cells with #MISSING, the cells remain #MISSING unless Essbase writes allocation data to those cells.

Basis

(POV X basis X range X [basis time span])

The region containing the basis values that are used to determine how the source amount is allocated.

Basis might override part of the POV.

Offset

(POV X offset X debitMember/creditMember)

The region containing the locations to which offset values are written.

Specifying Allocation Criteria

Allocation parameter values can be expressed in the following ways:

  • MDX member expression

  • MDX set expression

  • MDX tuple expression (where no two members can be from the same dimension)

  • Constant

For more information about how to express allocation parameter values, see the Oracle Essbase API Reference.

Using Shared Members

When shared members are specified in allocation parameters, Essbase maps the shared members to their original members before performing the allocation.

Using Duplicate Members

When duplicate members are specified in allocation parameters, either because a member name is repeated or because a member and its shared member are both specified, Essbase removes the duplicate members and issues a warning.

Setting the POV

POV specifies a symmetric region in the database that describes the context in which allocations are performed. The POV can consist of only level 0 members. The dimensions defined in the POV cannot be used in other parameters, except for the basis and the basis time span.

The allocation is repeated for every combination of members in the POV set. The number of POV combinations is the product of the number of members from dimensions with multiple members. (Dimensions with only one member are not used to calculate the number of combinations.)

For example, assume that the POV consists of two dimensions (CostCenter and Project), and allocations are to be made to two cost centers (CostCenter1 and CostCenter2) and three projects (Project1, Project2, and Project3). The number of POV combinations is six:

Project1,CostCenter1
Project1,CostCenter2
Project2,CostCenter1
Project2,CostCenter2
Project3,CostCenter1
Project3,CostCenter2

The allocation is repeated six times by successively setting the allocation context to each combination.

Values considered as the basis for the allocation are dependent on the POV combination. See Setting the Basis.

Note:

If time periods are specified in the POV, you cannot use the amount time span and the target time span options. See Setting the Amount and Setting the Target, respectively.

Setting the Range

Range specifies a symmetric region in the database in which allocated values are calculated and written.

If you do not want allocation values written to certain cells within the range, use the excluded range parameter to express a symmetrical subset of the range. Even when excluding a subset of the range, Essbase uses all cells in the range to calculate allocated values.

When you exclude cells from the range, the sum of allocated values might be less than the value of the amount.

The following examples assume that the range consists of six member combinations, the amount is 6, and the allocation method is spread, in which Essbase evenly allocates the amount across the range. The allocation spread amount is 1 (6/6 = 1).

As illustrated in Table 212, Essbase writes 1 in each cell in the range.

Table 212. Example: Allocating the Amount to Each Member in the Range

 CostCtr1CostCtr2
Project111
Project211
Project311

As illustrated in Table 213, if the excluded range is set to the member combination of (Project2,CostCtr2), Essbase does not write the allocation spread amount to that cell. Therefore, the sum of allocated values (5) is less than the amount (6). The value of the excluded cell after the allocation process is either #MISSING or zero.

Table 213. Example: Allocating the Amount to Only Some Members in the Range

 CostCtr1CostCtr2
Project111
Project21 
Project311

The range and excluded range can consist of only level 0 members.

Setting the Amount

Amount specifies the source of the allocation. The amount value is allocated to cells in the target region. The amount, which can consist of upper-level or level 0 members, can be expressed as a numeric value expression, a tuple, or a constant.

How you express the amount determines certain requirements:

  • Numeric value expression:

    • All members in the expression must be from the same dimension.

    • Tuples cannot be used in the expression.

    • Only arithmetic expressions (+, -, /, and *) can be used in the expression.

    • MDX functions (such as Avg and Parent) are not allowed.

    For example:

    (Acc_1000 + Acc_2000)/2
    AccA + AcctB
    Balance * 1.1
  • Tuple:

    • The tuple must use one member from every dimension that is not specified in the POV.

    • The amount context must be empty.

    For example:

    (Balance,Cost_Center_00,Project_00)
    (Balance,Cost_Center_00,Actual)
  • Constant:

    • The amount context must be empty.

    • The amount time span must be empty.

    For example:

    100

You can use these parameters to further define the amount:

  • (Optional) Amount context provides additional context, or specificity, for the amount. The amount context, which can consist of upper-level or level 0 members, can be expressed as a tuple. By specifying the amount context, you can include a member from a dimension that is not specified in the POV.

    When using amount context, these requirements apply to the amount and the amount context:

    • The parameters cannot refer to members in the same dimensions.

    • Together, the parameters must use members from every dimension not specified in the POV.

  • (Optional) Amount time span specifies one or more time periods to be considered for the amount. The amount value is aggregated over the specified time periods, and the aggregated amount value is allocated. Time periods must be level 0 members in a Time dimension.

    When amount is specified using an arithmetic expression, and amount time span is used, amount time span takes precedence over any formulas in the amount or any formula members used in the amount. For example, assume that the amount is specified as Dept_A/Dept_B and amount time span is set to Jan, Feb, Mar, and Apr for each department, as shown in Table 214. The amount to be allocated for the POV is calculated by dividing the amount time span value for Dept_A (10) by the amount time span value for Dept_B (20), which is 0.5.

    Table 214. Example: Amount Time Span Takes Precedence Over Formulas in Amount

    Members in Amount Time SpanDept_ADept_B
    Jan12
    Feb24
    Mar36
    Apr48
    Total1020
  • (Optional) Zero amount options specifies how to treat the amount if the value is zero or #MISSING. You can choose to allocate zero values (the default), skip to the next nonzero or non-#MISSING amount value, or cancel the entire allocation operation.

You can use amount context and amount time span to achieve the same result, as shown in following example. The amount is the value of Dept_A, but amount time span is used to focus only on the months of Jan, Feb, Mar, and Apr for Dept_A. As shown in Table 215, the aggregated value of the members included in the amount time span (10) is the amount value that is allocated across the cells in the range.

Table 215. Example: Amount Time Span

Members in Amount Time SpanDept_A
Jan1
Feb2
Mar3
Apr4
Total10

You can achieve the same amount value by specifying amount as an arithmetic expression of Jan + Feb + Mar + Apr and setting the amount context as Dept_A, as shown in Table 216:

Table 216. Example: Amount Context

Amount ContextJanFebMarAprTotal
Dept_A123410

Setting the Basis

(Optional) Basis, when combined with the range, defines the location of basis values that determine how the amount is allocated. The basis can consist of upper-level or level 0 members.

You can use these parameters to further define the basis:

  • (Optional) Basis time span specifies one or more time periods to be considered for the basis. Time periods must be level 0 members in a Time dimension.

  • (Required if basis time span is set) Basis time span option specifies how the basis is calculated across the time periods specified by the basis time span. You can choose to use the basis value for each time period individually (split) or use the sum of the basis values across the time periods specified by the basis time span (combine).

    • If basis time span specifies multiple time periods and the target time span specifies one time period or is empty, you must set the basis time span option to combine. Essbase ignores the target time span option.

    • If basis time span and target time span specifies multiple time periods, and you set the basis time span option to split, the periods specified by basis time span and target time span must be identical. Essbase ignores the target time span option.

    See Understanding Settings for Basis and Target Time Span.

  • Zero basis options specifies how to treat a zero basis value. You can choose to skip to the next nonzero or non-#MISSING amount value or cancel the entire allocation operation. Essbase processes the zero basis options setting based on the allocation method. See Setting the Allocation Method.

  • (Optional) Negative basis options specifies how to treat a negative basis value. The options available for the negative basis options depend on the allocation method used. See Setting the Allocation Method.

Note:

The basis is ignored when using the spread allocation method and you have not set any spread skip options.

Setting the Target

Target, when combined with the range, defines the region in the database where allocation values are written. The target can consist of only level 0 members.

You can use these parameters to further define target:

  • (Optional) Target time span specifies one or more time periods to be considered for the target. Time periods must be level 0 members in a Time dimension.

  • (Required if target time span is set) Target time span option specifies the method for allocating values across the time periods specified in target time span. You can choose to divide the amount value or repeat the amount value across the specified time periods.

    • If basis time span specifies multiple time periods, and the target time span specifies one time period or is empty, you must set the basis time span option to combine. Essbase ignores the target time span option.

    • If basis time span and target time span specifies multiple time periods, and you set the basis time span option to split, the periods specified by the basis time span and target time span must be identical. Essbase ignores the target time span option.

See Understanding Settings for Basis and Target Time Span.

Setting the Allocation Method

Allocation method specifies whether to allocate the amount evenly or proportionally.

  • The share method allocates a percentage of the amount (alloc_share_amt) by dividing the basis value for the current member in the range (basis_mbr_value) by the sum of the basis across the range (basis_range_sum). The allocated amounts are based on the number of valid basis values in the range. The algorithm for calculating the allocation share amount:

    alloc_share_amt = (basis_mbr_value/basis_range_sum) * amount

    Basis values and Essbase action:

    • Zero, Essbase writes a zero to the corresponding target cell.

      If the sum of all basis values is zero (which would result in a division-by-zero error), Essbase uses the zero basis options setting. See Setting the Basis.

    • #MISSING, Essbase either leaves the target cell as #MISSING, or, if the target cell already has a value, overwrites the existing value with zero.

    • A negative number, Essbase uses the negative basis options setting. You can choose to use the negative basis value (the default), skip to the next amount value (no data is allocated for the current amount value, and Essbase skips to the next POV combination), or cancel the entire operation.

    The following examples illustrate the share allocation method. In both examples, the amount to allocate is 10.

    In Table 217, assume that the amount (10) represents the rent expense for a building, and the basis represents the head count of each department in the range. Essbase uses the basis values for departments with non-#MISSING head count (Dept_A through Dept_D) to calculate the allocation share amounts, which is the rent allocation.

    The rent allocation for Dept_A is the basis value of Dept_A (3), divided by the sum of valid basis values across the range (3 + 2 = 5), multiplied by the amount (10): 3/5 * 10 = 6. For Dept_D, the rent allocation is 2/5 * 10 = 4. The total of the target cells in the range equals 10.

    Table 217. Share Allocation Method Example

    Members in RangeBasis (Head Count)Target (Rent Allocation)
    Dept_A36
    Dept_B  
    Dept_C00
    Dept_D24

    In Table 218, assume that all basis values are to be considered in calculating the share allocation amounts. The allocation for Mbr1 is the basis value of Mbr1 (3), divided by the sum of valid basis values across the range (3 + -1 + 2 = 4), multiplied by the amount (10): 3/4 * 10 = 7.5. For Mbr3, the allocation is -1/4 * 10 = -2.5; for Mbr4, the allocation is 2/4 *10 = 5. The total of the target cells in the range equals 10.

    Table 218. Share Allocation Method Example: Negative Basis Options — Default

    Members in RangeBasisTarget
    Mbr137.5
    Mbr2#MISSING 
    Mbr3-1-2.5
    Mbr425.0
  • The spread method allocates the amount evenly across the range (alloc_spread_amt). The number used to divide the amount and, therefore, the number of target cells where the allocation spread amount is to be written, is based on the number of valid basis values in the range (#_valid_basis_values). The algorithm for calculating the allocation spread amount:

    alloc_spread_amt = amount/#_valid_basis_values

    When using the spread allocation method, you can use the optional spread skip options parameter to skip all basis values in the range that are zero, #MISSING, or negative. You can specify multiple options.

    Basis values and Essbase action:

    • Zero, Essbase writes a zero to the corresponding target cell.

      If spread skip options is set to skip zero, no data is allocated.

    • #MISSING, Essbase either leaves the target cell as #MISSING; or, if the target cell already has a value, Essbase overwrites the existing value with zeros.

      If spread skip options is set to skip #MISSING, no data is allocated.

    • A negative number, Essbase uses the negative basis options setting (which takes precedence over the spread skip options setting of skip negative). You can choose one of the following actions:

      • Use the negative basis value (the default)

      • Skip to the next amount value (no data is allocated for the current amount value)

      • Use the absolute value of the negative number

      • Treat the negative number as $MISSING (no value is allocated to the target cell)

      • Treat the negative number as a zero (zero is allocated to the target cell)

      • Cancel the entire operation

    If all basis values have been skipped (which would make the denominator in the allocation zero), Essbase uses the zero basis options setting. See Setting the Basis.

    The following examples illustrate the spread allocation method. In both examples, the amount to allocate is 10.

    In Table 219, assume that the spread skip options parameter is not specified. Therefore, Essbase considers all four basis members in the range. Essbase divides the amount (10), by the number of valid basis members in the range (4), and spreads that value (2.5) to each target cell in the range: 10/4 = 2.5.

    Table 219. Spread Allocation Method Example: Do Not Skip Basis Values

    Members in RangeBasisTarget
    Mbr122.5
    Mbr2#MISSING2.5
    Mbr332.5
    Mbr4-62.5

    In Table 220, assume that the spread skip options parameter is set to ignore #MISSING and negative numbers. Therefore, Essbase considers only the two basis members with positive values (Mbr1 and Mbr3). Essbase divides the amount (10), by the number of valid basis members in the range (2), and spreads that value (5) to the Mbr1 and Mbr3 target cells: 10/2 = 5.

    Table 220. Spread Allocation Method Example: Skip #MISSING and Negative Basis Values

    Members in RangeBasisTarget
    Mbr125
    Mbr2#MISSING 
    Mbr335
    Mbr4-6 

Setting the Rounding Method

The rounding method specifies whether to round allocated values (the default is not to round).

If you choose to round values, the rounding method specifies how to handle rounding errors. You can choose to discard rounding errors, or to round all allocated values and add the total rounding error to the highest allocated value, the lowest allocated value, or to a specific cell. If you choose to add the rounding error to the highest or lowest allocated value, and there are multiple highest or lowest allocated values, Essbase chooses one of the highest or lowest values to which to add the rounding error.

If you choose to round allocation values, you can use these parameters to further define the rounding method:

  • (Required if rounding allocated values) Round digits specifies the number of decimal places to which allocated values are rounded. You can choose to round to the nearest integer (the default), to a specified number of decimal places, or to a power of 10.

    Round digits must be a number from -100 to 100 and can be expressed as an integer, an MDX numeric value expression, or a tuple.

    Using an MDX numeric value expression is helpful when the setting for round digits is based on the currency of the allocated value. For example, assume that the database contains a dimension named Currency, which is part of the POV, and an associated attribute dimension named NumCurrencyDigits, which specifies how to round allocated values based on the currency of the allocated values. You can express round digits as:

    Currency.currentMember.NumCurrencyDigits

    Note:

    If, for the rounding method, you choose not to round allocated values, the round digits parameter must be empty.

  • (Required if rounding method is set to a specific location) Round to location specifies a cell to which to add the total rounding error. Expressed as a tuple, the cell must be in the range and have the same dimensionality as the range. Round to location can consist of only level 0 members.

    Note:

    If, for the rounding method, you choose an option other than to round to a specific cell, the round to location parameter must be empty.

Setting the Offset

(Optional) Offset specifies the location in the database where an offsetting value for each source amount is written.

Offset works the same for allocations and custom calculations. See Understanding Offset Handling for Custom Calculations and Allocations.

Balancing Allocations

(Optional) debitMember and creditMember can consist only of level 0 members.

The debit member and the credit member must be two different members from the same dimension.

debitMember and creditMember work the same for allocations and custom calculations. See Understanding Credit and Debit Processing for Custom Calculations and Allocations.

Understanding Settings for Basis and Target Time Span

The number of members that are specified for the basis time span and target time span affect how Essbase treats the basis time span option and target time span option, respectively, as summarized in Table 221. In situations where the basis or target time span is empty or set to a single time period, Essbase ignores any setting that you might have set for the respective basis or target time span option. In situations where one or both of the basis or target time spans are set to multiple time periods, Essbase requires a particular setting for the respective basis or target time span option.

Table 221. Summary: Basis and Target Time Span, and Basis and Time Span Option

Basis Time SpanTarget Time SpanBasis Time Span OptionTarget Time Span OptionSee
Empty or single memberEmpty or single memberIgnoredIgnoredExample 1: Basis and Target Time Span—Empty or Single Member
Empty or single memberMultiple membersIgnoredDivide or repeatExample 2: Basis Time Span—Empty or Single Member; Target Time Span—Multiple Members
Multiple membersEmpty or single memberCombineIgnoredExample 3: Basis Time Span—Multiple Members; Target Time Span—Empty or Single Member
Multiple membersMultiple membersSplitIgnoredExample 4: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Split
Multiple membersMultiple membersCombineDivide or repeatExample 5: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Combine

Example 1: Basis and Target Time Span—Empty or Single Member

In this example, the basis time span and target time span are not set or are set for only one time period. Essbase ignores any setting you might have chosen for the basis time span option or target time span option.

Example 2: Basis Time Span—Empty or Single Member; Target Time Span—Multiple Members

In this example, the basis time span is not set, and multiple time periods are specified for the target time span. The basis time span option is ignored. For the target time span option, you can select either divide or repeat.

Assume the amount is 1000. Table 222 shows that the basis for each department (Dept_1 = 1) and the total basis for the range (21):

Table 222. Example 2: Basis Values

 RangeTotal
 Dept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
Basis

1[1]

23456

21[2]

1 Basis for the member

2 Total basis for the range

The setting for target time span option determines how the allocation is calculated.

  • Repeat the allocated amount across the specified target time span periods:

    In this scenario, Essbase performs the allocation for a single period and copies the allocated amount value to all members in the target time span.

    The algorithm Essbase uses:

    alloc_amt = (basis_mbr_value/basis_total_range) * amount

    As shown in Table 223, for Dec 07,Dept_1, the member basis value (1) is divided by the total basis across the range (21), and the result (0.04762) is multiplied by amount (1000): (1/21) * 1000 = 47.62. Essbase copies 47.62 into the cells for Jan 08, Feb 08, Mar 08, and Apr 08. Essbase continues to perform allocations for Dec 07 for each department. For each target time span, the sum of the allocated values across the range equals the amount (1000).

    Table 223. Example 2: Allocation Using Target Time Span Option Set to Repeat

     RangeTotal
    Members in Target Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
    Dec 0747.6295.24142.86190.48238.10285.711000
    Jan 0847.6295.24142.86190.48238.10285.711000
    Feb 0847.6295.24142.86190.48238.10285.711000
    Mar 0847.6295.24142.86190.48238.10285.711000
    Apr 0847.6295.24142.86190.48238.10285.711000
           

    5000[1]

    1 Total allocated values

    The total allocated amount is the original amount value (1000) multiplied by the number of target time span members (5): 1000 * 5 = 5000.

  • Divide the allocated amount across the specified target time span periods

    In this scenario, Essbase performs the allocation for one period and evenly divides the allocated amount across all members in the target time span.

    The algorithm Essbase uses:

    alloc_amt = ((basis_mbr_value/basis_total_range) * amount)/#_target_time_span_periods

    As shown in Table 224, for Dec 07,Dept_1, Essbase performs the same calculation as described for the repeat target time span option scenario to arrive at 47.62. However, this amount is evenly divided across all five target time span periods for Dept_1; therefore, 9.52 is written in each target cell: 47.62/5 = 9.52. Essbase continues to perform allocations for each department. For each target time span, the sum of the allocated values across the range equals (200).

    Table 224. Example 2: Allocation Using Target Time Span Option Set to Divide

     RangeTotal
    Members in Target Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
    Dec 079.5219.0528.5738.1047.6257.14200
    Jan 089.5219.0528.5738.1047.6257.14200
    Feb 089.5219.0528.5738.1047.6257.14200
    Mar 089.5219.0528.5738.1047.6257.14200
    Apr 089.5219.0528.5738.1047.6257.14200
           

    1000[1]

    1 Total allocated values

    The total allocated values across the range is the original amount value (1000): 200 * 5 = 1000.

Example 3: Basis Time Span—Multiple Members; Target Time Span—Empty or Single Member

In this example, multiple time periods are specified for the basis time span, but the target time span is not set. The target time span option is ignored. The only valid choice for the basis time span option is combine.

Assume the amount is 1000. As shown in Table 225, the basis to be used for each department is the sum of the basis values for the basis time span (Dept_1 = 15); the total basis for the range is the sum of all department basis values (147):

Table 225. Example 3: Basis Values

 RangeTotal
Members in Basis Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
Dec 07123456 
Jan 08234567 
Feb 08345078 
Mar 08456189 
Apr 085672910 
Total

15[1]

2025123540

147[2]

1 Basis for each range member summed across the basis time span periods

2 Total basis for the range

The allocation is calculated using the basis time span setting of combine, which uses the sum of the basis values across the basis time span periods.

The algorithm Essbase uses for each range member:

alloc_amt = (sum_across_basis_time_span/basis_total_range) * amount

As shown in Table 226, the allocated value for each department is written to one target location, because the target time span is not set to multiple periods. For the allocated amount for Dept_1, the sum of the basis time span (15) is divided by the total basis for the range (147), and the result (0.10204) is multiplied by amount (1000): (15/147) * 1000 = 102.04. Essbase continues to perform allocations for each department in the range.

Table 226. Example 3: Allocation Using Basis Time Span Set to Combine

 RangeTotal
 Dept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
Target102.04136.05170.0781.63238.10272.11

1000[1]

1 Total allocated values

The total allocated values across the range is the original amount value (1000).

Example 4: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Split

In this example, multiple time periods are specified for the basis time span and the target time span, and the basis time span option is set to split. When using the split basis time span option, the periods specified by the basis time span and target time span must be identical. (For an example of setting the basis time span option to combine, see Example 5: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Combine.)

Assume the amount is 1000. As shown in Table 227, the total basis for the range is the sum of all department basis values (165):

Table 227. Example 4: Basis Values

 RangeTotal
Members in Basis Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
Dec 07123456

21[1]

Jan 0823456727
Feb 0834567833
Mar 0845618939
Apr 08567291045
       

165[2]

1 Total basis for each basis time span period

2 Total basis for the range

The allocation is calculated using the basis time span setting of split, which uses the basis value for each time period individually.

The algorithm Essbase uses:

alloc_amt = (basis_mbr_value/basis_total_range) * amount

As shown in Table 228, for Dec 07,Dept_1, the member basis value (1) is divided by the total basis for the range (165), and the result (0.00606) is multiplied by amount (1000): (1/165) * 1000 = 6.06. Essbase continues to perform allocations for each time period for each department.

Table 228. Example 4: Allocation Using Basis Time Span Set to Split

 RangeTotal
Members in Target Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
Dec 076.0612.1218.1824.2430.3036.36127.27
Jan 0812.1218.1824.2430.3036.3642.42163.64
Feb 0818.1824.2430.3036.3642.4248.48200.00
Mar 0824.2430.3036.3642.4248.4854.55236.36
Apr 0830.3036.3642.4248.4854.5560.61272.73
       

1000[1]

1 Total allocated values

The total allocated values across the range is the original amount value (1000).

Example 5: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Combine

In this example, multiple time periods are specified for the basis and target time spans; however, because the basis time span option is set to combine, the basis and target time spans need not contain the same member set. (For an example of setting the basis time span option to split, see Example 4: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Split.)

Assume the amount is 1000. As shown in Table 229, the basis to be used for each department is the sum of the basis values across the basis time span (Dept_1 = 10); the basis for the range is the sum of all department basis values (113):

Table 229. Example 5: Basis Values

 RangeTotal
Members in Basis Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
Dec 07123456 
Jan 08234567 
Feb 08345678 
Mar 08456089 
Total

10[1]

1418152630

113[2]

1 Basis for each range member summed across the basis time span periods

2 Total basis for the range

The setting for target time span option determines how the allocation is calculated.

  • Repeat the allocated amount across the specified target time periods:

    In this scenario, Essbase performs the allocation for a single period and copies the allocated amount value to all members in the target time span.

    The algorithm Essbase uses for each range member:

    alloc_amt = (sum_across_basis_time_span/basis_total_range) * amount

    As shown in Table 230, for Dec 07,Dept_1, the basis for Dept_1 (10) is divided by the total basis for the range (113), and the result (0.0885) is multiplied by amount (1000): (10/113) * 1000 = 88.50. Essbase copies 88.50 into the cells for Jan 08, Feb 08, Mar 08, and Apr 08. Essbase continues to perform allocations for Dec 07 for each department. For each target time span, the sum of the allocated values across the range equals the amount (1000).

    Table 230. Example 5: Allocation Using Target Time Span Option Set to Repeat

     RangeTotal
    Members in Target Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
    Dec 0788.50123.89159.29132.74230.09265.491000
    Jan 0888.50123.89159.29132.74230.09265.491000
    Feb 0888.50123.89159.29132.74230.09265.491000
    Mar 0888.50123.89159.29132.74230.09265.491000
    Apr 0888.50123.89159.29132.74230.09265.491000
           

    5000[1]

    1 Total allocated values

    The total allocated values is the original amount value (1000) multiplied by the number of target time span members (5): 1000 * 5 = 5000.

  • Divide the allocated amount across the specified target time periods:

    In this scenario, Essbase performs the allocation for a single period and evenly divides the allocated amount across all members in the target time span.

    The algorithm Essbase uses:

    alloc_amt = ((basis_time_span/basis_total_range) * amount)/#_target_time_span_periods

    As shown in Table 231, Essbase performs the same calculation as described for the repeat target time span option scenario to arrive at 88.50. However, this amount is evenly divided across all five target time span periods for Dept_1; therefore, 17.70 is written in each target cell: 88.50/5 = 17.70. Essbase continues to perform allocations for each department. For each target time span, the sum of the allocated values across the range equals (200).

    Table 231. Example 5: Allocation Using Target Time Span Option Set to Divide

     RangeTotal
    Members in Target Time SpanDept_1Dept_2Dept_3Dept_4Dept_5Dept_6 
    Dec 0717.7024.7831.8626.5546.0253.10200
    Jan 0817.7024.7831.8626.5546.0253.10200
    Feb 0817.7024.7831.8626.5546.0253.10200
    Mar 0817.7024.7831.8626.5546.0253.10200
    Apr 0817.7024.7831.8626.5546.0253.10200
           

    1000[1]

    1 Total allocated values

    The total allocated values across the range is the original amount value (1000): 200 * 5 = 1000.

Examples of Aggregate Storage Allocations

The following example illustrates how changing the POV combination affects the values considered as the basis for the allocation. The example, which uses the share allocation method, allocates the total rent from the previous year to all cost centers in the current year, based on each cost center's head count. Assume that the aggregate storage database has four dimensions—Departments, Time, CostCenter, and Measures—and the allocation criteria is specified as shown in Table 232:

Table 232. POV Example: Allocation Criteria

CriteriaDefinition
POVDept_A, Dept_B
Amount

2007, CCNA, TotalRent

Assume that the amount values are:

  • Dept_A = 1000

  • Dept_B = 2000

BasisJan 2008, Head count
TargetJan 2008, RentalAllocation
RangeLevel 0 descendants of CostCenter

Assume that the range evaluates to the following cost centers:

  • CostCenter1

  • CostCenter2

  • CostCenter3

  • CostCenter4

The allocation is performed for each of the POV combinations:

  • Dept_A

  • Dept_B

Each POV combination has its own set of basis values that are used in calculating the allocation: the head count for each cost center in the range and the total Jan 2008 head count, as shown in Table 233:

Table 233. POV Example: Basis Values for Each POV Combination

 Member Basis ValuesRange Basis Value
POV

CostCenter1

CostCenter2

CostCenter3

CostCenter4

2008 Head Count Total

Dept_A123511
Dept_B50 1015

For each POV, Essbase divides the head count of each cost center (the basis value of each member) by the total head count of the range (the basis value of the range), and then multiplies that value by the total rental amount for each department (amount). For example, for Dept_A,CostCenter1, the member basis value (1) is divided by the basis of the range (11), and the result (0.09090909) is multiplied by amount (1000): (1/11) * 1000 = 90.90909. For Dept_B,CostCenter1, the allocated amount is 666.6667: (5/15) * 2000 = 666.6667. Table 234 shows the allocated share amount for each cost center:

Table 234. POV Example: Target Values for Each POV Combination

 Member Target ValuesAmount Value
POV

CostCenter1

CostCenter2

CostCenter3

CostCenter4

Rental Allocation Total

Dept_A90.90909181.8182272.7273454.54551000
Dept_B666.66670 1333.3332000

Sample Use Case for Aggregate Storage Allocations

The objective of this sample use case is to use the share allocation method to proportionally redistribute the total monthly rent expense across departments, based on the square footage each department occupies.

Consider an outline with the following dimensions:

  • Company: Contains multiple ledgers. The rent expense allocations take place in the Vision US ledger.

  • Department: Contains the following members:

    • 100, which stores the total monthly rent expense, of $100,000, for Vision US. This amount is proportionally allocated to the children of department 999.

    • 999, which is the parent of the following departments:

      • 101, which receives 45% of the rent allocation

      • 102, which receives 30% of the rent allocation

      • 103, which receives 25% of the rent allocation

  • Account: Contains the following members:

    • 5740, which is a rent expense account

    • SQFT, which is a statistical account used to record square footage for each department

  • AmountType: Contains PeriodActivty, which is the parent of the following members:

    • PeriodActivityDebit, which is the target location

    • PeriodActivityCredit, which is the offset location

You can accomplish the rent expense allocation in several ways, each with the same result. Two scenarios are presented. For each scenario, assume that the following parameters are defined as follows:

  • Allocation method: Share

  • Range: The descendents of department 999:

    • 101

    • 102

    • 103

    No cells in the range are excluded.

  • Basis: The square footage of each range member for the period of activity (which is monthly).

    SQFT,PeriodActivity
  • Zero amount option: (Default) Allocate zero amount values.

  • Zero basis option: If the basis value is zero, cancel the allocation operation.

  • Basis time span option: (Default) Split, use the basis value for each time period individually.

  • Rounding method: Round allocation values to the nearest 1,000 and add the total rounding error to department 101.

  • Debit member: If the sum is positive, write the value to PeriodActivityDebit.

  • Credit member: Write the value to PeriodActivityCredit.

Scenario 1: Aggregate Storage Allocations

For scenario 1, assume the following parameters are defined as follows:

  • POV: Consists of one member, Vision US, from the Company dimension.

    Because only one member from one dimension is specified, the POV does not change and, therefore, the allocation is performed only once.

  • Amount: The source value of the allocation is from the following cross-dimensional member:

    5740,100,Beginning Balance
  • Target: Write allocated values to account 5740 for each department.

  • Offset: Write the offsetting entry to member 5740,100.

Scenario 2: Aggregate Storage Allocations

For scenario 2, assume the following parameters are defined as follows:

  • POV: Consists of one member each from two dimensions:

    • Vision US, from the Company dimension

    • 5740, from the Amount dimension

      In this scenario, account 5740 is a part of the POV. In the basis, account 5740 is overridden with member SQFT.

    Because only one member from each dimension is specified, the POV does not change and, therefore, the allocation is performed only once.

  • Amount: The source value of the allocation is from the following cross-dimensional member:

    100,Beginning Balance
  • Target: Not set. Because the combination of POV, target, debit member/credit member, and range uses members from all dimensions, the target can be empty.

  • Offset: Write the offset entry to department 100.

Avoiding Data Inconsistency When Using Formulas

Formula members can be used in the amount and the basis but not in the target. However, because aggregate storage databases do not support transaction semantics, you might experience data inconsistency issues when using formula members in the amount or the basis.

In the following example, User 1 posts revenue values for a set of departments and User 2 performs an allocation of bonus money, for which the year-to-date revenue for each department is basis of the allocation. The order in which these operations are performed affects the result:

  • Scenario 1: User 1 posts revenue before User 2 runs the allocation.

    The allocation results are based on the updated revenue values.

  • Scenario 2: User 2 runs the allocation before User 1 posts revenue.

    The allocation results are based on prior revenue values, not on the updated revenue values.

  • Scenario 3: User 1 posts revenue and User 2 runs the allocation concurrently.

    The allocation results are based on the updated or prior revenue values, depending on which user operation started first.

    Oracle does not recommend running these operations concurrently when using formula members in the amount or the basis.

Also, assume that an MDX formula is used to calculate the year-to-date revenue for the allocation. The complexity of the formula can affect the result:

  • Scenario 4: The year-to-date revenue formula involves members from one dimension and uses only the following arithmetic expressions: +, -, /, and *.

    The allocation results are based entirely on either the updated or prior revenue values.

    Oracle recommends using simple MDX formulas, as described in scenario 4.

  • Scenario 5: The year-to-date revenue formula is more complicated than the formula in scenario 4.

    It is possible that some of the allocation results are based on the updated revenue values and some are based on the prior revenue values.

Understanding Data Load Buffers for Custom Calculations and Allocations

When performing allocations or custom calculations on an aggregate storage database, Essbase uses temporary data load buffers. If there are insufficient resources in the aggregate storage cache to create the data load buffers, Essbase waits until resources are available.

Multiple data load buffers can exist on a single aggregate storage database. The data load buffers that Essbase creates for allocations and custom calculations are not configurable. You can, however, configure the data load buffers that you create for data loads and postings.

If you want to perform allocations and custom calculations concurrently with data loads and postings, set the resource usage for the data load buffers that you create for data loads and postings to a maximum of 0.8 (80%). The lower you set the resource usage setting, the greater the number of allocations and custom calculations that can run concurrently with data loads and postings. You can also configure the amount of time Essbase waits for resources to become available in order to process load buffer operations.

To configure data load buffers, use the alter database MaxL statement with the initialize load_buffer grammar and ASOLOADBUFFERWAIT configuration setting.

Understanding Offset Handling for Custom Calculations and Allocations

In general ledger bookkeeping, an offsetting entry is a counterbalancing measure on the opposite side of the ledger from a transaction of equal value. In this document, an offsetting entry is referred to as an offset.

Specification of an offset is optional. An offset might be needed in case the sum of credits and the sum of debits are not equal. If the sum of credits and debits are not equal, the ledger is unbalanced. In such a case, an offset would serve to balance the ledger.

For example, a $100 credit in January may need a $100 offset added to the debit side of the ledger, so that the ledger can be balanced in preparation for a known upcoming expense of that amount.

An offset is a location you specify in the form of a tuple, to which Essbase writes a value offsetting the result of the custom calculation script.

In the following examples, assume the POV is Prod1, Prod2, AcctA, AcctB, Jan.

The following custom calculation script has a sum of 13.

mbr1 := 7;
mbr2 := -4;
mbr3 := 0;
mbr4 := 10;

Therefore, if an offset is required, it must also be 13. Assume that an offset is written to a member called “Offset_Member.”

          Debit     Credit
mbr1          7          
mbr2                    4
mbr3          0	
mbr4         10	
mbr_offset             13
Total        17        17

When an offset is used, credit and debit processing is reversed. The following calculation sequence occurs when an offset is used with credit and debit processing:

  1. For the given POV, get the sum of results written by the calculation script (in this case, 13).

  2. If the sum is positive, write it to the credit member in the target database.

  3. If the sum is negative, change it to a positive and write it to the debit member in the target database.

Understanding Credit and Debit Processing for Custom Calculations and Allocations

Oracle General Ledger uses double-entry accounting, in which every transaction has two journal entries: a debit entry and a credit entry.

Thus, for every transaction, there are two accounts, represented as columns. The two accounts must balance; in other words, the sum of debit column must equal the sum of the credit column.

A debit member can be specified, to which the custom calculation writes positive result values, and a credit member can be specified, to which the custom calculation writes negative and offsetting result values. The debit member and the credit member must be two different members from the same dimension. For example, a dimension called “AmountType” may have two level 0 children named “Credit” and “Debit.”

Whenever the calculation would result in writing a positive number to a level 0 cell in the target database, the positive value is written to the debit member.

Whenever the calculation would result in writing a negative number to a level 0 cell in the target database, the sign is changed to a positive and is written to the credit member.