Reporting on SAP BW Performance

[Former Member]

SAP R/3 and the myth of the average dialog response time

Back in the good old days when SAP software was considerably simpler (e.g. no Java at the horizon yet) you had your SAP R/3 system(s) and transaction ST03 for analyzing its performance. The average dialog response time was THE (arguably even the only) SAP performance metric of the system. It seems comical, but it is coincidental that the value for the average dialog response time correlated so very well with the end users experienced performance. Reasons for this were two opposite effects which mostly canceled out each other:

1. What end users experienced as a single dialog step was reported as many really fast (sub) steps. You can verify that via single records analysis (transaction STAD).

2. Even though there is a limit on the maximum hold time for dialog workprocess, you can still find some rare but really long running DIA transactions in the ST03 data. They have a high impact on the averages.

In summary, you checked the average dialog response time and luckily most of the time it was very close what users saw when working on the system. If you don't believe me, reduce the value for rdisp/max_wprun_time by 20% and check the effect in ST03.

Standards, which Standards?

Now when you have a SAP BW system, some transparency would be great. Of course users are complaining about reporting and/or data load performance, but the big questions remain:

- Are the complaints justified?

- Is the trend just negative or catastrophically?

- Where are the pain points?

- How do I verify or report overall system performance?

Unfortunately there seems to be no equivalent to the average dialog response time known from R/3 systems. This sounds like a harsh statement, especially since I am no SAP BW specialist.

There is this really great presentation "SAP NetWeaver BW Administration Cockpit", couldn't I just read that and I am done with it?

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/c0e5ca3b-95ce-2b10-4d94-864ab29a8b63

Well, actually no.

Land of Confusion

At first, this whitepaper answers lots of questions and provides deep insight into the SAP BW performance reporting topic, so I am really thankful that it exists. However when I try to use it in real life, the problems start and more and more mysteries pop up:

Query Runtime Statistics

SAP says that the OLAP processor collects Query Runtime Statistics in the RSDDSTAT* tables. The BW technical content reads this data via view RSDDSTAT_OLAP and writes it into DSO 0TCT_DS01 and later InfoCube 0TCT_C01. So far so good, but have you ever tried to verify that information? I had a deeper look into RSDDSTAT_OLAP on various systems and tried to make sense of what was later written into 0TCT_C01. The data manager timing (typically an Oracle database) correlated very well with what was found in 0TCT_C01. This was of no great help to me, because Oracle databases provide lots of helpful KPIs on performance themselves, I don't need SAP systems for that. But anyway that specific data was accurate. Strangely enough, the correlation for the OLAP data was much less obvious. There were many rows with unexplainable differences between the RSDDATAT_OLAP and 0TCT_C01 data. In total, these differences could accumulate to well over 10% therefore skewing the averages. I used lots of tracing to find a reason for this, but I simply cannot explain it. Worse than that was the frontend data. While RSDDSTAT_OLAP typically doesn't show much frontend response times, the InfoCube 0TCT_C01 does! Even with tracing I couldn't see how SAP BW "makes up" this data. For performance reporting it is useless.

Data Load Statistics

I was not much surprised that for Data Load Statistics the picture is quite similar. Comparing the entries from table RSDDSTATDTP with InfoCube 0TCT_C22 is difficult, even for small systems showing only little activity. Maybe haven't been looking correctly, but can it be that the contents of table RSDDSTATDTP aren't documented by SAP? The InfoCube 0TCT_C22 is quite self explaining, but negative runtimes reduce the trustworthiness. If more that 10% of the rows contain negative runtimes, I don't know how to handle that.

Similarly I cannot see a strong correlation between RSDDSTATWHM and InfoCube 0TCT_C23. And here as well 10% of the rows have negative durations. Most probably I am just missing the right documentation on table RSDDSTATWHM contents and the hidden logic how this is uploaded into 0TCT_C23, but depending on which data I consult I get quite different pictures on performance.

If there is indeed documentation for the contents of RSDDSTATDTP and RSDDSTATWHM, please provide a link. I will be happy to apologize and restart my research.

Whom should I trust?

Reality, as usual, is at least one order of magnitude more complex than anticipated. To make the picture more complete, SAP is adding quite a lot to my confusion. For example if you access transaction ST03 on a BW system, you can see the "BI Workload" performance. There is a branch called "Load Data" containing InfoPackage and DTP performance data:

Now guess what? The InfoPackage performance data is taken from table RSDDSTATWHM and the DTP performance data is taken from table RSDDSTATDTP, but neither correlates really close with the actual table contents! Of course, the data presented in ST03 is quite close to the actual table contents, but again I see lots of unexplainable discrepancies. The discrepancies affect all categories like packages, number of rows, runtimes etc. So whom should I trust? The raw data in RSDDSTATDTP? Or transaction ST03? Or InfoCube 0TCT_C22? Which one is closest to the truth?

For the Query Runtime Statistics SAP has already changed the picture, so transaction ST03 Query Runtime Statistics are taken from InfoCube 0TCT_C01. In the past the data was from the RSDDSTAT* tables. Still the big question remains what is closer to the truth, the actual data from the RSDDSTAT* tables or the technical content InfoCubes like 0TCT_C01.

Help is on its way

You can imagine how thankful I am for SAP's announcement of the In-Memory Database. I am not so confident that the mysteries of how SAP presents BW performance data will ever be really clarified. (But I would acknowledge if I was too pessimistic here.) However some problems might vanish by themselves:

- As soon as the BW system is running on an In-Memory Database the  Query Runtimes can be expected to decrease by 1 or 2 orders of  magnitude. The criticality of BW performance reporting will decrease  respectively.

- Once the ERP and the BW system share the same In-Memory Database, the need for data loading is mostly removed. Thus analyzing data loading performance problems belongs to the past. This alone would justify migrating to In-Memory Databases.