Optimizing SAP Datasphere Performance for Large-Scale Data Systems

In the fast-paced world of data and Business Intelligence, ensuring that your systems run efficiently is crucial. This is especially true if your data warehouse has seen extensive use, contains many data models, or includes tables with millions of records; in such cases, system performance might start to decline. SAP Datasphere, a powerful data warehouse solution, offers performance monitoring tools to help keep your reports running smoothly and efficiently.

This blog will guide you through the essentials of performance monitoring in SAP Datasphere, including its importance, usage, default monitoring views, setting up monitoring spaces and views, and creating a performance monitoring dashboard.

Importance of Monitoring your SAP Datasphere system

Once your data warehouse is used by many developers and departments, it can be difficult to know which objects or spaces require the most attention. SAP Datasphere provides various ways to monitor the usage and performance of the data warehouse. The following two topics are a good starting point:

• Resource Management between Spaces & System Monitoring
  Effective performance monitoring helps manage system resources, ensuring they are used efficiently and preventing overuse or underutilization between spaces and data models.

• Identifying Models with High CPU Usage
  Quickly identify models that consume the most CPU resources. These models may need additional optimization if users are experiencing long loading times.

In the following sections, we will guide you through setting up these monitoring topics.

Optimizing Space Management in SAP Datasphere

To monitor the SAP Datasphere system, starting with the spaces is a good approach. By default, the space concept provides monitoring tools that offer insights into which spaces consume the most system resources. To access this information, follow these steps:

• In the side navigation area, click Space Management, locate your space tile, and click Edit to open it. Then, select Workload Management.

• To prioritize between spaces, enter the desired priority in the Space Priority section when querying the database. You can choose a value from 1 (lowest priority) to 8 (highest priority). The default value is 5. When spaces compete for available threads, those with higher priorities will have their statements run before those with lower priorities. 

To manage other workload parameters, select either Default or Custom from the Space Configuration dropdown list. Be aware that changing these parameters significantly impacts the performance of the SAP Datasphere system. SAP has configured workload classes to ensure the system never reaches 100% of its available capacity. If the system reaches full capacity, it becomes unresponsive to all developers and business users. Therefore, the statement limit that represents 100% is set at 80% of the maximum CPU capacity. When the system reaches 80% capacity, every subsequent query is queued until there is enough CPU available to handle the next task. If many users experience long wait times, it may indicate that the system is running at full capacity. To confirm this, you can check the overall system performance as described in the following section.

Monitoring System CPU Usage

To see if there is significant queuing in the system, SAP provides a default monitoring dashboard. If you have sufficient authorization, you can access this dashboard in the System Monitor tab.

 Here, the KPI ‘Admission Control Queuing Events Last 7 Days’ shows any queuing events that occurred over the past seven days.

If the number of queued statements is high, there are two possible solutions:

• Resize the System to Increase Capacity: More information on this solution can be found here.

• Analyze Tasks or Models with High CPU Usage: In the next section, we will explain how to perform such an analysis.

Identifying Models with High CPU Usage (MDS Trace)

To monitor models that use a lot of memory in the SAP Datasphere system, you must first enable Expensive Statement Tracing. Without this, important information about peak memory usage, CPU time, and out-of-memory errors will not be available. Note that these features are not available for regular users and require the DW Administrator role.

To enable Expensive Statement Tracing, perform the following steps:

• Create a <SAP_ADMIN> space. This space is dedicated to pre-configured monitoring views provided as business content by SAP via the Content Network. Create the new space with the Space ID <SAP_ADMIN> and Space Name <Administration (SAP)>, enable access to it, and import the package from the Content Network.

• Go to Configuration → Monitoring.

• Select a space from the drop-down list and click Confirm Selected Space.

• If you've created the <SAP_ADMIN> space and want to enable it, click Enable Access to SAP Monitoring Content Space. If there is no space named <SAP_ADMIN> in your tenant, this option will not be available.

• To trace expensive statements, select Enable Expensive Statement Tracing, specify the necessary parameters to configure and filter the trace details, then save your changes. After completing these steps, you can view MDS query information by clicking More in the Statement Details column of the Statements tab in the System Monitor.

Default Monitoring Views in SAP Datasphere

Since SAP Datasphere is built on top of HANA, it provides many monitoring tables that are readily available. These tables offer a quick overview of system performance and help identify potential issues. Once you have enabled Expensive Statement Tracing, you can find the Expensive Statements table in the <SAP_ADMIN> space.

Interesting information in the “M_EXPENSIVE_STATEMENTS” table includes CPU usage, query length, query size, and the number of executions.

Keep in mind that this table only contains statements with a duration longer than the threshold specified in Step 4. Additionally, this table is limited by default to 30,000 rows, but this can be adjusted in the same parameter section. More information about the “M_EXPENSIVE_STATEMENTS” table can be found here.

 Another useful table is the “M_MULTIDIMENSIONAL_STATEMENT_STATISTICS” table, which provides information on individual MDS statements executed in the HANA database. For example, you can fetch the MDS statement string from this table and execute it on the HANA database to create a Planviz. The Planviz can then be analyzed to identify performance issues in a data model. An example of a Planviz file extraction and analysis can be found here.

An extensive list of HANA monitoring views and their uses can be found here. 

Creating a Performance Monitoring Dashboard

If you plan to use these monitoring tools over a longer period, creating a data model and dashboard on top of the monitoring tables might be a good idea. A performance monitoring dashboard can provide insights into trends within the organization on a daily, weekly, or monthly basis. It can also help identify which data models consume the most CPU resources. To create a performance monitoring dashboard using the generated tables, follow these steps. (For steps 2 and 3, we assume you have an SAC tenant. If not, you can consume the view generated in step 1 using another frontend tool.)

Step 1: Create a graphical or SQL view on top of the “M_EXPENSIVE_STATEMENTS” table. Some columns with interesting information are:

• Execution Count

• Runtime

• Peak Memory

• Row Count

• Data Source Name

• Statement String (from this column, information like Story Name, Story ID, and Widget ID can be extracted)

• Last Execution Timestamp

Step 2: Create a Fact + Analytic Model so the data can be consumed in SAC.

Step 3: Create an SAC dashboard and visualizations on top of the exposed model. 

Additional Sources

This was only a first step into performance monitoring. Additional useful blogs that can help you out on this topic are listed below.

SAP Datasphere: Monitoring and Analysis of SAC requests (Ina/MDS)

SAP DataSphere – SAP HANA Database Monitoring

Conclusion

Performance monitoring in SAP Datasphere is a critical aspect of managing your data systems efficiently. By using the monitoring views, you can ensure optimal performance, proactive issue resolution, and effective resource management. With these practices in place, you can confidently manage and optimize your data operations in SAP Datasphere.

If you want to learn more about our approach to SAP Datasphere or are interested in participating in our hands-on training, please contact Joran de Vries at Joran.de.Vries@mccoy-partners.com.