SAP BW Query Optimization

Team-Fly

---

In the previous section, I discussed how aggregates help improve query performance. Remember that aggregates speed up query response because data requested by the end user (query) is already available in the summarized/condensed fashion in an aggregate cube. This saves a lot of database and system resources to find requested data from a large InfoCube.

The aggregate cubes only help speed up fetching data from the database, but how SAP BW manages and delivers retrieved data to the end user is another dimension to consider when improving overall end-user query response. The SAP BW OLAP process needs to know when and how much data to read from the fact table and send to the user based on the query read mode. The read mode determines how often the OLAP processor reads data from the database during navigation. The three possible query read modes are listed below.

• Read all data

• Read data during navigation

• Read data during navigation and when expanding the hierarchy

Read all Data

In this mode, query reads all data to its lowest granularity from the fact table and stores data in the user memory space. The OLAP processor computes all the aggregates and data summaries to meet end-user query needs in memory. All of the navigational steps are done in memory. This makes query navigation fast; however, it eats up tons of OLAP processor memory, and that may cause severe memory problems with other SAP BW tasks. Because the OLAP processor reads all possible data from the fact table that a query may need, the OLAP and database engine may read unnecessary data that may not be needed for query navigation at that time, and hence too many resources are consumed that were not needed. The usage of this mode should be limited to special queries when end users (data analysts) need to slice and dice data against all dimensions or perform data mining tasks.

Read Data During Navigation

In this mode, data is retrieved on demand during navigation-for example, at first when a user launches a query that shows summary level information. At this time, the OLAP processor simply reads the required data elements and prepares the data set to satisfy the data request. Suppose now that the user wants to drill down to next level detail; the OLAP processor reads the database to generate the next level of detailed data set for the end user. This is the default read mode for the queries.

Read Data During Navigation and When Expanding the Hierarchy

In this mode, the data is read on the hierarchies level from the database when requested by the user. The OLAP processor does the data aggregation. If queries do not use hierarchies, there is no difference between this and the previous read mode, Read data during navigation.

Now that you know how query read modes work, you may be wondering how you can set or change query modes. The next section discusses this subject.

Setting up Query Read Modes

Query modes can be set in two ways. First, set default query mode for an InfoCube using transaction READMODE, as shown in Figure 16-10. All queries generated against that InfoCube, after setting the read mode, will have default read mode set for the InfoCube.

Figure 16-10: Setting Global Query Read Mode for an InfoCube. Changing Read Mode for an InfoCube Does Not Change Read Mode for all Existing Queries against the InfoCube. This Mode is Applied to Only New Queries.

The second method to change the read mode of an individual query is done via Query Monitor transaction RSRT, as shown in Figure 16-11.

Figure 16-11: Changing Read Mode for an Existing Query using Transaction RSRT. Note that this Transaction Can be Used to Debug the Query.

To check read mode of a query, select a query, and then click the Technical Info button. It displays technical information of the selected query, such as when the query was last modified, the ABAP program name for the query, and the read mode.

To change the read mode, after selecting a query, click the Read Mode button and select the read mode type as shown in Figure 16-11.

An interesting feature of the Query Monitor transaction is that you can debug the selected query. You can debug the actual report ABAP code for the query. Simply click Debug and step through the ABAP code. To simply see the query results, without BEX Analyzer, click Execute System.

---

Team-Fly