A typical challenge for managing an SAP NetWeaver BI environment is to frequently monitor, analyze, and fine tune queries that efficiently produce results for users. SAP has developed new features for companies that have either recently upgraded to or installed SAP NetWeaver BI 7.0, including those that generate and store query runtime statistics you can analyze to uncover performance issues.

The new BI Statistics feature is one of those improvements. Using BI Statistics, you can more effectively collect detailed data, monitor performance, and use analysis tools on key SAP NetWeaver BI objects such as InfoProviders, query retrievals, and data loads.

SAP has improved the method for capturing statistical data on SAP NetWeaver BI queries and warehouse management functions (i.e., various programs and new tables) to provide more accurate and relevant information so that users can better tune their systems. SAP has enhanced InfoProviders and tables that capture detailed statistics on query runtime, data load, and data load status (Table 1). The table lists the relevant SAP NetWeaver BI tables used to store the statistical metrics for the appropriate item, such as process chains. Note that the SAP BW 3.x InfoProvider statistics (such as 0BWTC_C02 and 0BWTC_C03) are not recommended for use. 0BWTC_C02 will not receive any new data because the source table RSDDSTAT is not used for statistics data collection anymore with the new 7.0 BI Statistics. The InfoCube is available but is never populated. 0BWTC_C03 is a bit different. It’s still usable but the new enhancements to the source tables have not been reflected in this InfoCube.

SAP has also stated that it is not possible to transfer your previously collected data into the new SAP NetWeaver BI 7.0 InfoProviders for monitoring or analysis.

Activate BI Statistics Technical Content

BI Statistics is delivered as part of the Technical Content option in SAP NetWeaver BI 7.0. You can install the Technical Content in the same way that you install any other Business Content. To successfully activate BI Statistics, you should first prepare your system and then complete the activation steps. As far as we know there is no one place you can get all the information we are presenting, which is derived from different sources. The information is required to configure statistics.

Prepare for Activating Technical Content

To get ready to activate Technical Content, complete the following preparation steps to ensure that your SAP NetWeaver BI system contains the necessary software and features:

Upgrade as necessary: If the system is not using SAP NetWeaver BI 7.0 Support Package 17, implement SAP Note 1114719.

Verify security: Per SAP Note 965386, make sure that the user who activates the content has all the authorizations in the S_RS_RDEMO template. You can view the S_RS_RDEMO template by using transaction SU24 and clicking on the Authorization Templates button.

To add the template to a profile or role, run transaction PFCG. Edit the authorization profile by following menu path Edit>Insert Authorizations>From template. For details and a description of the BI authorization templates, see SAP Note 161292.

Confirm roles: Confirm the BWREMOTE user has the SAP_RO_BCTRA role, which contains the S_RO_BCTRA authorization object for the remote activation of data sources.

Assign roles: Using transaction SU01, assign BI administrators (users) to the role SAP_BW_BI_ADMINISTRATOR.

Activate the Technical Content

After verifying that all of the prerequisites are complete, you are ready to activate the BI Technical Content. Recall that Technical Content is delivered with the BI Business Content so you can analyze and optimize the various processes of the BI system. Complete the following steps to install the Technical Content.

Step 1. Activate the Technical Content. Run transaction code SPRO and follow menu path SAP NetWeaver>Business Intelligence>Settings for BI Content>Business Intelligence>BI Administration Cockpit>Activate Technical Content in SAP NetWeaver BI.

The Activate Content for the BI Admin Cockpit screen appears with several check boxes selected, indicating that those tasks are ready to be performed (Figure 1). In this screen, use the Log button to view the activation logs and monitor the progress of the tasks (Figure 2).

Figure 1

Screen for activating content

Figure 2

Activation logs

Activating the Technical Content is a long running process. We recommend that you run the activation in the background to avoid any time-out issues. To do so, select Program>Execute in Background or press the F9 key. To check on the background job tasks, open a new SAP session, run transaction SM37, and locate the job that starts with RSTCC_* (Figure 3). This is the job that activates the Technical Content.

