SAP NetWeaver BI Accelerator, available with SAP NetWeaver BI 7.0 and later, can reduce reliance on aggregates and improves how the system reads data, resulting in faster query performance. Find out which scenarios work best with the accelerator and learn which tasks you should carry out for the best possible accelerator performance.
Key Concept
The SAP NetWeaver BI Accelerator helps improve SAP NetWeaver BI query performance with little to no query tuning, aggregate building, or impact on users. The accelerator generally produces a query runtime speed-up factor of between 10 and 100 times. Although the accelerator can be delivered only in hardware and software configurations certified by SAP, there are no dependencies with hardware, database vendors, or the SAP NetWeaver BI operating system to which the accelerator is connected.
As InfoCube volume grows or when dimension complexities increase (e.g., navigation attributes, time dimensions, or multiple hierarchies), the data access performance of BI queries starts to degrade rapidly. Performance challenges can prevent companies from meeting service levels related to query performance. In such situations, you can consider using SAP NetWeaver BI Accelerator (the accelerator). Better performance and more stable response times lead to increased user productivity. Furthermore, you reduce the total cost of operations (TCO) by avoiding aggregate maintenance, consolidating currently overloaded SAP NetWeaver BI systems, and simplifying data modeling, for example.
What follows is a series of tips and techniques to optimize SAP NetWeaver BI query performance with the accelerator. We will provide advice for planning, implementing, and maintaining SAP NetWeaver BI Accelerator and for troubleshooting performance issues. We offer lessons learned and undocumented tips to optimize accelerator performance, including how to avoid overload. We will also cover some of the unexpected accelerator issues that you may not find until testing or after the accelerator is live in your productive system. If you are unfamiliar with the accelerator terminology, you should consult the “Important SAP NetWeaver BI Accelerator Terms” sidebar.
Note
The techniques described apply to SAP NetWeaver BI 7.0 and SAP NetWeaver BI Accelerator (revision 49).
Important SAP NetWeaver BI Accelerator Terms
SAP NetWeaver BI Accelerator index: This index contains all the data for an InfoCube in a compressed, but not aggregated, form. The accelerator index stores the data at the same level of granularity as the InfoCube. As with SAP NetWeaver BI aggregates, an accelerator index is a redundant downstream data store that you can use to improve query performance and lower administration effort. It is especially useful for sophisticated scenarios with unpredictable query types, high data volumes, and frequent queries.
Indexing: This is the transfer of InfoCube data to the accelerator server including the processing and compression of the data into the accelerator index. The indexing process is similar to the aggregate creation process and consists of index creation, activation, and filling. During index creation, the system defines the indexes for the InfoCube star schema tables on the accelerator server. During the index filling process, the system reads data in the InfoCube star schema tables from the database and writes it to the corresponding indexes on the accelerator server.
Accelerator revision: The maintenance and support process of the accelerator is organized in the same manner as other SAP products. SAP NetWeaver BI Accelerator 7.0, revision 49 is the current version for the accelerator.
TREX: This is the SAP search and classification engine. The accelerator is built using TREX technology (e.g., indexing, retrieving, and compression). Note that SAP NetWeaver BI Accelerator and TREX are two different installations.
When You Can Use the Accelerator
Besides BEx, the SAP NetWeaver BI tools and interfaces that use the accelerator currently include Online Analytical Processing (OLAP) interfaces: XMLA, ODBO, OLAP BAPI, LISTCUBE, Application Process Designer (APD), and custom implementations based on the RSDRI_INFOPROV_READ API. All certified third-party front-end tools using published SAP interfaces also benefit from the accelerator because all front-end tools communicate with the analytical engine (OLAP), which determines if data is read from the accelerator or database tables.
You can use the accelerator in most cases in which you can use aggregates (we discuss the exceptions in the next section):
InfoCubes. The current version of the accelerator can only leverage SAP NetWeaver BI InfoCubes as a source of data.
Real-time InfoCubes. For queries based on real-time InfoCubes, the system has to read data in open requests from the database but can index data in closed requests into the accelerator. Consequently, the accelerator helps the performance of planning queries and layouts for Strategic Enterprise Management-Business Planning and Simulation (SEM-BPS), BW-BPS, SAP NetWeaver BI Integrated Planning, as well as for the SAP NetWeaver version of SAP Business Planning and Consolidation (SAP BPC). Generally, the accelerator improves the data selection time component of planning functions.
MultiProviders. Any InfoCube as part of the MultiProvider that is indexed in the accelerator uses the accelerator. In general, the slowest InfoProvider in a MultiProvider environment defines query performance. Note that parallel execution of MultiProvider queries may be limited in some cases, such as by the number of free work processes.
Virtual InfoProviders. Virtual InfoProviders based on InfoCubes can benefit from the accelerator — for example, balance sheet reporting scenarios in Financial Accounting or SAP BPC. For more information refer to SAP Note 1027176 (“Restricted support of BIA index in BCS reporting”).
Query value help. If you set the query execution filter value selection to Q (“Only posted value for navigation for a characteristic”), then the value help uses the data manager during query execution. This means that it also uses the accelerator (similar to how it uses aggregates). The value help also uses the accelerator (similar to how it would have used aggregates) if applicable for retrieving the surrogate IDs (SIDs) — texts are read from the database. For other filter value selection settings, the system does not use the accelerator.
When You Should Avoid the Accelerator
The following are cases in which you cannot use the accelerator or where results may differ:
Master data. Master data queries (for InfoObjects defined by InfoProviders) do not use the accelerator. A common misconception is that the accelerator contains all master data values. The reality is that you cannot use the accelerator for master data selection because only SIDs are indexed. For master data queries, you would need to index all master data values in the accelerator, too. The text tables are missing in SAP NetWeaver BI Accelerator 7.0 as well.
Sources of data other than InfoCubes. The accelerator does not currently work with sources of data other than InfoCubes. You cannot create accelerator indexes for DataStore Objects (DSOs), Open Hub Destinations, InfoSets, Virtual Providers, and InfoObjects as InfoProviders (flexible master data), for example.
Zero records elimination. When InfoCube fact tables are compressed with "zero records elimination," running queries in conjunction with the accelerator could lead to different or unexpected query results compared to database query results.
InfoSets. You cannot use the accelerator with InfoSets because they generate a SQL statement.
Virtual key figures. The accelerator may lead to different results when used for queries that contain virtual key figures that are not well defined. For more information and examples, refer to SAP Note 1143411 (“BIA, virtual key figures and characteristics”). If you want to deactivate the accelerator for queries with virtual key figures, refer to SAP Note 1149760 (“Do not use BIA index for queries with virtual key figures”).
Benefits and Guidelines
Runtime performance is divided into four areas for SAP NetWeaver BI queries, as shown in Table 1 and Figure 1.
Initialization time
|
Portion of the query runtime spent in reading metadata, compiling/re-compiling query, checking query access authorization
|
Data read time |
Portion of the query runtime spent in reading and aggregating data from the database or from the accelerator |
OLAP time |
Portion of the query runtime spent in calculations and conversions, display hierarchy, and security checks, for example. Such operations are performed on SAP NetWeaver BI within the analytic engine (ABAP), not within the accelerator. |
Network time |
Portion of the query runtime transferring the result set via the network to the client (user PC) |
Front-end time |
Portion of the query runtime bringing data to the user workstation and formatting the report |
|
Table 1 |
SAP NetWeaver BI query runtime performance areas |

