Two-Pass Calculations

In the TBC database, Margin % and Profit % contain the label two-pass. This default label indicates that some member formulas must be calculated twice to produce the desired value. The two-pass property works only on members of the dimension tagged as accounts and on members tagged as Dynamic Calc and Dynamic Calc and Store.

The following example illustrates why Profit % (based on the formula Profit % Sales) has a two-pass tag. The tables have five columns (column headers are labeled left to right as Dimension, Jan, Feb, Mar, and Qtr1) and three rows (labeled as Profit, Sales, and Profit %). Jan, Feb, Mar, and Qtr1 are members of the Year dimension. Profit, Sales, and Profit % are members of the Measures (accounts) dimension.

Table 10, Data Loaded into Essbase defines the initial data to load into Essbase. The data values for Profit -> Jan, Profit -> Feb, and Profit -> Mar are 100. The data value for Sales -> Jan, Sales -> Feb, and Sales -> Mar are 1000.

Table 10. Data Loaded into Essbase

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

N/A

Sales

1000

1000

1000

N/A

Profit %

N/A

N/A

N/A

N/A

First, Essbase calculates the Measures dimension. In Table 11, Data After Essbase Calculates the Measures Dimension, the data values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar are 10%.

Table 11. Data After Essbase Calculates the Measures Dimension

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

Sales

1000

1000

1000

Profit %

10%

10%

10%

N/A

Next, Essbase calculates the Year dimension. The data rolls up across the dimension. In Table 12, Data After Essbase Calculates the Year Dimension, the data values for Profit -> Qtr1 (300) and Sales -> Qtr1 (3000) are correct. The data value for Profit % -> Qtr1 (30%) is incorrect because Profit % is tagged as a two-pass calculation.

Table 12. Data After Essbase Calculates the Year Dimension

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

300

Sales

1000

1000

1000

3000

Profit %

10%

10%

10%

30%

Essbase then recalculates profit percentage at each occurrence of the member Profit %. In Table 13, Data After Essbase Recalculates Profit Percentage, the data value for Profit % -> Qtr1 (10%) is correct after the second pass.

Table 13. Data After Essbase Recalculates Profit Percentage

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

300

Sales

1000

1000

1000

3000

Profit %

10%

10%

10%

10%