Improving InfoProvider and Query Performance with Aggregation and Design Choices

Explanation

Once a BW consultant understands where bottlenecks originate, the next skill is applying targeted techniques to improve InfoProvider storage efficiency and query response time. These techniques differ meaningfully between classic BW-on-anyDB, BW-on-HANA, and BW/4HANA, so it is essential to know which optimizations are still relevant in each deployment. In classic InfoCube-based models, compression (also called collapsing) merges request-based data into the compressed fact table, removing request IDs and reducing the number of records through summarization of identical dimension combinations. Uncompressed InfoCubes with many requests accumulate redundant data and slow both loads and queries because every query must scan across all uncompressed requests. Regular compression, run as part of a process chain after successful load validation, keeps fact tables lean. However, compression is generally irreversible for request-level rollback, so it should only run after data has been validated as correct downstream. Partitioning is another classic technique: dividing a large InfoCube's fact table by a time characteristic (such as fiscal period) allows the database, and later HANA, to prune partitions during query execution, scanning only relevant time ranges instead of the entire table. In on-premise anyDB environments, partitioning was often essential for large cubes; in HANA-based systems, partitioning still helps for very large data volumes and can assist with parallel processing during data loads, though the performance benefit is less dramatic than on traditional disk-based databases because HANA's column store already compresses and scans efficiently. Aggregates were a cornerstone technique in classic BW: pre-calculated, pre-aggregated summary tables built on frequently queried characteristic combinations, allowing the OLAP engine to answer certain queries from a much smaller pre-summarized table rather than scanning the full fact table. Building and maintaining aggregates required careful analysis of query usage statistics, and poorly chosen aggregates could add load overhead without improving query performance. In BW-on-HANA and especially BW/4HANA, aggregates and the BW Accelerator index-based acceleration are largely superseded by HANA's in-memory columnar engine and CompositeProvider-based Advanced DSOs, which are designed to be queried directly at high speed without separate aggregate tables. Consultants working in HANA-native landscapes should validate current guidance for their specific release rather than assuming aggregates remain the primary lever, since capability varies by version and deployment model. Query design itself heavily influences runtime regardless of underlying storage. Excessive free characteristics force the OLAP engine to consider many drill-down paths; deeply nested hierarchies with many levels increase calculation complexity, especially when combined with exception aggregation or complex calculated key figures using cell-level formulas. Query performance can often be improved by restricting default result sets, using appropriate query read modes (such as reading data only when navigation requires it versus reading everything at query start), and avoiding unnecessary use of very wide, unrestricted key figure structures. Monitoring is essential throughout: reviewing DTP and process chain runtime logs identifies load-side issues, while query runtime statistics and OLAP cache hit rates identify query-side issues. In production support, a consultant should never apply a technique like compression or aggregate rebuild without first confirming, through monitoring data, that it targets the actual bottleneck observed.

Code example

* Example ABAP snippet illustrating a BAD transformation pattern to avoid:* Row-by-row select inside a loop causes massive DB round-trips for large volumesLOOP AT source_package ASSIGNING FIELD-SYMBOL(<source_fields>).  SELECT SINGLE matnr, mtart FROM mara    INTO (lv_matnr, lv_mtart)    WHERE matnr = <source_fields>-matnr. " avoid per-record SELECTENDLOOP. * Preferred pattern: bulk select once, then loop for lookups in memorySELECT matnr, mtart FROM mara  INTO TABLE @DATA(lt_mara)  FOR ALL ENTRIES IN @source_package  WHERE matnr = @source_package-matnr. LOOP AT source_package ASSIGNING FIELD-SYMBOL(<source_fields>).  READ TABLE lt_mara WITH KEY matnr = <source_fields>-matnr    INTO DATA(ls_mara).  IF sy-subrc = 0.    " use ls_mara-mtart from in-memory lookup, not a fresh DB call  ENDIF.ENDLOOP.

Real project scenario

A manufacturing client's monthly finance InfoCube query took over three minutes to render in Analysis for Office. Investigation showed the InfoCube had not been compressed in over a year, leaving hundreds of uncompressed requests that the OLAP engine had to scan through for every query execution. After validating the data with the finance team, a compression step was added to the process chain immediately after successful DTP completion and reconciliation checks, reducing query response time to under fifteen seconds without any changes to the query design itself.

Common mistakes

• Compressing InfoCube requests before confirming the loaded data has passed reconciliation checks, losing the ability to roll back individual requests • Building aggregates based on assumptions about query usage rather than actual query statistics • Assuming aggregates or BW Accelerator concepts still apply the same way in BW/4HANA without checking current release behavior • Designing queries with unnecessary free characteristics and wide default result sets that force large unfiltered result sets at query start • Using per-record database lookups inside transformation routines instead of bulk selects • Partitioning a HANA-based InfoProvider without evaluating whether the data volume actually justifies it

Best practices

• Validate loaded data before running compression, since compressed requests generally cannot be selectively rolled back • Base aggregate design, where still applicable, on actual query runtime statistics rather than guesses • Re-evaluate whether classic techniques like aggregates and partitioning are still the right lever in HANA-native or BW/4HANA systems before applying them • Review query design for excessive free characteristics, wide structures, and inefficient exception aggregation as a first step in query tuning • Replace row-by-row database lookups in transformation routines with bulk selects and in-memory lookups • Maintain a documented performance baseline and re-test after each tuning change to confirm actual improvement

Interview angle

A frequent interview scenario is being asked to explain the difference between classic aggregate-based tuning and HANA-native performance approaches, and to demonstrate awareness that techniques valid in older BW-on-anyDB systems are not automatically appropriate or necessary in BW/4HANA. Candidates should be able to discuss compression, partitioning, and query design trade-offs with concrete reasoning rather than generic statements.