Figure 3

Check on the background job RSTCC_*

Step 2. Allow SAP NetWeaver BI to complete the activation program steps. When the activation program starts, SAP NetWeaver BI performs several tasks, including the following:

Assign source system: Installs the BI AdminCockpit content for the source system entered in this field

Replicate DataSources: Replicates the DataSources of the source system entered in the Assignment of the Source System field

Repair missing InfoPackages: Repairs any inconsistencies

Activate Technical Content: Activates all Technical Content objects

Schedule initial process chains: Schedules the initial process chains

Maintain BI Statistical Properties for Query Runtime

After the system has activated the BI Technical Content, you are ready to configure what types of data to capture and to what level of detail. SAP NetWeaver BI 7.0 lets you manage the collection of the statistical information. Enter transaction code RSDDSTAT to define the objects for which runtime statistics are recorded within the system (Figure 4).

Figure 4

Define the objects for which you want statistics

On the Maintenance of Statistics Properties screen, you can configure the settings to collect statistics (using the Stat. “On/Off” property in Figure 5) for queries, InfoProviders, Web templates, and workbooks. Highlight the row you would like to change, then select Extras>Change Default from the menu. Select the Query tab to set additional levels of statistical details you want to capture. Note, however, that SAP NetWeaver BI 7.0 cannot capture the additional statistical details in the InfoProvider, Web Template, or Workbook tabs.

Figure 5

Configure the settings

The levels of additional statistical detail include the following:

0 (Aggregated data): This option calculates the overall total runtime (this is the default for queries)

1 (OLAP data): This option collects information about the front-end and OLAP events per handle, but only one Data Manager event per handle. Other data, such as DB read statistics, are not collected.

2 (Full detail): This option records all front-end and OLAP events and all the Data Manager information, including DataSource and access time for each single database or TREX call or Remote Function Call (RFC)

9 (No data): Does not record any query statistics. This is the equivalent of turning statistics off.

Query Runtime Statistics

As mentioned earlier, SAP NetWeaver BI 7.0 has enhanced its statistics collection to capture more details for analysis and monitoring. This article briefly focuses on query runtime statistics. A review of other statistics on data load and data status is saved for future articles. Figure 6 shows the tables and other objects from which statistical information is gathered for analysis.

Figure 6

Relationship between BI Statistics tables and InfoProviders

The query runtime statistics include data from the front end/OLAP and Data Manager. Each area captures details called events, which are stored according to an event ID. Event IDs are new to SAP NetWeaver BI 7.0 and allow you to track every runtime component of an executed query. You can read more about this feature from the download available via this link. In a log, SAP NetWeaver BI 7.0 lists a separate event ID for reading data in the OLAP cache, another for authorization checks, and so on until the query completes its execution.

Analyze Query Runtime Statistics

Using a typical example, this section shows how you can analyze query runtime statistics to improve query performance.

Step 1. Select the query. Enter the transaction code RSRT. SAP NetWeaver BI displays the Query Monitor screen (Figure 7). Enter the technical query name, and then select the Query Display method. You can select one of three options based on how typical users run the query: List (raw data collection and display), BEx Analyzer, or HTML (for Web reporting).

Figure 7

Enter technical name of the query and select the Query Method display

Step 2. Set the debug options. Next, click on the Execute + Debug button to open the Debug Options window, which lists the various ways to debug a query (Figure 8). To view the statistics, select the following settings in the Others section:

Figure 8

Set the debug options

• Display Statistics Data: Collect statistics (such as database, OLAP and front-end) on the query
• Do Not Suppress Messages/Warnings: Display any messages during the execution of the query

Step 3. Execute the query. After you select the debug options, click on the green check mark icon to run the query. The system gathers and then presents the data in the current query display format (Figure 9).

