Featuring: Eric Laurin is a Principal Solution Architect and Community Captain
In brief: Aggregate directly when moving data up a hierarchy. For large lift-downs that are slow with Extend, mapping the data off the hierarchy and back onto the detailed Entity can be a much faster option.
A Practical Performance Lesson
In a recent project, I ran into a familiar challenge: moving data down a large Entity hierarchy was taking longer than expected. The direct calculation, moving values one level down, took more than 20 minutes. I tried mapping the data to a standalone Entity that matched the lower level, then mapping it back to the hierarchy. That sequence finished in under a minute. It was a useful reminder that, when Extend becomes a bottleneck on a large dataset, changing the route the data takes can make a dramatic difference.
I wouldn’t use this pattern automatically for every lift-down. If the standard approach performs well, it may be the simplest choice. The key is to recognize when the calculation is the source of a performance bottleneck, then choose a pattern that fits the data volume and the result you need.
Choose the Method Based on Direction and Scale
Moving data up: aggregate directly
When moving values from lower levels to higher levels, use normal aggregation. Aggregation Dataflows can process multiple relevant Entities together, and Board uses the hierarchical relationships when aligning Cubes with different structures.
Moving data down: use Extend, unless scale makes it costly
A standard lift-down uses Extend on the detailed Entity and is generally handled Entity by Entity. For modest volumes, this is often the simplest and clearest solution. If a large lift-down is taking too long, a mapping pattern may avoid the expensive Extend operation.
The “Off the Hierarchy and Back on” Pattern
The pattern moves the values through a standalone or pseudo-replicated Entity that represents the detailed level. In practical terms:
- Copy the aggregated value to a standalone Entity at the target level using a mapping Cube.
- Remove the aggregated Entity from the intermediate result.
- Add the detailed Entity through a second mapping Cube.
- Remove the standalone Entity, leaving the values aligned to the detailed hierarchy.
This replaces the costly lift-down step with simpler JOIN Dataflows that do not use Extend. In the recent case, that change reduced processing time from more than 20 minutes to less than one minute.
A Few Checks Before You Change the Design
- Confirm the calculation is moving data down the hierarchy. Moving data up is an aggregation problem and should generally be handled directly.
- Check whether Extend is actually driving the delay, and whether the data volume is large enough to justify an alternate pattern.
- Validate the mapping at each step, including how the standalone Entity represents the target-level members and how the final result aligns to the hierarchy.
- Compare both runtime and output values before adopting the change.
Takeaway
Use direct aggregation to move data up. For data moving down, start with the standard lift-down when it performs well. When Extend becomes a bottleneck on a large dataset, mapping the values off the hierarchy and back onto it can turn a slow calculation into a much faster sequence of joins.