Intro to Calculations in Cube

In this article:

• Import Calculations

• Rollups of CubeValues into Parent Dimensions.

• Simple Formulas

Import Calculations

Cube can import data from various sources. Most commonly, Cube ingests transactions or other source data as inputs into the Cube system.

This data is stored as raw values as the first step in Cube’s data pipeline. Then, raw values go through the import process, aggregated by month to create CubeValues.

To enable drilldowns, each CubeValue keeps track of its raw value source, which includes columns for each of the standard top-level dimensions (TLDs): Account, Department, Scenario, Time, and up to four custom TLDs.

Most commonly, the calculation of CubeValues from import is simply a sum of transactions within a month, and this (if needed) is then multiplied by the appropriate currency conversion calculation.

However, various options and overrides may complicate determining the appropriate currency conversion.

Post Import Calculations

Let's look at a simplified example where the imported CubeValues look like this:

Using the following Dimensions:

The dimensions form a hierarchy or tree structure. The deepest level dimensions of the hierarchy are also called the leaf dimensions (notated in green). 

Before rollups and formulas are applied, there are potential CubeValues at the intersections of all leaf dimensions, as shown below:

In practice, many intersections may be missing from the CubeValues table.

For example, the Engineering Department may not have any entries in the Advert. & Marketing account. However, calculations still need to consider these, so it typically defaults empty entries with a “0” value for roll-ups and formulas.

Rollups of CubeValues into Parent Dimensions

Rollups are calculated for each parent dimension that is requested in a spreadsheet. In most cases, these are simply sums of the CubeValues of the children dimensions. However, there may be a lot of the CubeValues involved in these sums.

The darkest CubeValues below represent all the potential parent rollups in our sample case.

For example, “Product Revenue” and “Other Revenue” rollup to “Revenue” on the “Account” dimension, and in turn, “Revenue” and “Interest Income” rollup to their parent “Income”. In the “Time” dimension, “month” values rollup to “quarter” values which in turn rollup to “year” values.

Simple Formulas

Cube formulas can generally be quite complicated, and the results of complex formulas can sometimes be unintuitive.

However, simple formulas are straightforward. For our example, the “Profit” account dimension is specified by the formula:

Profit = Income - Expense

At a conceptual level, this subtracts each cell in the “Expense” slice from the corresponding cell in the “Income” slice to obtain the value for the corresponding cell in the “Profit” slice.

Example: