Performance analysis in the SAP Datasphere

Processing data poses great challenges for system performance, especially with rising data volumes. SAP Datasphere therefore offers us the ability to analyze performance and to locate problems. In this blog we will be taking a closer look at this capability

SAP Datasphere uses SAP HANA Cloud DB. Therefore, tools for the HANA performance analysis can also be used for the performance analysis in the Datasphere.

The Data Integration Monitor

The Data Integration Monitor offers support in monitoring and managing table replication from sources outside the SAP Datasphere and indicates the frequency of data updates.

It is easy to see how many tables there are, how much memory they require and what actions have recently been performed. Data can also be replicated here, persistent views can be created, remote connections can be analyzed and details on these actions can be queried.

Figure 1 – The Integration Monitor

Query runs, in which the Datasphere understands all database queries, can also be monitored in the Data Integration Monitor.

For detailed analyses, Plan Viz can be used as in SAP HANA On Prem systems. The following section describes the step-by-step process of the HANA PlanViz analysis.

Generate a SQL code for the Datasphere data model

• Log into the Datasphere and open the data model you would like to analyze.
• Under Edit, click on “Preview SQL”.

Figure 2 – Steps in generating the SQL Code

The code is displayed and can be used in the next step for generating the PlanViz file.

Figure 3 – The generated SQL code

Generating the Plan Viz output file

For this step, a Database Analysis User is required for the Datasphere. The user is created with the following steps:

• In Datasphere click on System – Configuration.

Figure 4 – Creating a Database Analysis User

• Under Database Analysis the DB Analysis User can be created. First, assign the technical user name and select the option “Enable Space Schema Access”. You still have the option to indicate a time period in which the access should occur.

Figure 5 – Possible options in creating a Database Analysis User

• Now a password is generated. The password should be copied and backed up, because it will be needed later.

Figure 6 – Password when creating a Database Analysis User

• In the same “Database Access” tab, select the user and then click on “Open Database Explorer”

Figure 7 – Open Database Explorer

A new window opens for logging in to the SAP Business Technology Platform. You can log in using “Sign in with default identity provider”. The DB Analysis User and the generated password are used for this.

Figure 8- Logging in to SAP Business Technology Platform

After successful log in, the Datasphere will be visible with its objects on the left side of the window. The SQL console can be opened by right-clicking on the instance using the selection “Open SQL Console” in the context menu.

Figure 9 – The SQL Console

• The generated SQL code can be inserted here. The schema, in our example “Einkauf_Demo”, must be added in front of the table name.
• Click run to check the SQL code.

Figure 10 – Testing the generated SQL Code

Next, the PlanViz file is generated with “Generate SQL Analyzer Plan File” under “Analyze”.

Figure 11 – Generating the PlanViz file

Analyzing the visualized HANA plan

After saving the Plan Viz file on our local system, the processing can be analyzed more closely, as follows:

• Start Eclipse/HANA Studio
• In the menu, click on File and select the generated file with “Open File”.

Figure 12 – Opening the PlanViz file

• Now the file opens and you can perform an analysis of the individual operations (e.g. joins, calculating times).

Under “Overview” you can find general information on processing times and memory use.

Figure 13 – View of the PlanViz file

Under “Executed Plan” you fill find detailed information on the processing times of the individual operations with the relevant number of data records, the server accounts used and any network transfers.

Figure 14 – Executed Plan general display

Each box in the Plan Viz output represents a Plan Operator (POP). In the Plan Viz display, data runs from bottom to top. The query results can be found in the view and the actual data query from database tables is located at the bottom. The plan operators in between represent the transformations. In each box, you can click on the arrow at the top right to go to other operators up to the source.

Figure 15 – Executed Plan detailed display

The exclusive time in the node refers to the time that is needed to process an individual operation. The inclusive time is the time required to process the entire operation including the time of lower order operators and excluding compiler time.

Load times with long processing times and large data quantities are potentially critical areas, and for this a detailed analysis of the underlying part of the data model is recommended. In our example, the left load step with 29,953 data records and a processing time of just 3 seconds is one of these.

Summary

As can be seen, with just a few simple steps a Plan Viz file can be created from the SQL code of a Datasphere object and analyzed. This is extremely helpful in identifying potential performance bottlenecks in the Datasphere and correcting them.

This post was first published at Nagarro (nagarro-es.com)