Figure 1
SAP NetWeaver BI Accelerator impact on query performance areas
The accelerator selects data from memory and helps improve data read time. The accelerator benefits query runtimes as follows:
- Highest performance impact on read (and aggregation) time
- Little impact on calculation (OLAP). SAP plans to address this area in future SAP NetWeaver BI Accelerator releases. In the current release, the interface between OLAP and the accelerator returns additional information that allows the OLAP engine to create the result grid (rows and columns structures) more efficiently compared to records returned by the database interface when the accelerator is not involved.
- No acceleration of initialization, network, or front-end time
It is important to set the right expectations when implementing the accelerator. Most queries will experience great runtime performance gains with the accelerator. However, the current release of the accelerator does not improve the runtime of every query because it is targeted explicitly at speeding up data selection and aggregation at this time. There might even be certain scenarios in which the accelerator does not boost query performance significantly compared to the traditional performance tuning measures, such as aggregates created specifically for a single query. You should use and position the accelerator based on its intent — for example, queries with high database time, not with high OLAP or front-end times. Figure 2 compares when it's best to use the accelerator versus when not to use the accelerator.

Figure 2
SAP NetWeaver BI Accelerator query performance improvement guidelines
Note
The information in number 4 of Figure 2 (Transferred amount of data > 500,000 records) assumes a one Gbit/sec connection between SAP NetWeaver BI and the accelerator — transferring 500,000 records of 1,000 bytes would take about five seconds. Because performance depends on record size, which in turn varies with the drill down, it is better to check table RSDDSTATTREXSERV for high ABAP_RFC_TIMES due to high data volume. The KBYTES column is filled correctly after implementing SAP Note 1083285 (“Field KBYTES in table RSDDSTATTREXSERV is incorrect”).
As an added benefit, the accelerator also contributes to reducing the cost of administrative operations (e.g., aggregate design, data latency, performance tuning, and database use) for virtually all scenarios. Lastly, the accelerator contributes to reducing data load time — such as roll-up and change runtimes, which requires deactivating or deleting aggregates that are no longer needed after you implement the accelerator.
Identifying Candidate InfoCubes and Queries
The ideal accelerator candidates are those queries with high data read/data manager time and large aggregation (i.e., high ratio of selected records/ transferred records). To find the queries that are best suited for the accelerator, use one of the four tools in Table 2, which are listed by ease of use.
1. BI Statistics queries |
Execute queries on technical content queries (statistics InfoCubes) to determine the query performance. For example, query 0TCT_MC03/0TCT_MC03_Q0200 requires setting of BI Statistics data collection to “All” and loading BI Statistics InfoCubes. |
2. Workload monitor |
Use the workload monitor (transaction ST03N) to review the statistics captured for any period of time |
3. BI Statistics view |
Review statistics view RSDDSTAT_OLAP to identify those queries with a high aggregation ratio. Select events 9000, 9010, and 9011 to obtain statistics for the data manager time, number of selected records, and number of transferred records |
4. Query monitor |
Execute and debug queries in transaction RSRT. Capture statistics to identify queries with high data manager time. You can also see the time from other components, such as Remote Function Calls (RFCs). |
|
Table 2 |
Tools to identify accelerator query candidates |
Figure 3 provides an example of query runtime using the query monitor (transaction RSRT). The key to using this transaction is to choose Execute and Debug with Debug Options: "Display Statistics Data," "Do Not Use Cache," and "Do Not Use BI Accelerator."

