SAP BW Performance Optimization

2.1 BW queries for monitoring performance

Now that you understand how to activate BW statistics for technical content, let’s explore the relevant objects for performance monitoring and tuning. There are many queries delivered based on MultiProvider which will help to identify performance bottlenecks.

The standard MultiProvider include:

• InfoPackage Statistics – 0TCT_MC23
• DTP Statistics – 0TCT_MC22
• Process Statistics – 0TCT_MC21, 0TCT_MC12
• Report Availability Status – 0TCT_MC14
• Front-End and OLAP Statistics (Highly Aggregated) – 0TCT_MCA1
• Front-End and OLAP Statistics (Details) – 0TCT_MC02
• Front-End and OLAP Statistics (Aggregated) – 0TCT_MC01
• MPRO – Data Storages with inconsistent and incomplete data – 0TCT_MC15
• BI Object Request Status – 0TCT_MC11
• OLAP Statistics – 0TCT_MC05, 0TCT_MC06, 0TCT_MC07
• Workspace Statistics – 0TCT_MCWS

System bottlenecks may occur in a number of different areas. The following ones may be of particular interest to BW Administrators:

• Database Volume Statistics – 0TCT_MC25
• BWA Statistics – 0TCT_MC31, 0TCT_MC32
• Data Manager Statistics (Details) – 0TCT_MC03

For SAP BW 3.5

All of the major queries supplied by a standard system under the BW statistics are based on the MultiProvider 0BWTC_C10.

This MultiProvider is based on the following basic cubes.

• BW statistics – OLAP [0BWTC_C02]
• BW statistics – OLAP, Detail Navigation [0BWTC_C03]
• BW statistics – Aggregates [0BWTC_C04]
• BW statistics – WHM [0BWTC_C05]
• BW statistics – Metadata [0BWTC_C08]
• BW statistics: Condense InfoCube [0BWTC_C09]
• BW statistics: Deleting data from InfoCube [0BWTC_C11]

System bottlenecks may occur in a number of different areas. The following ones may be of particular importance to BW Administrators:

• Loading data into BW InfoProviders
• Execution of queries

Depending on the area of the bottleneck, the query output generated from the standard queries from BW statistics will need to be studied and appropriate preventive, or corrective action needs to be taken.

2.1.1 Relevant BW queries

BW statistics has more than 135 queries. We will cover representative queries that can help in identifying performance problems.

0TCT_MC23_Q0101 [Total Runtimes of InfoPackageInfoPackages]

This query compares the average number of InfoPackage runtimes over the past 30 days to the average number of records extracted. Drilling down at the day level helps to compare and identify InfoPackage with lower performance. Daily trend of extraction duration and number of records identifies any performance deterioration over a period of time. Other than 30 days average duration and 30 days average records extracted, the query also provides two more key figures—averages duration in last 48 hours and average number of records extracted in last 48 hours. A quick comparison between 30 day averages and the average over the last 48 hours makes it easy to pinpoint any InfoPackage with large deviations.

Figure 2.12: Total InfoPackage runtimes

0TCT_MC01_Q0200 (Aggregated Query Runtime Statistics: Analysis)

This query provides runtime breakup of all BI applications, such as web templates and workbooks. Breakup of total runtime in terms of front-end time, OLAP time, data manager time, planning time, other time is available in the query. Along with this information, three addition key figures, including deviation in times in percent per BI application, BI application: short-term total time, BI application: long-term total time provide the complete information required for query runtime analysis. By default, there are conditions present in the query to highlight BI applications with exceptionally high runtimes. The free characteristics will allow you to drilldown by day, query, and query step level providing complete insight into problem areas for a given query or web application.

Figure 2.13: Aggregated query runtime statistics: Analysis

0TCT_MC25_Q0105 [BW DB Usage: Monthly Overview]

BW DB Usage query is an excellent report available for database administrators to keep close tabs on month over month data growth in the SAP BW system and help in capacity planning. It provides the ability to drilldown the database growth at Object Type (Aggregate, InfoCube, Data Store Objects, InfoObjects), Object Id on Calendar Day level. Any object with high data growth can easily be tracked and further analyzed to understand the cause of the amount of data. For example, BW object fed by DataSource with full extraction mode, could be cause of high data volumes. These types of data extractions can be controlled by pseudo delta data loads.

Figure 2.14: Monthly overview of BW DB use

0TCT_MC21_Q0503 [Process Chain Performance – Monthly Overview]

Data loading activity is one of key processes in SAP BW and it takes quality time of a SAP BW consultant to ensure that all necessary data is available for reporting to business users. Regularly analyzing process chain runtimes is critical to achieve the agreed upon service level for data availability. Query 0TCT_MC21_Q0503 – Process Chain Performance – Monthly Overview, provides the process chain wise month over month average duration taken to complete it successfully. A copy of this query with calendar day as additional free characteristics will help in monitoring the process chain performance on day to day basis.

Figure 2.15: Monthly overview of process chain performance

For SAP BW 3.5

Of the 40+ queries from BW 3.5 technical content, we will cover representative queries that can help in identifying performance problems.

0BWTC_C10_Q314 [WHM Use per InfoSource]: This query is based on the MultiProvider 0BWTC_C10. It helps in identifying which InfoSource is a bottleneck while loading, since it gives the throughput of the data load per InfoSource for a specified time period.

Once the InfoSource is identified, use Query 0BWTC_C10_Q313 to find out which process takes the most time.

