Concept: Cube Sheet Calculations Must Evaluate to Zero

This warning indicates that your cube calculation presents as an error from the sheet view. The errors ensure that the cube sheet continues to perform.

How Evaluating to Zero Helps

Think of the cube sheet as a dinner table and the cells as seats at the table. The host serves food at seats with plates. Likewise, a cube doesn’t perform the calculation when formulas reference empty cells. Instead, the cube calculates to zero, or doesn’t serve food.

This system all works well when the referenced account is on the same cube. But when the referenced account isn’t on the same cube, the cube doesn’t know when to evaluate to zero.

It’s like blindfolding the host at dinner. If the host has to serve food without knowing where the plates are, the host wastes food and energy. Likewise, if the cube has to search every intersection to know when to evaluate to zero, it burdens the performance of the sheet.

Instead, the host refuses to serve; likewise the cube refuses to calculate and instead returns errors.

Calculations within the Same Cube Sheet

This example shows how calculations in cube sheets work when you reference accounts within the same sheet.

Acct.CubeCalc

and

Acct.Cube_001

exist in the same cube sheet.

Acct.Cube_001

is a data entry account.

Acct.CubeCalc

has this formula referencing the cube account: Cube_ACCT.001*5
.

Account	Jan	Feb
Acct.Cube_001	5
Acct.CubeCalc	25	0

Because

Acct.Cube_001

has no data for February, the cube calculation evaluates to 0.

Calculations with References Outside of the Cube

This example shows shows how cube calculations work when you reference accounts outside of the cube sheet.

Acct.CubeCalc

references

Acct.002

, a general ledger that’s outside of the cube sheet.

Acct.CubeCalc

has this formula referencing the general ledger: div(Acct.002, 2)
.

When you save the cube calculated account in

Modeling

, you get this error: Cube Calculation Formulas Must Evaluate to Zero
. The error doesn’t prevent you from saving the sheet. On the sheet, however, you see errors in the cells:

Account	Jan	Feb
Acct.CubeCalc	Error	Error

The cube calculation doesn’t know whether or not there’s plates in January or February for Acct.002 because that account isn’t on the cube sheet. Rather than serve food at every possible seat – or search for every possible intersection – the cube refuses to serve the food at all, meaning it returns an error.

Trigger Accounts in Cube Sheets

To resolve the error, you can:

Reference another cube account as a trigger account and add an isblank
condition to the formula.

Add the general ledger or custom account to the cube as a cube-entered account. See Add General Ledger and Custom Accounts to Cube Sheets.

Create a merged cube sheet, which removes the need of a trigger account. See Concept: Merged Cube Sheets.

A trigger can be any account on the same cube, excluding cube metric accounts. The trigger tells the cube calculation where the expected plates are at the table. To remove the error for the formula of AcctCubeCalc, update it like this:

Old formula: div(Acct.002, 2)

New formula: if(isblank(ACCT.Cube_Trigger),0,div(Acct.002, 2))

The new formula says: If the trigger account is blank
, then return a zero. Otherwise divide the general ledger account by 2. Now, you remove the errors from the sheet. The calculation only works when there’s a plate in the trigger even when there’s data in the general ledger:

Account	Jan	Feb
Acct.CubeCalc	3500	0
Acct.Cube_Trigger	1

Once you add data to February for the trigger, the calculation finds the data in the general ledger and calculates for February.

Limitations for Cube Trigger Accounts

Cube metric accounts don’t work as triggers. Cube metrics calculate at every possible intersection within the cube. In addition to calculating at all possible leaf intersections, they also calculate at every possible permutation of parent intersections.

Cube metrics force a host to serve dinner at a huge magnitude, even when only a few guests are in attendance. When a cube metric entertains 10 guests, it serves 10,000 plates of food.

Therefore using a metric as a trigger would cause undue stress on the cube sheet.

Triggers, in general, have the potential to add stress to the system. Be careful not to add triggers to intersections that aren't currently in use.

Efficient triggers create only the necessary intersections. Example: Use a PDS to load triggers for certain dimension values. Or, use historical combinations to indicate future values.