Figure 3
Statistics data for query runtime in the query monitor (transaction RSRT)
Depending on what you want to compare, you may also leave aggregates on for a general check that corresponds to the current user experience. The exception would be if you wanted to compare against a full InfoCube selection (e.g., identify queries that would have had high database time if no aggregates were present, such as with the same query but different drill downs). The example in Figure 3 shows a good query candidate for the accelerator because the read time (data manager) makes up over 80 percent of the total query runtime (e.g., 26.8 out of 32.75 seconds, taking into account the user wait time [selection input]). The accelerator should help alleviate most of the read time for this query.
Data Modeling Tips and Techniques
The accelerator integrates seamlessly into existing SAP NetWeaver BI environments and does not require changes to the existing data model (such as InfoProviders or DataSources). Although some modeling guidelines might become less relevant once the accelerator is in place, SAP recommends continuing to follow best practices for data modeling and data management to achieve optimal accelerator query performance.
If modeling or remodeling is an option when implementing the accelerator, the key data modeling considerations for the accelerator include:
Logical partitioning. Use logical partitioning of the InfoProvider to ensure the accelerator has smaller InfoProviders and subsets of data with which to work. Logical partitioning generally results in improved memory handling for the accelerator. For example, Figure 4 illustrates a scenario with logically partitioned InfoProviders by year, but only the two most recent years are frequently queried. For this scenario, you may want to index only the two most recently queried InfoProviders in the accelerator and create aggregates for the other six InfoProviders/years.