Figure 9

BEx Analyzer query results

Step 4. Review the query runtime results. Click on the back icon to view the collected query runtime results (Figure 10). The Frontend/Calculation Layer tab contains several rows of event IDs. During runtime, the system begins by reading the first user action. For example, the initial execution of Total DBTRANS (number of transferred records) has a counter of 6,984 records, while Total DBSEL (number of records read from the database) has a counter of 7,500. The percentage of database time is .9312 (6,984/7,500 = .9312).

Figure 10

Statistics for query runtime

Step 5. Make changes based on the query runtime results. Now that BI Statistics has captured and quantified the runtime numbers, you can change the query based on the collected data. In this example, you can see whether a change to the query’s Read Mode affects the query runtime. Alter the Read Mode from option A to read all the data at once to option H to read only when you navigate or expand (Figure 11). Click on the Properties button on the main RSRT screen. This lets you test whether this change can help decrease the database time.

Figure 11

Alter option to read only

Step 6. Review the query runtime results. After you make the change, review the same Event IDs, Total DBTRANS, and Total DBSEL that you reviewed in Figure 10. As Figure 12 shows, the percentage of database runtime has improved to .0032 (24/7,500 = .0032).

Figure 12

Statistics for query runtime after altering the read mode

This example uses two key event IDs to compare query runtimes. The new BI Statistics feature contains numerous event IDs that you can use to quantify changes you make to the BI system.

Clean Statistical Data

When your BI environment is successfully collecting data based on your requirements (query runtime statistics, data load statistics, and data load status statistics), you can monitor and maintain the collected statistical data.

Depending on the level of statistical detail you are collecting, the system writes 20 to 40 records per query navigation step and adds them to the BI Statistics tables (for those InfoProviders and queries where statistics data collection is turned on). For example, suppose 100 users each perform an average of six navigation steps per day. That means 100 × 6 × 30 = 18,000 records are collected per day in the BI Statistics tables.

To keep SAP NetWeaver BI running efficiently, you should delete outdated or otherwise unnecessary statistical data frequently. SAP NetWeaver BI has provided two methods for deleting data: manual and automatic.

Delete Statistical Data Manually

Start by running the transaction RSDDSTAT. When the Maintenance of Statistics Properties screen appears, select the row that you want to delete, and then click on the Delete Statistical Data button. When the Deletion of Data in BW Statistics Tables window opens, select the appropriate options to indicate which data you want to delete and for which dates (Figure 13).

Figure 13

Deleting data in BI Statistics tables

Delete Statistical Data Automatically

To delete statistics data automatically during data load, refer to the TCT_KEEP_OLAP_DM_DATA_N_DAYS property in the RSADMIN table. By default, the system deletes data from the last 14 days from the BI Statistics tables with each delta load for query runtime statistics.

You can alter the default 14-day setting to suit the requirements in your environment. Refer to the SAP Note 891740 “Query runtime statistics: Corrections for extractors” for information on editing the table correctly.

Muke Abdelnaby

Muke Abdelnaby is a principal with Renaissance Partner Group, Inc. (RPG). He and RPG create sustainable value for their customers by effective implementation and support of enterprise reporting, financial planning, and consolidations solutions. Muke and RPG not only focus on the technology of SAP NetWeaver, but also on business process understanding, industry knowledge, and technical expertise to deliver value solutions in both implementation services and product development. Muke has presented at several SAP NetWeaver BI sessions at ASUG and TechEd.

You may contact the author at muke.abdelnaby@rpg-corp.com.

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

Hisham Ismail

Hisham Ismail is a principal with Renaissance Partner Group, Inc. (RPG). Hisham has SAP experience in effectively implementing and managing projects involving ERP (SAP), Business Intelligence (BI), Enterprise Portals (EP), Middleware, and Supplier Relationship Management (SRM).

You may contact the author at Hisham.ismail@rpg-corp.com.

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