Optimizing InfoProvider Design and Query Execution for Better Performance
A practical look at how InfoProvider modeling choices—partitioning, compression, aggregates versus HANA-native aggregation, and CompositeProvider design—affect query and load performance, with concrete tuning techniques.
Explanation
Once a consultant can identify whether a performance issue lives in load, storage, or query execution, the next skill is knowing the concrete modeling and configuration levers available to address each. This lesson focuses on the storage and query layer, since this is where most durable, structural performance improvements come from. Partitioning is one of the most effective techniques for large InfoProviders. In classic InfoCubes, database-level partitioning (typically by a time characteristic such as fiscal year or period) allows the database to prune irrelevant partitions during query execution, so a query filtered to the current year does not scan five years of historical data. In ADSOs and BW/4HANA, similar partitioning concepts apply at the HANA table level. The key design decision is choosing a partitioning characteristic with enough cardinality to create meaningful splits, but not so many partitions that management overhead increases. A common mistake is partitioning by a characteristic that queries rarely filter on, which provides no pruning benefit. Compression (or in ADSO terms, activation and the collapse of change logs) matters because uncompressed InfoCube data is stored as a sequence of requests, and every additional uncompressed request adds a join condition the OLAP engine must resolve at query time. Over months of daily loads without compression, query performance degrades steadily and mysteriously, since no single load caused it—the cumulative request count did. Establishing a regular compression job in the process chain, run only after data has been validated (since compression makes request-level deletion harder), is a standard best practice. Aggregates were the classic answer to slow queries on disk-based BW: pre-calculated, smaller summary tables that the OLAP engine could substitute transparently when a query's drill-down matched the aggregate's characteristics. Building the right aggregates required analyzing query navigation patterns (via query statistics) to find common drill-down combinations, then balancing aggregate build time and storage cost against query speedup. In HANA-optimized and BW/4HANA environments, the in-memory columnar store often makes traditional aggregates unnecessary for many InfoProviders, since HANA can aggregate large fact tables on the fly with acceptable performance; however, complex CompositeProviders that union or join multiple large providers can still benefit from denormalized or pre-joined structures, and some very high-volume scenarios still use techniques like calculated columns or SAP HANA views to push logic closer to the data. CompositeProvider design deserves particular attention because it is a common source of hidden performance cost. Combining many ADSOs via UNION is generally cheap, since HANA can process unions efficiently, but combining providers via JOIN introduces the risk of expensive runtime joins, especially if join cardinality is high or join fields are not well-indexed. Consultants should minimize the number of providers joined at query time and favor pre-joining data into a physical ADSO during the transformation/DTP phase when the join is stable and reusable across many queries, rather than repeating the join cost for every query execution. Query-level tuning complements provider design: minimizing the number of free characteristics in the initial view, avoiding unnecessarily wide date ranges, using query-level filters (fixed values) instead of variables when the scope is genuinely static, and being cautious with cell-level formulas and non-cumulative key figures, which can force cell-by-cell calculation instead of set-based processing. Reviewing query runtime statistics (captured in BW's statistics InfoProviders) helps identify which specific queries are consistently expensive and whether the cost is in database access, OLAP processing, or front-end formatting.
Real project scenario
A manufacturing client's BW/4HANA system had a CompositeProvider joining a large sales ADSO with a slowly changing master data ADSO at query runtime for every report execution, because the original design avoided pre-joining to 'stay flexible.' As transaction volume grew past several hundred million records, join-time performance degraded from 3 seconds to over 45 seconds per query. The remediation involved restructuring the transformation to denormalize the relevant master data attributes directly into the sales ADSO during the load, eliminating the runtime join for the most frequently used queries while keeping a separate flexible CompositeProvider for ad hoc analysis that genuinely needed the join.
Common mistakes
• Building CompositeProvider joins for convenience without checking join cardinality or data volume growth trends. • Leaving InfoCube compression disabled or irregularly scheduled, causing gradual, hard-to-diagnose query slowdowns. • Creating aggregates based on assumptions rather than actual query navigation statistics, wasting build time and storage. • Using wide-open date range variables in queries by default instead of encouraging users to filter to relevant periods. • Assuming HANA's speed eliminates the need for good modeling, leading to unnecessarily complex CompositeProviders that still underperform at scale.
Best practices
• Prefer UNION over JOIN in CompositeProviders wherever the business logic allows it. • Schedule InfoCube compression regularly as part of the process chain, after validating load quality. • Base aggregate design on actual query statistics, not assumptions about usage patterns. • Denormalize stable, frequently joined master data into the transactional ADSO when the join is expensive and repeated across many queries. • Periodically review query runtime statistics to catch gradual performance degradation before it becomes a user complaint. • Educate report designers on the performance cost of wide-open filters and excessive free characteristics in initial views.
Interview angle
A frequent scenario-based question is: 'A CompositeProvider query has become slow as data volume grew—how would you investigate and fix it?' Strong candidates mention checking whether the CompositeProvider uses JOIN versus UNION, reviewing join cardinality, checking query runtime statistics to isolate database versus OLAP time, and considering whether denormalizing into a physical ADSO would remove a repeated runtime cost. This demonstrates both diagnostic method and knowledge of concrete remediation options rather than generic 'add an index' answers.