Figure 4
Logically partitioned InfoProviders
Keep in mind that maintaining aggregates has a cost and can affect the master data change run. Data partitioning should match the most frequent data selections. You should avoid splitting data into too many base providers — for example, if you want all of the base InfoCubes in the accelerator, it can generate too many sub-selections that require aggregation from the base InfoCubes in the OLAP engine.
DSO design. If applicable, use write-optimized DSOs as the initial data warehouse and inbound layer (as opposed to standard DSOs that are reporting performance optimized). Using write-optimized DSOs removes the activation time from the data flow. This design also positions the accelerator for greater use with the next release of SAP NetWeaver BI in which SAP plans to enable the accelerator for DSOs. This highly anticipated release should provide the capability to load data from a DSO directly to the accelerator, without the need for persistent data in an InfoCube (called the HybridProvider).
Data and Cache Management Tasks
The accelerator supplements but does not replace traditional SAP NetWeaver BI performance optimization methods — such as aggregates, query caching strategies, and information broadcasting. The accelerator may be superior to other performance optimization approaches in many scenarios, but it is not mandatory to replace the current SAP NetWeaver BI setup and migrate to the accelerator.
Guidelines and best practices for data warehouse design are still applicable and should be followed even with the accelerator. However, data needed for SAP NetWeaver BI queries are processed from the accelerator's memory rather than from database tables, as it is in the case of aggregates and InfoProviders. Therefore database-related performance optimization techniques become less relevant with the accelerator (Figure 5). Keep in mind that you need a backup plan when the accelerator is down or for queries on InfoCubes that are not indexed in the accelerator. For example, you may need aggregates and compressed InfoProviders with up-to-date database statistics whenever the accelerator indexes are switched off.

