While BW automatically optimizes queries the moment users save them, the query optimization can become outdated quickly because BW is an ever-changing environment. This process is virtually identical in SAP BW 3.x and SAP NetWeaver 2004s. During the generation of the query, the system runs an optimization process to generate the best SQL based on the known system information. The system uses this SQL code to access the requested data from the various tables in the database.

In addition to optimizing the query at the time of generation, by default, the system also automatically optimizes queries every 31 days when the first user runs the query. This optimization happens in the background when running the query. For example, say that no one has changed a query and thus, the system has not regenerated it in 31 days. The next time BW runs the query, BW automatically optimizes it based on the existing environment.

The user does not see or is not even aware that the system is optimizing the query. Often, the only noticeable difference for the user is the increased efficiency and performance of the query because of the optimization. All users benefit from this optimization and it continues every 31 days for each query unless the query is generated earlier. Generation of the query occurs automatically whenever a query is saved or during a manual generation in transaction RSRT. When the system generates a query, it automatically runs optimization on that query. Understanding how this optimization works allows you to determine if automated optimization would benefit your system’s performance.

Check Optimization Status

To see the last time that a query was optimized, go to transaction RSRT and choose the Technical Information button in Figure 1. In the Table Sizes Optimized column in Figure 2, the system populates the last system optimization date and time. This is only filled after the system runs the automatic optimization. If a query is manually generated or saved, the system populates the Query Generation Time in the same technical information screen. To find out when the query was last automatically optimized, look at the most recent date in the Table Sizes Optimized and the Query Generation Time columns.

Figure 1

Transaction RSRT: choose your query and click on the Technical Information button to see when the query was last optimized

Figure 2

The technical information screen shows when the system last optimized or generated the query or when the query was last optimized

Test Query Optimization

You can test the optimization of the query and its effect on a query’s performance. Pick a query in your system that has not been generated or optimized recently. Check to see if this query has been run in the past 30 days to make sure that it is ready to be optimized on this run. Run the query using transaction RSRT using the Execute + Debug option (Figure 3). Choose the options Display Statistics Data, Do Not Use Cache, and Do Not Suppress Messages/Warnings (Figure 4). This shows the performance statistics without using the online analytical processing (OLAP) cache.

Figure 3

Run the query using the Execute + Debug option

Figure 4

These options appear after you click on the Execute + Debug button

After the query runs, the next screen that appears gives you a statistical measure of the query (in the 3.x version). Note the timing numbers that appear in the following columns: QTIMEOLAPI, QTIMEOLAP, QTIMEDB, and QTIMECLIEN in Figure 5. This gives you a measure of how quickly the query is running before optimization. For more information about these statistic fields, see Table 1.

Figure 5

Statistics screen showing the various times for the query run

Next, manually generate the query using the Generate Report button in transaction RSRT (Figure 6). This generation causes the system to optimize the query. You can now run the query again using the Execute + Debug button as I described above. Look at the times again in the fields QTIMEOLAPI, QTIMEOLAP, QTIMEDB, and QTIMECLIEN. Typically, you should see a rather dramatic improvement in the times. It is not rare to see a 50-60 percent decrease in the times, and often more. This translates into better performance for the end users.

Figure 6

Click on the Generate Report button in transaction RSRT

This is not a completely scientific test because it does not take into account other processes running on the BW server or network traffic. However, it does give an idea of the impact of the optimization and the benefits on query performance.

Alter the Automatic Optimization

Why should you have to wait 31 days for the system to optimize your queries? Wouldn’t it make sense to perform this optimization more often? The answer depends on how dynamic your environment is. Typically, you can do very little harm by increasing the frequency of the automatic optimization. The optimization process happens quickly and consumes few system resources. Additionally, it only occurs on the first run once the query is due for optimization and can provide significant query performance improvement.

When deciding how often to optimize your queries, assess how volatile the associated BW tables become and how quickly this degrades the performance of the queries. Also consider the net effect of the optimization on performance. The statistical test that I detailed above should give you an idea of the impact of the optimization. Overall, a good practice is to schedule this process for all recently run queries about once every two weeks. Schedule this process less often if the volume of data in BW is light, and more often if the volume of data in BW is high. If the regeneration takes a long time, you can split it up and run it using the InfoCube parameter in the program I’ll explain below.

You should be aware that any query that has been regenerated invalidates all global OLAP cache for that query. For example, if a query has been run several times since the last reload of data, and cache was turned on and active, the query would have several entries in OLAP cache. The query would use those entries in OLAP cache to aid in performance for subsequent runs of the query.

Thus, a query that runs right after regeneration has no existing OLAP cache entries and is forced to read the InfoCube or associated aggregates. All new runs of the query begin to rebuild their OLAP cache.

Because reloading of data also causes the associated query cache to become invalid, any query that references data that is loaded daily has its cache invalidated daily. Thus, all query regeneration should be timed to run after the associated data loads but before the users start running the queries for the day. There would be no net effect on use of OLAP cache because the existing OLAP cache would already be invalid after the data was loaded.