The standard query allows a user to enter a timeframe [Date: from and to] and name of the InfoSource. If, for instance, a timeframe of one week and three InfoSources [e.g., 0FI_GL_4, 2LIS_03_BF and 2LIS_13_VDITM – all standard InfoSources which are very important as far as system loading is concerned], are given as input values, it is evident from the output shown in Figure 2.16 that only the time is recorded. What one would want to see, however, is how each individual InfoSource is performing during the given period of time on a daily basis. The standard query outputs only the number of total records loaded for the InfoSources and the total time taken for loading. The standard query has therefore been modified slightly so that it can be more useful.

Figure 2.16: Output of standard query 0BWTC_C10_Q314

As stated, the query was changed by adding the calendar day as a free characteristic to the filter portion of the query. The standard query also has a filter on

Process Type Value = 900 [Total Process Time], which was changed to 10 [Time for Processing in source system]. This now gives an administrator the flexibility to view the ratio of the number of loaded records over the time required to extract them. If there is a drastic increase/decrease in this ratio one needs to investigate in detail. If the ratio changes drastically for a single InfoSource, the probable bottleneck area is around that particular InfoSource. In this case, check how much time is taken by the source system, transfer rules, update rules, etc. over a period of time. Query 0BWTC_C10_Q313 can assist in obtaining some information on this. However, if the ratio changes drastically for almost all the major InfoSources, there could be a problem with the system overall.

Look at the problem from a wider perspective, e.g., whether a new patch at any level has been applied on either the BW system or the source system, changes in the hardware such as memory, CPU, IO cards, changes on the DB parameters, etc.

Now look at the modified query output shown in Figure 2.17. In order not to clutter the output, it is filtered for one InfoSource, 2LIS_13_VDITM. The time shown here is the time taken for the extraction process in the source system. From the output reflected in Figure 2.17 it is evident that the extraction performance on 01/ 08/2014 was the worst of the days displayed in the chart. There is a huge decrease in performance between 03/08/2014 and 06/08/2014. The causes for this could include lack of up-to-date database statistics on the underlying table, a missing index on the table, a shortage of system resources due to multiple jobs running at the same time, etc. If the ratio deteriorates on a particular day, also check whether more jobs are running on those days due to business requirements.

Figure 2.17: Output of modified query 0BWTC_C10_Q314

0BWTC_C10_Q313 [Transfer Method: Loading Data]: This query is also based on the MultiProvider 0BWTC_C10 which helps in monitoring the loading process with breakdowns, such as time taken by the source system, sending the data to BW, saving data in an ODS object, reading data from an ODS object, inserting into an InfoCube, etc. The information supplied by this query helps by pointing towards the performance bottleneck area. It may be that there is a bottleneck in sending the data from the source system and the user is spending time in BW waiting for the data to arrive.

This is very flexible query because it provides focus on characteristics such as calendar day, InfoSource, and process type, which can be very useful in identifying the part of the system where most of the time is spent. The sample output is shown in Figure 2.18.

Figure 2.18: Sample output of query 0BWTC_C10_Q313

All process types are listed below in Table 2.1.

Process Type Value

Description

10

Processing in source system

11

Sending data

12

Extractor

20

Converting communication structure

30

Saving data in ODS

39

Read from ODS

50

Converting update rules

60

Inserting into InfoCube

65

Changing InfoCube

200

Delete InfoCube contents

210

Delete request from InfoCube

220

Reverse posting

300

Total process until data is saved in the BW system (ALE/ODS)

600

Restructuring InfoCube

900

Total process

Table 2.1: Values of process type

0BWTC_C10_Q312 [WHM Use for Each Time ]:

This query can assist in identifying the peak time of day in order to reevaluate the data being loaded at that particular time. Leverage this information to restructuring the loading process to prevent overloading the system at peak times by rescheduling some of the lower priority loading jobs.

Figure 2.19: Sample output of query 0BWTC_C10_Q312

In Figure 2.19 the data indicates that there was no activity between 03:00 and 07:00 AM and between 08:00 and 10:00. There is another lean period between 19:00 and 22:00. The chart above only reflects one day, but data can be reviewed for a week or a month to identify the non-peak hours. Choose to schedule other activities at this time, such as printing, broadcasting, or other administrative related activities.

Now let us look at some queries which can be useful from the OLAP point of view.

0BWTC_C03_Q210 [Using InfoCubes / Aggregates to Navigate]:

This query allows for specification of a time frame by entering from and to dates. Use this query one to see how often InfoProviders and aggregates are used.

Figure 2.20: Sample output of query 0BWTC_C03_Q210

In the InfoCube column the aggregates can also be seen. Sort the table by “Frequency” in descending order to identify the most heavily accessed InfoProviders. This may help in prioritizing the performance tuning related work on objects.

0BWTC_C10_Q014 [Runtime Query]:

This query will help to check which queries are widely used in the system; how many records they read from the database; how many records have been transferred to the BW server; how much time it has taken to read these records from the database, or to run authorization checks, etc. A sample output is shown in Figure 2.21. It is also possible to identify whether an aggregate would help process this query faster by checking if the ratio of the number of records read from the database is significantly large in comparison to the number of records being transferred from the database. SAP recommends creating an aggregate when the ratio of records selected on database to records transferred from database is greater than 10 and time taken to read the database is greater than 30% of total run time.

Figure 2.21: Sample output of query 0BWTC_C10_Q014