Figure 5
SAP NetWeaver BI query execution sequence
Minimal performance tuning efforts are required with the accelerator:
Aggregates. Aggregates are not obsolete with the accelerator because it is possible to have active aggregates and an accelerator index for the same InfoCube. However, aggregates are not needed for a given InfoCube if the InfoCube is indexed in the accelerator because queries that use accelerator indexes bypass the database and aggregates.
It is best to deactivate as many aggregates as possible when you are comfortable with the accelerator (e.g., large, unused aggregates). Fewer aggregates result in faster data load processes (aggregate change runs and roll-up) and gains in database space. Most organizations drop, or at least deactivate, all aggregates after a few weeks of going live with the accelerator.
The aggregates to keep after the accelerator go-live include:
- Aggregates are still needed for InfoCubes that are not indexed within the accelerator. However, you should replace aggregates for InfoCubes that are indexed in the accelerator.
- Keep some of the aggregates as backup in case of accelerator failure, until issues are resolved. If the accelerator is down, SAP NetWeaver BI uses aggregates (provided that one or more aggregates are applicable to the users' queries), resulting in better query performance compared to reading data from the InfoCube.
- Some organizations keep Basis aggregates in the beginning. These aggregates do not contain navigational attributes or hierarchy levels and consequently do not affect the change run.
Compression. In the context of the accelerator, there's normally no difference between compressed and uncompressed (E and F) fact table handling. InfoProvider compression is only necessary for the InfoProviders with the following features:
- High number of cancellation requests (eliminate many records with zero values)
- Non-cumulative key figures (InfoProviders with inventory data)
- Significant number of requests (for request maintenance performance)
- High number of partitions in the F fact table
- Real-time InfoCubes with slow B-tree database indexes (depends on database platform)
- In case of uneven data distribution in load requests, compression may improve performance of initial indexing of data in the accelerator
Database indexes. Database indexes for the InfoProviders are less relevant when you use the accelerator. If you use your InfoCubes purely for reporting and do not run compression jobs, you could even completely forgo creating secondary database indexes.
Scenarios that still require secondary database indexes include:
- Initial filling of accelerator indexes
- Open hub extractions
- Data marts for transferring data from InfoCube to another InfoProvider
- InfoSet queries
- Queries that use "Request ID" as a characteristic (this usually is an IT scenario, not a business requirement)
- When the accelerator is deactivated for the query containing virtual InfoObjects
- In SAP BW 3.x queries that still have calculated key figures with the "Calculate before Aggregation" property turned on
- Real-time InfoCubes (at a minimum the open request will be read from the database)
- Consistency checks that compare data between an InfoCube and the accelerator (especially the random queries) in transaction RSRV or RSDDBIAMON (data consistency check center)
Data archiving. Keep in mind that it is important to manage the BI data and periodically archive or delete data that is no longer needed, such as unused plan data for historical years. It is a critical process for the InfoProviders with the largest number of records and critical for reporting if your BI environment has large data volumes and data growth. Data archiving results in reduced data volumes and optimized query performance, while reducing the accelerator index creation time and probability of accelerator overload. You can also consider using near-line storage solutions as an alternative for archiving. When you perform data archiving on InfoCubes with accelerator indexes, SAP recommends that you perform a full rebuild of the accelerator indexes involved after the archiving is complete.
OLAP Cache. After your accelerator implementation, you do not need to switch off the OLAP Cache. SAP recommends keeping the OLAP Cache switched on for InfoProviders with accelerator indexes unless read time from the OLAP Cache is similar or longer compared to reading directly from the accelerator or if users are performing mostly ad-hoc queries (selected data not in the OLAP Cache). The OLAP Cache settings should be the same as for InfoProviders with aggregates. Using OLAP Cache takes the load off the accelerator and contributes to its high scalability. By design, SAP NetWeaver BI query results based on InfoProviders indexed in the accelerator are retrieved from pre-calculations and OLAP Cache (if applicable) before going to the accelerator. Using OLAP Cache is especially important if your queries depend on OLAP-intensive calculations such as exception aggregation or currency conversions.
Other processes. The accelerator removes the workload from the database and therefore minimizes the likelihood of other processes such as data loads competing for the same database resources. Note that running master or transaction data loads in parallel to the accelerator indexing jobs might create a performance issue. However, if process chains are set up properly, then the accelerator indexing tasks (roll-ups) should always happen after completion of the actual data loads.
Front-End Tips and Techniques
The accelerator is transparent in terms of BI queries and its introduction into the SAP NetWeaver BI system does not require any changes to the existing queries. In the same manner that you should follow data model best practices, SAP recommends that you follow front-end tuning and best practices for optimal query performance (whether you use the accelerator or not). These include:
Query monitoring. After you implement the accelerator, it is important to continue to monitor and analyze the accelerator and query statistics periodically using the tools such as BI Statistics queries, the standard workload monitor (transactions ST03 and RSRV), performance tests, and accelerator system checks. Additional tuning efforts should concentrate on queries that are not achieving significant performance gains with the accelerator.
InfoCubes with detailed information. When an InfoCube contains very granular or detailed data, the accelerator performance varies depending on the data model design and size of the accelerator (in general, the more blades, the better the accelerator performance). You should test this scenario thoroughly to ensure adequate performance. It is critical to design such queries to use mandatory filtering to mitigate runaway queries that retrieve vast amounts of data.
Query complexity. Reduce complex calculations to a minimum. For example, split several calculations into several different queries, keep the query result sets realistic, and use multi-dimensional navigation to drill down to details rather than having a large initial report.
OLAP-intensive operations. Minimize the use of OLAP-intensive operations such as sorting, formula calculation before aggregation, conditions such as top-n or bottom-n, zero suppression in query properties, and conditions-and-exceptions reporting.
Prioritize requirements. Prioritize the accelerator requirements for important and frequently used InfoCubes. Unless the accelerator is sized accordingly, do not use it for all InfoProviders to avoid unnecessary load to the accelerator. For instance, you can disable the accelerator for low-priority InfoProviders or complex queries (see SAP Note 1093719 "BIA capacity utilization: Preventing overloading").
APD. You may also want to disable the accelerator for APD because APD queries can be very large and could result in a negative impact to the SAP NetWeaver BI system and the accelerator performance. Disabling APD should not be an issue because APD queries are typically executed in background and APD performance has lower priority to the BI users compared to BI query performance. For more information, refer to SAP Note 1231794 (“APD: Using the BIA to read InfoProvider data”).
Information broadcasting. You can increase perceived query speed by broadcasting the results of commonly used queries to the OLAP Cache, your portal (SAP or non-SAP), or directly via email to your end users.
Report-Report-Interface (RRI). Because the accelerator enables reporting on detailed InfoProviders, you do not need to use RRI as often and less end user training is required for this functionality.
Query read mode. Select the right read mode for your queries. For queries involving large hierarchies with many nodes, you should select the option "Read data during navigation and when expanding the hierarchy" to avoid reading data for the hierarchy nodes that are not expanded.
User population. The accelerator is for everyone, but you might consider deploying the accelerator queries to the most knowledgeable users to avoid uneducated or unnecessary drain on queries and the accelerator. Also consider providing training and education to your BI query users and developers in the area of optimal query design and use for best performance.
Additional Tips and Techniques for Reading High Data Volumes
Additional performance considerations are required when reading high data volumes within the accelerator. In such scenarios, a high number of records is transferred, which results in high processing time within the SAP NetWeaver BI analytic engine and a high network load between the accelerator and SAP NetWeaver BI. Possible causes include:
- Wide record length attributed to hidden key figures and free characteristics
- Queries containing all characteristics in the data model
- Data dumps without filter restrictions
When reading large data values, SAP recommends the following:
- Limit the query to only those key figures and characteristics required for the output
- Set the "Use Selection of Structure Elements" property for query with hidden key figures (you can set this property in transaction RSRT)
- Activate Formula Element Selections (FEMS) compression. This intelligent compression schema eliminates redundant data in query results on the accelerator side and reduces query data transfer costs.
- Reduce the accelerator query size for complex cases
- Implement the SAP Note or corresponding Support Package to enhance the interface between the data manager and OLAP processor
- For non-cumulative key figures, use variable selection criteria to limit the data being queried; limit the validity table size; compress InfoCubes; and re-index periodically
- Activate "Package-Wise Read" to ensure all data can be read (without memory overflow). This setting does not make performance faster but puts the accelerator data to be read by BI into packages of configurable sizes, streamlining the transfer of data.
- For queries that dump large data volumes and require no further drill down, consider using a DSO (with database indexes on the filtered columns) rather than an InfoCube (with the accelerator index). Unlike a query executed against an InfoCube, DSOs do not perform joins at query runtime.
Table 3 highlights recommended SAP Notes for large data volumes.
1018798 |
Reading large data volumes from BI accelerator |
1118425 |
BIA 7.00: Activate performance feature FEMS compression |
1074953 |
FEMS, BIA optimization |
1085745 |
Reduction of BIA query size in complex cases |
1091714 |
Dynamic DATA table during reading of data |
1160520 |
BIA and non-cumulative queries |
1157582 |
BIA 7.00: New feature "package wise read" |
1002839 |
BIA 7.00: Error due to huge resultset of query (revision 48 and higher) |
|
Table 3 |
SAP Notes to review when reading high data volumes from the accelerator |
Further considerations are needed when using the accelerator in conjunction with inverted data models (data models with large master data tables). When master data tables are extremely large (often larger than fact tables) then large tables are joined at query runtime. If there are no filters set in the queries, (hundreds of millions of) SIDs and dimension IDs (DIMIDs) are joined, which is typically expensive — regardless if the accelerator is used or not.
Figure 6 shows the standard star schema data model. Compare this to the inverted star schema data model in Figure 7. In the inverted star schema, master data tables are extremely large (often larger than fact tables) due to relatively large SID or dimension tables (compared to the fact table). Such data models can lead to performance issues if there are no or very few selective restrictions on the large SID and dimension tables.

Figure 6
Standard SAP NetWeaver BI star schema data model

Figure 7
Inverted star schema data model
In the case of large master data tables, you should take the following factors into account:
- Avoid “unselective joins” by using filters in queries
- If possible, redesign the data model to decrease the size of master data tables
- Use flat accelerator indexes as described in SAP Note 1047527 “ ‘Flat' BIA index: Indexing performance with line items”
- Delete unused master data
- Run transaction RSRV checks to clean up dimension data
- Use the dimension function index (DFI) for the accelerator when it becomes available (expected with SAP NetWeaver BI 7.2). For more information, refer to SAP Note 1074559 (“Dimension function index - support in the BIA”).
SAP NetWeaver BI Accelerator Administration
For optimal accelerator performance, ensure that you have successfully met all the prerequisites:
Maintenance. Apply the latest SAP Notes relevant to the accelerator and implement the latest accelerator revision and BI Support Package. You can find more information about this at www.service.sap.com/sp-stacks.
Sizing. Deploy adequate accelerator sizing. As a rule of thumb, sizing of the blades should be based on 50 percent memory use. Note that in certain cases, such as high user activity and large data requests, the accelerator memory can be consumed more than usual. If you load too much data (indexes) into the accelerator, then performance suffers because there is not enough memory to build temporary indexes (e.g., for hierarchy node selections) and to process SAP NetWeaver BI query requests. The higher the number of blades and the more widely distributed the data, the more heavily the system uses parallel processing. Generally, more blades mean faster queries. Also remember to plan for at least one backup blade and to resize when additional data/InfoProviders are indexed or more users are running queries against the accelerator. For more information refer to SAP Note 917803 (“Estimating the memory consumption of a BIA index”).
Network. A sufficient network and low number of hops are required for optimum accelerator performance. The network speed between SAP NetWeaver BI and the accelerator must be one Gbit/sec or faster. SAP also recommends that the accelerator blades be in the same subnet as the SAP NetWeaver BI system and that this subnet be used exclusively for the accelerator.
Basis optimization. Basis optimization of both SAP NetWeaver BI and the accelerator is critical for optimal query performance (but not in the scope for this article). For example, load balancing helps to leverage all database and application servers rather than having system bottlenecks during initial indexing and roll-ups of data.
Here are some best performance tips and techniques with regard to the accelerator index and memory management:
Reorganizations. Execute or schedule periodic reorganizations of the accelerator indexes as needed (provided data distribution is out of balance or if you have changes in the accelerator parameters). Reorganization optimizes the accelerator index redistribution across all the accelerator blades.
Index rebuild. In certain cases, you should delete and rebuild the accelerator index for an InfoCube on a regular basis (e.g., once a month) to ensure good reporting performance. To automate this task, you can use the process type "Initial Activation and Filling of BIA Indexes" in your process chains. This rebuild is important to ensure optimal query performance, especially if you delete requests from an InfoCube or compress the data with zero elimination in an InfoCube on a regular basis. If you compress or delete many requests over time, you will find a growing discrepancy between the size of the accelerator fact index and the InfoCube fact table. This ultimately affects query performance (but not query results).
If a request is compressed, this action has no impact on the fact table index on the accelerator because only one joint index exists for the E and F fact tables. If a request is deleted out of an InfoCube with an accelerator index, the data in the fact index is not deleted. Instead, the ID of the deleted request is removed from the index of the package dimension (the ID is actually replaced with some unused value).
Optimize schedule. Minimize accelerator index back-end operations during peak reporting periods. For example, during reorganization and index creation/roll-up, the accelerator index is not available and queries on that InfoProvider are executed from database or aggregates.
Delta indexes. You can use delta accelerator indexes to minimize the time for merging new data into the accelerator index with each data load. You should use these indexes only for frequent data loads with small requests (relative to InfoCube size) — for example, do not use these indexes for InfoCubes with full loads.
Do not let these delta indexes get too large and make sure to merge them often, especially after large or frequent data loads. If a delta index is very large, the accelerator index and query performance might not be optimal. The SAP NetWeaver BI Accelerator Monitor provides a warning during the index check when a delta index is more than 10 percent of the InfoProvider in size. You can adjust this threshold in transaction RSRV.
Memory use. The accelerator needs memory for query processing. For the queries to pull all that data together and give you a result set, they require memory as well. If the accelerator is sized to only hold the data and indexes, there will not be enough room left to run queries. SAP's rule-of-thumb is that data should not consume more than 50 percent of the memory on the blade servers (generally, we compare the size of the accelerator indexes on disk to the size of the RAM). That leaves room for large queries to run without causing performance issues. If you exceed the 50 percent threshold, the main option is accelerator resizing unless you can disable non-critical InfoCubes and queries from the accelerator.
Index properties. For critical queries and InfoProviders, use the accelerator index property “Keep the index in the main accelerator memory.” Normally if the accelerator is sized correctly, all the data can reside in memory. If it is sized incorrectly, the accelerator swaps data out to a file system if it runs out of space in memory. If a query hits an accelerator index, which is only stored on the file system, it loads the relevant data into main memory before processing it. This results in reduced query performance.
System cleanup. If you have too many (hundreds) temporary indexes on the accelerator, you can bounce the accelerator index servers or delete temporary indexes using the TREX admin tool (transaction TREXADMIN) to clean up unused indexes from the accelerator memory. For more information, refer to SAP Note 1132962 (“BIA 7.00: Deletion of more than 1,000 temporary indexes”).
Note
If you have revision 48 or later and the latest BI Support Package, deleting temporary indexes is no longer an issue.
Overloading the Accelerator
The stability and performance of the accelerator server may be negatively impacted and queries terminated if it is overloaded. This can occur if you load too much data (indexes) into the accelerator, if the query or user load is so high that the temporary memory consumption becomes too large, or with APD queries on large-volume InfoCubes. It is important to monitor and optimize accelerator use as needed.
In case of accelerator overload, several options are available:
- When planning for the accelerator hardware, perform a proper sizing. When data volumes and user activity are growing significantly, you should re-size the accelerator.
- Try and reduce the load on the accelerator by deleting some accelerator indexes for low-priority InfoCubes. If applicable, disable the accelerator for APD. For more information refer to SAP Note 1231794 ("APD: Using the BIA to read InfoProvider data”).
- Distribute data optimally across the accelerator blades. For more information, refer to SAP Note 1163149 (“BIA 7.00: New Reorg Parameters”).
- For more information about the accelerator overload, refer to SAP Notes 1093719 (“BIA capacity utilization: Preventing overloading”), 1132572 (“BIA monitor: Adjusting revision and overloading checks”), and 1223230 (“Monitoring the BI Accelerator in overload situations”).
Outlook
The accelerator is an excellent option to help your organization achieve SAP NetWeaver BI query performance gains with little to no query tuning, aggregate building, or user impact. Going forward, the accelerator performance and scalability will continue to improve with future releases. SAP will keep on pushing the performance and boundaries of what can be done. Planned enhancements for the accelerator in the roadmap are to add and improve functionality and performance including:
- Extend the accelerator to other InfoProviders. For example, you can index data in DSOs and HybridProviders to ensure real-time analytics such as the capability to load data from a DSO directly to the accelerator, without the need for persistent data in an InfoProvider
- Move more analytic operations that make intensive use of memory — such as top N queries, count distinct, and MultiProvider aggregation — to the accelerator
- Enable and optimize BusinessObjects Web Intelligence and Polestar integration on top of the accelerator
- Become a standalone and source-agnostic analytic engine that can be deployed on any source
Catherine Roze
Catherine Roze is a senior BI consultant with more than seven years of full life cycle experience in SAP reporting and SAP NetWeaver BW with special focus on SAP NetWeaver BW enterprise reporting, performance optimization, and SAP NetWeaver BW Accelerator. A seasoned BI professional, she has contributed to more than 15 SAP NetWeaver BW implementation projects for Fortune 500 companies. Catherine holds an MBA and MS (industrial technology) and is the author of SAP BW Certification: A Business Information Warehouse Study Guide.
You may contact the author at cmroze@yahoo.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.