Measuring Performance for Real-time BW on HANA Report

[vineet_gupta3]

After upgrading to BW on HANA and Suite on HANA, the scenario for providing real time reports from BW is greatly simplified. (See blog: https://blogs.sap.com/2019/12/13/cds-based-data-extraction-part-i-overview/). Even with HANA everywhere, you could run into performance issues. For complex ABAP CDS models that combine several other ABAP CDS views, the first step is to find which included view is the root cause of the performance issue. This blog may help you in identifying the component CDS view that maybe resulting in overall poor performance. There are several blogs on best practices for ABAP CDS design.

System Landscape

1. BW 7.5 SPS16 on HANA
2. Suite on HANA ECC EHP 08
3. Business Objects 4.2 SP6 with Lumira Server Add-on

 

Reporting Scenario

A typical real time reporting scenario can be setup with ABAP CDS views in ECC that is the source for BW open ODS  and BW query. Here are the detail steps:

1. You have created a model with several ABAP CDS views to fulfill real-time reporting needs. The ABAP CDS view are combined in a Consumption ABAP CDS view with @analytics dataExtraction and dataCategory annotations


2. BW on HANA system has a source system of type ODP – ABAP CDS context pointing to ECC system. Let us assume that the connection uses ALEREMOTEBW as userid


3. You have created a datasource DS1 in BW that points to the ABAP CDS consumption view


4. You have created an open ODS view OD1 pointing to DS1 data source. Some of the fields in OD1 are identified by existing infoobjects in BW


5. You have created a composite provider CP1 in BW on top of open ODS view OD1


6. You have a query Q1 on composite provider CP1


7. End users access query Q1 from Lumira Generic analysis in Business Objects and you would like to evaluate query performance.

Measurement Setup

1. Execute Lumira Generic analysis to execute the query Q1. I used a BW OLAP connection in Business Objects to connect to the BW query. Navigate to the step for which you would like to measure performance

2. Using ST05 in SAPGUI, activate SQL trace with filter in Suite on HANA. Set userid ALEREMOTEBW in the filter



3. Execute the reporting step in Lumira

4. Deactivate trace in Suite on HANA

5. Display captured trace

6. Select the row that gives duration of the select statement on the ABAP CDS view and select

Display Execution Plan For Recorded Statement



7. SQL statement and its execution plan is displayed. A user friendly way to display the plan data is in the HANA Modeler perspective of Eclipse or HANA Studio. Use the Execution trace button in the toolbar to save the trace file on your local file system.



8. Open the file saved in the previous step in Eclipse HANA Modeler perspective. Select Executed Plan to display a graphical representation of how the result set was built and which components of the ABAP CDS view model took how much time.



9. Execution times are displayed