SAP BW Aggregates: A Pragmatic Guide to Query Performance in the HANA Era

Arjun Mehta cuts through the noise with 25 years of hands‑on BW tuning experience

I still remember the call—a finance controller furious that the monthly margin report took 22 minutes. This was 2005, a BW 3.5 system on Oracle. The fix was a single aggregate on just two characteristics and six key figures. Runtime dropped to 12 seconds. Back then, aggregates were our Swiss Army knife.

Today, with BW/4HANA and column‑store in‑memory engines, many assume aggregates are obsolete. That’s only half true. Badly managed aggregates still cause stale data and batch‑job sprawl, but entirely dismissing them can leave serious performance gaps in hybrid landscapes and legacy InfoCubes. Let’s examine what still works, what definitely doesn’t, and how to tell the difference.

The Real Story: Aggregates Are Not Dead, Just Dangerous

An aggregate is a materialized, pre‑summarized subset of an InfoCube. You choose a combination of characteristics (like Month, Region, Product Group) and BW builds a physical table that stores the sums of key figures at that granularity. When a query groups by those exact dimensions, the OLAP processor reads the small aggregate instead of scanning millions of fact rows.

That sounds simple, but the devil is in the synchronization. If you don’t run a rollup after each data load, the aggregate still reflects old numbers—and your query will silently deliver wrong results. I’ve seen audit issues arise because a support team forgot to schedule the rollup step for a weekly load. Worse, aggregates that are never compressed gradually swell with open request IDs, consuming disk space and slowing down the rollup itself.

Transaction RSDDV shows all aggregates defined on a Cube. You can test an aggregate’s effect using the query monitor (RSRT): execute the query, open the “Aggregates Used” tab or trace execution statistics. A query might hit multiple aggregates—or none—and the difference can be staggering. Yet in many systems I audit, 30–40% of aggregates have zero hits over the last quarter; they exist only because someone once ran a proposal and approved everything. That’s pure overhead.

To keep your house clean, use RSRV (the BW Analysis and Repair tool). It can check for inconsistencies between aggregate data and the base Cube—vital after transport replications or failed rollups. Also, table RSDDSTATAGGR (part of BW statistics) logs aggregate usage counts. A monthly job that flags unused aggregates will save you headaches during upgrades.

What This Means for You: Concrete Examples for Different Roles

For Basis Administrators and BW Operations:
In a classic BW system, add a process chain with an “Execute Aggregates Rollup” step immediately after the data load. On large Cubes, use the “Rollup with Time Window” variant to avoid locking issues. Then schedule a separate weekly job to compress the aggregate (process type “Collapse Aggregates”). This keeps the aggregate lean without impacting daytime queries.

For Analysts and Developers:
Don’t create aggregates blindly. Use transaction RSDDV to generate a proposal based on query statistics, but prune it ruthlessly. I usually keep only those characteristic combinations that cover at least three top‑used queries and reduce the number of rows by an order of magnitude. Adding too many characteristics forces the aggregate to approach the Cube size, yielding no benefit while multiplying loading times.

Consider this real‑world scenario from a manufacturing client: a Sales Cube with 400 million records. Queries drilling down by Customer and Material ran fine because those dimensions were already indexed. The killer was a Year‑To‑Date margin report that grouped only by Fiscal Year and Company Code. A single aggregate on those two characteristics, with *

References

• SAP Performance Tuning || 1. Aggregates
• SAP HANA Platform Overview- SAP Community Hub

References

• SAP HANA Platform Overview
• SAP Community Hub
• SAP Performance Tuning || 1. Aggregates