• Protects upper-level data loads
• Slows calculation performance
• Is the default setting for new databases
Before calculation After calculation
Oracle Internal & Or acle Academy Use Only
Controlling Aggregate Missing Values Behavior
There are two ways to control aggregate missing values behavior:
• Default database setting: New databases by default do not aggregate missing values. You can change the default setting on the General tab of the Database Properties dialog box.
• Calculation script: You can use the command SET AGGMISSG ON|OFF to control the aggregation of missing values on a script-by-script basis. This command
overrides the default database setting and is typically included in the housekeeping section of the script.
Performance Implications
When you aggregate missing values during hierarchy consolidations (SET AGGMISSG ON), calculation performance is significantly enhanced for the following reasons:
• Dense calculations within a data block: Cell aggregations that can be performed multiple ways are summed only once. In the following example, Essbase can achieve the same result in the intersection of Cost of Sales and Qtr 1 by using the account formula (Labor+Material+Overhead) or the time period formula (Jan+Feb+Mar).
Rather than calculating "Cost of Sales"->"Qtr 1" during the Accounts dimension calculation and then again during the Year Tot dimension calculation, Essbase calculates the cell only once, using the consolidation path of the dimension that it calculates last.
T I P
As a best practice, you should set aggregate missing values on as a database default and turn it off selectively in your calculation scripts.
Jan Feb Mar Qtr 1
Labor 100 100 100 300
Material 50 50 50 150
Overhead 75 75 75 225
Cost of Sales 225 225 225 675
Oracle Internal & Or acle Academy Use Only
• Sparse calculations between data blocks: Blocks that can be aggregated multiple ways are calculated only once, using an algorithm that attempts to calculate using the fewest possible blocks. In the following example, data block 12 (the intersection of Thunderball and OEM) can be calculated as the sum of blocks 4+8 or the sum of blocks 9+10+11. Rather than using both formulas and thus calculating block 12 twice, Essbase uses only the 4+8 formula because it requires fewer blocks.
Although not aggregating missing values (SET AGGMISSG OFF) is required to protect upper-level data, not aggregating missing values during hierarchy consolidations can have a serious impact on calculation performance, especially in the following two situations:
• When you have a low ratio of calculated data blocks to input data blocks
• When you load many data values at parent levels on sparse dimensions; for
example, in the Bigcorp Sales database, if you load many data values into Retail in a sparse Customer dimension
In these situations, the performance overhead required for not aggregating missing values is between 10% and 30%. If calculation performance is critical, reconsider how you configure the database or how you load data, to avoid the need to protect upper-level input data.
Expected Versus Correct Considerations
When you load data to upper levels in your hierarchy, additional issues of expected versus correct calculation behavior must be considered. These issues are difficult to understand without stepping through the calculations. The following example illustrates the problem.
O-Dell O-HP O-IBM OEM
Thunderball 270 A 1 2 3 4
Thunderball 540 A 5 6 7 8
Thunderball 9 10 11 12
Oracle Internal & Or acle Academy Use Only
The Other CGS account data for Bigcorp Sales is collected in transactional systems without regard for customer or product. Thus, the data is available for input only at the level that represents the total for all products (Family Total) and all customers (Channel Total). The following input sheet loads Other CGS data to the "Current Year"->"Family Total"->"Channel Total" data block.
However, all other account drivers are input to level 0 blocks. The following input sheet loads other account data (units, list price, and so on) to the "Current Year"->"Lightbolt 365 A"->"O-IBM" and "Current Year"->"Thunderball 540 S"->"O-IBM" data blocks.
You calculate the following script, making sure to include instructions to protect the data loaded in the upper-level block.
/* Housekeeping */
SET UPDATECALC OFF;
SET AGGMISSG OFF;
/* The Main Rollup */
CALC DIM (Accounts, "Year Tot", Customer, Product);
Oracle Internal & Or acle Academy Use Only
/* The Back Calculation */
(
"List Price" = "Gross Sales" / Units;
"Discount %" = Discounts / "Gross Sales";
"Labor/Unit" = "Direct Labor" / Units;
"Matl/Unit" = Material / Units;
"Overhead Rate" = Overhead / "Direct Labor";
)
However, when you audit your results, you notice that, although totals for Net Sales, Cost of Sales and Other CGS are correct, totals for Gross Margin are not correct.
The incorrect totals for Gross Margin are due to calculation order: The Accounts dimension calculates first, creating Gross Margin totals in both level 0 bocks. When Customer and Product are calculated, Essbase aggregates the data that exists in the level 0 blocks. This aggregation creates incorrect totals for Gross Margin in the "Current Year"->"Family Total"->"Channel Total" block. The Other CGS data is protected during the aggregation by the SET AGGMISSG OFF command.
To correct the incorrect totals, you must calculate Gross Margin in the "Current
Year"->"Family Total"->"Channel Total" block in a separate, additional calculation pass, such as a back calculation, that takes place after the main rollup. This problem can occur frequently when you work with level inputs. If you have a large number of multi-level inputs that force you to recalculate data at upper multi-levels, consider a method other than protecting level loads by not aggregating missing values for handling upper-level input data.