1. Abstract
Over time, Board applications accumulate large volumes of historical data and obsolete sparse combinations, which can negatively impact cube size, performance, and maintainability. While standard clearing operations are commonly used to enforce data retention policies, they do not physically remove data structures from the database.
This article explains the differences between Clear Cube, Optimize, and Extract–Clear–Reload, and how they should be combined to implement an effective maintenance strategy. Special attention is given to the management of sparsity, which does not automatically clean obsolete combinations and therefore requires dedicated actions.
2. Context
During a Board application lifetime, when good retention policy are in place we expect to retain a minimum number of historical years of data and do a periodical clean of outdate data.
To prevent database growth and performance degradation, it is a best practice to implement periodic maintenance aligned with a data retention policy, typically removing older data on a yearly basis.
The most common approach is using Clear Cube with “Use Current Selection”, which safely clears data within a defined scope. However, this operation only sets values to zero and does not remove tuples in cubes or sparsity entries, requiring additional maintenance techniques for both cubes and sparsity.
As applications evolve, continuous data loads, updates, and partial clearing operations lead to:
- Growth of historical data
- Accumulation of zero-value tuples
- Persistence of obsolete sparse combinations
Regular cubes and sparsity maintenance activities became necessary when there is evidence that the cube structure itself has become inefficient. The strongest signals are oversized sparse structures, large gaps between theoretical and meaningful combinations, or cases where repeated clear operations have left behind too much structural baggage.
Cube size has increased sharply over time without corresponding business value.
Only a small portion of master data has transactional relevance, but the cube still carries the larger scope.
Performance degradation appears after months of maintenance activity rather than after one specific code change.
3. Content
Sparsity does not automatically remove obsolete combinations. Over time, combinations may become irrelevant, such as:
- Products no longer sold or inactive customers
- Combinations between them no longer valid (e.g., a customer not buying a product anymore)
Even if values are cleared, these combinations remain in the sparsity structure and continue to impact performance.
Implication:
- Clear Cube alone is insufficient
- Additional maintenance is required to control sparsity growth
3.1 Clear Cube (Use Current Selection)
The Clear Cube procedure step with the Use Current Selection option is the recommended approach for selective data removal.
Key behavior:
- Applies only to the current selection (e.g., specific year)
- Sets values to 0
- Does not remove tuples
Implications:
- Sparsity continues to store cleared combinations
- Cube size is not reduced
- Safe in high concurrency environments, avoiding write bottlenecks
This makes it suitable for retention policy implementation, but is insufficient for structural cleanup.
3.2 Optimize
The Optimize function operates at the physical storage level of the sparsity. In Board, sparsity is a shared structure across multiple cubes that stores the existing combinations of sparse entities and plays a key role in performance and storage optimization.
Key behavior:
- Reorganizes sparsity combinations
- Works within the shared sparsity structure
- Does not remove tuples from cubes or change data
Implications:
- Improves sparsity efficiency and performance
- Reduce sparsity cardinality
- Does eliminate obsolete combinations from sparsity
Optimize is therefore a technical optimization step, not a data-cleaning mechanism per se.
Attention: during the optimize function execution, the Data Model must be set into maintenance mode. Depending on the sparsity usage, the execution may take a few minutes or more. Always execute an unload Data Model from memory after the execution of the Optimize functionality on one of your sparsity.
Step-by-step:
- Activate maintenance mode
- Identify the sparsity to optimize
- Execute Optimize
- Unload data model from memory
3.3 Extract–Clear–Reload
The Extract–Clear–Reload approach rebuilds cube data through a process involving:
- Extract Cube to flat file
- Clear Cube (full clear)
- Reload data via Data Reader
Key behavior:
- Reload excludes records with value = 0
- Removes unnecessary tuples
- Operates at cube level
Implications:
- Reduces cube physical size on disk
- Cleans obsolete data combinations
- Does not affect the sparsity if shared with other cubes
- Rebuilds the cube content
This is the only approach that physically removes zero-value tuples and contributes to reducing the cube size.
An extract-Clear-Reload applied on all cubes will entirely rebuild the data model content and will have a larger effect not just reducing cube size, but also restructuring sparsity. With this global action we can combine benefits of both actions clear cube and optimize sparsity.
4. Conclusion
Maintaining optimal performance in Board applications requires more than periodic data clearing. While Clear Cube with Current Selection is essential for implementing retention policies safely, it does not reduce cube size or clean sparsity.
Because sparsity is a shared structure and does not self-maintain, obsolete combinations accumulate over time and must be addressed through additional processes.
Combined Maintenance Strategy
An effective maintenance process should combine:
- Clear Cube (Use Current Selection)
→ Enforce retention policy - Optimize
→ Improve storage efficiency - Extract–Clear–Reload
→ Remove zero-value tuples and clean cube data
This combination ensures both logical data cleanup and physical data reduction, while also addressing sparsity-related inefficiencies.
When to apply cubes and sparsity maintenance activities
- After large clearing operations where many values are set to zero
- When cube size is inflated by unused or zero-value tuples
- As part of periodic maintenance for high-volume cubes
A robust maintenance strategy should therefore:
- Regularly clear historical data using Clear Cube
- Improve sparsity efficiency with Optimize
- Periodically rebuild cubes using Extract–Clear–Reload to eliminate zero-value tuples
Only by combining these techniques can organizations ensure controlled database growth, efficient sparsity usage, and sustained application performance.
