SAP Cloud for Customer: How to troubleshoot sub-optimal reports performance

[verakrasnova]

SAP Cloud for Customer comes with its own analytical reporting features, capabilities and strengths. These capabilities should not be compared with other primary (independent) reporting / analytical tool, proper evaluation should be done to design and implement each business reporting use case optimally.

Inefficient report or data source modeling may result in extremely slow report performance.

Here is a simple example of how report design can affect performance.

Using HTTPWatch’s Basic Edition (free version - Download | HttpWatch), it is easy to see hints for sub-optimal report design.

HTTP Watch trace for custom built, non-optimal report:

The Red part in Time Chart column for POST request is Wait time. Wait time is the idle time spent waiting for a response message from the server. This value includes delays introduced due to network latency and the time required to process the request on the web server. We can see high server response time (~47 seconds) as well as high number of GET requests (which is status check requests).

First we have to check based on which data source report is built. In BUSINESS ANALYTICS work center we search for report name and in search results we get data source name.

Now we can review specific data source to see how it is built. In BUSINESS ANALYTICS work center we search for data source and go into EDIT mode.

In EDIT mode we see which fields and data sources are used.

Anchor data source is Opportunity Funnel (standard), joined via Inner Join with data source Opportunity Header and Item (standard).

In this example customer has built report based on customer designed Join Data Source. Join Data Source was based on two SAP standard Data Sources - Opportunity Funnel and Opportunity Header and Item. After reviewing the report results and available data sources, we have identified that the same data can be retrieved using just one SAP standard Data Source (Opportunity Funnel), and performance got improved.

HTTP Watch trace for re-designed report to improve performance:

We can see that performance has improved from ~47 seconds to ~2.5 seconds with adjusting the report design. This is basic and simple example of how inefficient design can impact end user experience. Of course there will be more difficult cases to analyse and optimize.

Before building custom reports or data sources always investigate possibilities of using SAP standard data sources or reports. If using SAP standard reports is not an option model and design reports based on the following best practices.

Join Data Source modeling best practices:

Inner Joins are a preferred approach.

Avoid joining multiple Data Source (DS). If unavoidable, for inner join, use them together in one DS

Left Outer Join should be avoided, if unavoidable can be joined separately, after Inner Joins.

The leftmost data source (Anchor):

• Should be the one that returns least number of unique records
• Should ideally be the source of report’s mandatory selection conditions

For generic requirements please provide SAP a feedback for evaluation if it can be delivered as standard DS in future releases.

Report modeling best practices:

If a field is available in DS directly add it through report wizard instead of using ‘Add Fields’.

If a field is not directly available in a DS consider using ‘Add Fields’ before joining.

If possible, reports on join DS should have selections from all data sources used in join.

It is recommended to use not more than one hierarchy in a report.

Avoid using negative selection conditions.

For detailed level reports add mandatory selections. Consider using relative selects.

Reduce report drill down to required fields only. End user has an ability to add fields to drill down and save their own views based on their requirements.

Avoid using column drill down.

Testing best practices:

Testing should always be done in  a smaller parts

Test data should be set up for all individual data sources (part providers)

Testing for the complete scenario with multiple and complex join should be done as last step

First, each part provider should first be tested with a respective test reports and right selection conditions

Once the lower level part providers are tested, the overlying hierarchy of joins can be tested in step by step manner

• This helps to identify the missing records early in the chain
• It gives clues if the joins are incorrect
• Data setup is not as expected

Once the lower level part providers are tested, the overlying hierarchy of joins can be tested in step by step manner

Additional information:

Anant Ahuja talks about and demonstrates Best practices for SAP Cloud for Customer Join Data Source Creation and Usage in the following video. Conversation transcript for best practices session is attached to this blog.

Business Analytics Guide is available here:

https://cp.hana.ondemand.com/dps/d/preview/cab8d3fba22647dea4f6f162f38bd519/1605/en-US/frameset.html

Use this guide to find more details about how to apply the rich analytic capabilities of SAP Cloud for Customer. You will find information about designing reports, KPIs, interactive dashboards, and more.