Dimensionality & hierarchies

Overview

Use this category when you need to control a formula’s output shape: which dimensions and levels the result has, and at what granularity values exist. These functions let you roll up to higher levels, drop detail, expand values to more detail, or distribute values across level values.

Start here if…

• Your result is too aggregated (you lost Product/Region/other detail).

• Your result is too granular (you got more detail than expected).

• Two nodes won’t combine cleanly because they have different dimensionality.

• You want to prepare a node for simulation by creating the full dimensional space.

Not here if…

• You only need a numeric transformation (scale, rounding, ratios) → see Math & numeric

• You only need a subset of members → see Filtering & data shaping

Mental model

• Roll up: reduce granularity by aggregating to specified higher levels.

• Drop level: remove levels from a dimension to simplify shape.

• Expand: add detail by creating combinations of level values.

• Distribute: allocate values down to a more detailed level using a distribution key.

• Rebook: move values between members within the same level.

Common patterns

• Aggregate to a higher level → ROLLUP('Node', "Dimension", "Level")
  Use when you want values only at selected higher levels.

• Make one node match another node’s shape → ROLLUP_TO('Node', 'TargetShapeNode')
  Use when you want the first node aggregated to the second node’s level dimensionality.

• Remove unwanted granularity → DROPLEVEL('Node', "Level")
  Use when a node carries detail you don’t want in further calculations.

• Apply a value across a level → EXPAND('Node', "Level")
  Use when you need a value available at a more detailed level.

• Expand only to specific members → EXPANDSINGLE('Node', "Level", ["A", "B"])
  Use when you want targeted expansion without creating the full cross product.

• Create an empty “data room” for simulation → EXPAND(0, "Level1", "Level2")
  Use to initialize a node with the required dimensionality.

• Distribute totals down to detail using weights → DISTRIBUTE('OriginalNode', 'DistributionNode')
  Use when you want allocations that preserve totals but add detail.

• Move values between members → REBOOK('Node', "Level", "OldValue", "NewValue")
  Use when you need to reclassify values within the same level.

Functions in this category

Choosing between similar functions

• ROLLUP vs DROPLEVEL

  • Use ROLLUP when you want an explicit aggregation target (you choose which levels remain).

  • Use DROPLEVEL when you want to remove specific granularity (and anything finer) from a dimension.

• EXPAND vs EXPANDSINGLE

  • Use EXPAND when you want the full cross product for a level.

  • Use EXPANDSINGLE when you only want specific level values without generating everything.

• ROLLUP vs ROLLUP_TO

  • Use ROLLUP when you know the desired target levels.

  • Use ROLLUP_TO when the target shape is already represented by another node and you want to keep them aligned.

• DISTRIBUTE vs REDISTRIBUTE

  • Use DISTRIBUTE to allocate values down using a distribution key.

  • Use REDISTRIBUTE when allocation should happen only within defined clusters/groups.

• REBOOK vs DISTRIBUTE

  • Use REBOOK when you move values from one member to another (reclassification).

  • Use DISTRIBUTE/REDISTRIBUTE when you split totals across multiple members using weights.

Pitfalls & troubleshooting

• Result is too aggregated: check whether + / - aligned to shared dimensions; make the intended shape explicit with ROLLUP or ROLLUP_TO.

• Result is too granular / large: check whether * or / expanded across combined dimensions or whether an EXPAND introduced additional levels and thereby changed the totals.

• Result is empty after reshaping: confirm referenced levels/members exist and labels match exactly (spelling/case).

• Unexpected allocation results: verify the distribution key node has values at the target detail and that totals behave as expected after distribution.

• Element-wise adjustment needed: use ADDEACH rather than relying on + constant behavior.

Related sections

• Formula basics notation and evaluation rules that affect shape

• Operators alignment behavior and comparisons used inside formulas

• Filtering & data shaping subset selection and fill/pick helpers

• Troubleshooting guide wrong shape and missing values