Two-Pass Calculations

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
Sales	1000	1000	1000
Profit %

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%

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%

Checklist for Calculations

Use the following checklist when you define a calculation:

• Does the default calculation logic achieve accurate results?
• Which members require formulas?
• Which members require time balance tags?
• Which members require variance reporting?
• Which members require two-pass calculation?
• Which members can be tagged as Dynamic Calc?
  
  Note:

  The Essbase triggers feature enables efficient monitoring of data changes in a database. See Understanding Triggers Definitions.

Defining Reports

To ensure that the design meets user information requirements, you must view data as users view it. Users typically view data through spreadsheets, printed reports, or reports published on the Web. Oracle and its partners offer many tools for producing the reporting systems that users use.

Several tools can help you display and format data quickly, and test whether the database design meets user needs. You can use the Report Script Editor in Administration Services Console to write report scripts quickly. Those familiar with spreadsheets can use the Spreadsheet Add-in or Smart View (Smart View requires Provider Services).

During the design phase, check for the following things:

• Grouping and sequencing of data. Do the intersections enabled by the design provide the data that users need?
• Levels of totals. What consolidation levels are required by, for example, a Spreadsheet Add-in user who drills down and up through the hierarchy of the outline design?
• Attribute reporting. Does the database design facilitate an analysis that is based on the characteristics or attributes of specific dimensions or members? For example, do you need to compare sales by specific combinations of size and packaging, such as comparing the sales of 16‑ounce bottled colas with the sales of 32‑ounce bottled colas?

Be sure to use the appropriate tool to create and test predesigned use reports against the test data. The reports that you design should provide information that meets your original objectives. The reports should be easy to use, providing the right combinations of data and the right amount of data. Because reports with too many columns and rows are difficult to use, you may need to create several reports instead of one all‑inclusive report.

Verifying the Design

After you analyze the data and create a preliminary design, check all aspects of the design with users. You should already have verified that the database satisfies the users’ analysis and reporting needs. Ensure that the database satisfies all of their goals.

Do the calculations provide the information they need? Are they able to generate reports quickly? Are they satisfied with consolidation times? In short, ask users if the database works for them.

Near the end of the design cycle, test with real data. Does the outline build correctly? Does all data load? If the database fails in any area, repeat the steps of the design cycle to identify the cause of the problem.

Essbase provides several sources of information to help you isolate problems. Sources include application and Essbase Server logs, exception logs, and database information accessible from Administration Services. Look at documentation topics relevant to your problem; for example, topics about security, calculations, reports, or general error messages. Use the index of this guide for help in solving problems. Look up such terms as troubleshooting, logs, optimizing, performance, recovery, resources, errors, and warnings.

Most likely, you will need to repeat one or more steps of the design process to arrive at the ideal database solution.