Cube - Multidimensional planning

What is a cube? #

In many business scenarios, plans are created separately for each dimension—such as regions, product lines, departments, or time periods—resulting in duplicated effort and fragmented planning. Multi-dimensional cube planning lets you create and allocate plans across multiple dimensions with different granularities in a single step.

Plan across unrelated dimensions#

In real-world planning, different functions often use different dimensions to represent their planning requirements. For example, the Sales function may plan revenue by Product, City, Time, and Channel, while Finance plans revenue by GL Account, Country, and Time.

These dimensions do not have a direct one-to-one relationship. A sales plan cannot be directly mapped to a finance plan. However, both functions may need to plan, reconcile, and report on the same revenue.

A cube can bridge these different planning structures by creating a multidimensional view of the data and allocating values across the required dimensions.

For example:

• Finance sets a revenue budget by GL Account, Country, and Time.
• Sales needs to distribute that budget across Product, City, Channel, and Time.
• The cube uses the available relationships and allocation drivers to distribute the finance-level value across the sales dimensions.
• Sales can then refine the forecast at its planning grain, while the values aggregate back to the finance dimensions for consolidated reporting.

Driver-based allocation model#

Each cube is configured around a data input measure or forecast measure. Allocation within the cube is performed using an allocation driver (also referred to as an allocation key or reference measure).

The allocation driver is typically a DAX (Data Analysis Expressions) measure from the semantic model, such as prior year actuals, current year revenue, units sold, headcount, or production volume.

This driver measure provides the weights and ratios used for proportional distribution.

How allocation works #

1. A value is entered at a summarized level, such as 500 entered for a product without selecting lower-level dimensions (for example, region or province).
2. The selected allocation driver measure determines how the value should be distributed.
3. The value is allocated proportionally across all valid dimension intersections, based on the driver measure’s relative weights.

Allocation formula (conceptual) #

The allocated value is calculated by multiplying the entered value by the relative weight of the allocation driver at each valid intersection. The following formula shows how allocations work:

Allocated Value =
Entered Value ×
(Driver Value at the intersection ÷ Sum of Driver Values within the hierarchy scope)

In the formula,

• Entered Value is the total value entered at a higher level of aggregation.
• Driver Value at the intersection is the allocation driver's value for a combination of row and column dimensions.
• The hierarchy scope includes all valid lower‑level intersections over which the entered value is distributed.

What allocation means in practice #

Allocation is performed only for dimension intersections where the driver has a non-null value.

Values are distributed based on the relative contribution of each driver value within the hierarchy scope.

Allocation respects the dimensional granularity and breakdowns configured in the cube, ensuring consistency with the data model.

Multidimensional allocation #

Cubes support distributing plans across:

• Dimensions present in the planning sheet
• Dimensions not currently visible in the sheet, but configured in the cube breakdown
• Multiple granularities, simultaneously

Complex enterprise allocations—such as Region > Product Line > Department—can occur in a single action, while maintaining data integrity across the cube.

The allocation driver measure doesn't need to be added to the planning sheet. It can exist solely in the semantic model and be used internally as the weighting mechanism.

Use case: Enterprise level budget allocation #

Consider an organization allocating an annual budget across Regions, Product Lines, and Departments.

The organization can follow these steps to use a cube:

1. Enter the total budget at a higher level.
2. Select a driver measure (for example, prior year actuals) as the allocation driver.
3. The cube proportionally distributes the budget across all valid intersections.
4. Allocations remain synchronized across all dimensions—even the dimensions not visible in the current sheet.

This approach avoids manual breakdowns, duplicate models, and reconciliation errors.

Use case: Multi-granular assumptions with hierarchical allocation #

Consider an organization planning across two core hierarchies:

• Geography Hierarchy: Region > Country
• Product Hierarchy: Brand > Category > Product

These hierarchies define the full analytical space (Region × Country × Brand × Category × Product × Time).

The organization can follow these steps to apply a cube-driven planning model:

1. Capture assumptions at their natural grain.

   Each assumption is entered at the level most relevant to the business:

   • Revenue Plan > Product × Country
   • Cost Plan > Region × Brand
   • Marketing Plan > Brand

   Each input reflects how the business actually plans, not an artificial lowest level.

2. Use a common driver for alignment.

   A consistent driver measure (for example, Revenue Actuals) is used to determine distribution weights across the entire hierarchy.

3. Allocate across hierarchies.

   The cube automatically spreads each assumption across missing dimensions:

   • Brand-level > down to Category > Product
   • Region-level > down to Country
   • Combined > expanded to Product × Country

   All allocations follow the driver distribution.

4. Converge to a common grain.

   All assumptions are aligned to a unified level: Product × Country × Time.

5. Enable unified reporting.

   Once aligned, assumptions can be combined seamlessly, enabling metrics like: Profit = Revenue − (Cost + Marketing) at the Product × Country level.

   Planners can work at different levels while ensuring all data converges into a single, consistent analytical model.