Adjust Automatic Optimization Settings

To change the frequency of the automatic optimization or even turn off this automatic optimization for a query, choose the Properties option in transaction RSRT (Figure 7). There are three optimization options:

• 0: Query optimized after generation
  
  
• 1: Query optimized with individual period in days
  
  
• 9: Query optimization inactive

Figure 7

The optimization mode can be changed per query in transaction RSRT>Properties

The default setting is option 0. This means the system automatically optimizes the query every 31 days by default and also optimizes after generation. Option 1 allows for entry of an automatic optimization period that differs from the 31 day default. The system automatically optimizes the query based on the number of days specified. You can specify any number of days from 1 to 255. Once set, the query will be automatically optimized on the next run after the selected number of days has passed since the last optimization.

There is also an option to turn off the automatic optimization. If you choose this option, then the system only optimizes the query on save or regeneration. Choosing this option is rare and would only make sense in a very static BW environment. It forces the system to use the initial optimization setting and never changes. Thus, I do not recommend it in a typical BW environment.

Note that the optimization option in transaction RSRT only affects the automatic optimization that the system performs. If a query is saved or manually regenerated, it always performs an optimization regardless of the optimization setting in transaction RSRT.

The parameter you should choose depends on how dynamic your BW environment becomes and the net improvements that you saw when testing the optimization statistics of the query. You can see the settings for each query in your system by using transaction SE16 to see table RSRREPDIR. Look at the OPT_OCCURS_MODE field. Table 2 shows the three possible settings for this field.

There is no way to set the Optimization Mode option once and have it default or push it to every query. You must make any setting changes on each query individually in transaction RSRT. This can be a cumbersome task in a very dynamic environment with a lot of queries.

Regeneration and Optimization in Batch

You can have the system perform the optimization for all queries in batch by scheduling a mass regeneration job. This job acts just like the generate option shown in Figure 6 for many queries in batch automatically. The system automatically optimizes queries after regeneration, so this would also optimize the queries.

It would be even better to regenerate only those queries that have run recently. It makes no sense to regenerate queries that are not being used because nobody would benefit from the regeneration.

There is a standard SAP program in BW called RSR_GEN_DIRECT_ALL_QUERIES that allows BW to regenerate queries in batch mode. You must alter the standard program slightly to regenerate those queries that have recently run.

When BW runs a query, it automatically places a flag in table RSRREPDIR in the OPT_OCCURS field. After the query is regenerated, the flag is automatically removed from the OPT_OCCURS field. Thus, you can schedule the query regeneration for those queries with an X in the OPT_OCCURS field. This would regenerate only those queries that have run since their last optimization.

ABAP Changes

To modify the program RSR_GEN_DIRECT_ALL_QUERIES, you must first create a copy of the program using transaction SE38. Use the copy icon to create a copy of the program called Z_REGEN_QUERIES (Figures 8 and 9).

Figure 8

Use the copy icon to copy the BW program RSR_GEN_DIRECT_ALL_QUERIES

Figure 9

Fill in your own custom name for the program. I used the name Z_REGEN_QUERIES.

Because the standard SAP program does not allow selection by the OPT_OCCURS field, you must now add this selection criteria. You can do this by adding two lines of ABAP code to the copied program Z_REGEN_QUERIES in transaction SE38. Add the second-to-last line of code circled in Figure 10.

Figure 10

Add the line shown above

In addition, add the line of code shown in Figure 11.

Figure 11

Add the line shown above

When run, this program automatically regenerates queries for InfoObjects, MultiProviders, DataStore objects (formerly operational data store [ODS] objects), and InfoCubes. Thus, you can use the program to optimize any query you created in BEx. The program works for BW 3.x and SAP NetWeaver 2004s. 

Run the Program and Schedule the Load

You can now run the program in the background via transaction SE38. See Figure 12. You can schedule the program by saving a variant with the I_OPTOCC (that corresponds to OPT_OCCURS) with an X. It can run with this variant using a process chain or with transaction SM36. You should always run the program in the background because, depending on the volume of queries to regenerate, it could take a while.

Figure 12

The program Z_REGEN_QUERIES: note the I_OPTOCC field that corresponds to the OPT_OCCURS field in the query

It is possible that errors will appear on the output after you run the program. This is because the system may try to regenerate all queries based on the parameters, and some invalid queries might exist in your system that the system cannot regenerate properly. You should either fix or delete the invalid queries via transaction RSZDELETE.

Gary Nolan

Gary Nolan is an SAP NetWeaver BW-certified consultant and author of Efficient SAP NetWeaver BW Implementation and Upgrade Guide and Efficient SAP NetWeaver BI Implementation and Project Management. Gary specializes in gathering and evaluating requirements, configuring SAP NetWeaver BW, and providing project management, performance management, and data architecture and data modeling expertise. A former platinum consultant with SAP America, Inc., Gary has more than 20 years of SAP experience, working with SAP NetWeaver BW since version 1.2B.

You may contact the author at melvan.consulting@yahoo.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.