Optimizing Performance of Sap Hana Calculation Views

BW on HANA simplifies SQL data access for external tools by generating Calculation Views from BW objects. However, performance issues can arise when using relational SQL to handle multidimensional BW scenarios. This blog offers technical tips to improve runtime performance in BW 7.5 on HANA SPS 10, 11, and 12.

Sap Hana Calculation View Performance Optimization

SAP provides the “SQL Statement Collection for SAP HANA”, which you can download from SAP Note. You will get a zip file which can be imported and used within HANA Studio, for instructions see this.

The assortment consists of a range of valuable database evaluations.

• As a starting point, you can execute the “MiniChecks” from within folder “Configuration”. The result indicates whether there is an issue you should look into more closely and it also points to the appropriate documentation. Further information how to interpret the results can be found in SAP Note.
• Folder “SQL” gives performance related information like historic expensive statements and plan cache usage.
• The Folders “BW” and “Tables” include some basic checks to make sure there are no inconsistencies or other issues related to your tables.

Apart from the checks performed in HANA Studio, there is a helpful tool called RSDU_TABLE_CONSISTENCY that can identify and resolve various issues specific to HANA, such as inefficient indexes, partitioning, or compression. To use this tool, run it in store mode to generate a list of problems found. Then review each entry and select the ones you want to fix using the context menu. Finally, run the tool again in repair mode. Most issues can be automatically resolved, but some may require manual intervention. SAP recommends running this report regularly, like once a month.

• • Column tables are optimized for query read access, feature advanced compression and can be partitioned and distributed through all nodes in scale-out environments.
  • In contrast, row store tables are more suitable for write operations of full data records. However, they always reside on the master node, which can become a performance bottleneck. That is, because A) for row store traffic there is no load balancing in scale-out environments and B) the more space your row tables consume, the less free memory remains for calculations, which can potentially lead to out-of-memory errors.
  • What’s more, there is an implication for the query execution plan, which you can display via HANA Studio “Explain Plan” feature. Most operations take place in column engine, however if you are using row store tables, then switches between execution engines are necessary, see screenshot below:

The issue arises when there are switches between engines, as it requires the temporary materialization of all intermediate results. This can significantly impact performance. Hence, it is recommended to utilize column tables exclusively for better efficiency.

Be cautious of the limitation of 2 billion rows for SID tables. If your tables exceed this size, they will need to be partitioned, which can result in a decrease in performance. It is important to regularly optimize table distribution to evenly distribute the workload on a scale-out system. This helps prevent issues such as new/change/active DSO tables not being kept together, which can lead to performance degradation during data loading or activation. Additionally, consider utilizing advanced tools like the Data Distribution Optimizer for improved efficiency.

In general, there is always a trade-off between speed and memory usage when it comes to SAP HANA Calculation View performance tuning. Since the cost of HANA memory is high and indexes can become quite large, it is advisable to avoid unnecessary ones. If removing multi-row indexes is not possible, an alternative approach could be reducing them to single-column indexes. Another option worth considering is converting indexes to inverted hash, which has the potential to consume 30% less space. However, we have yet to test this in practical scenarios and cannot confirm its effectiveness at present.

How to evaluate the efficiency of a calculation view in SAP HANA?

To improve the performance of a SAP HANA calculation view, follow these steps. First, open the calculation view in the view editor. This is where you can make changes to optimize its performance. Next, go to the menu bar and look for an option called “Switch to Performance Analysis Mode.” Click on it to enable this mode.

Once you have switched to Performance Analysis Mode, a dropdown list will appear. From this list, select “Run Performance Validations for this View.” This step will analyze your calculation view and provide suggestions on how to enhance its performance.

Overall, following these steps allows you to assess and fine-tune the performance of your SAP HANA calculation views effectively.

3. Parameter Settings

It is crucial to ensure that the parameters in the Administration Console -> Configuration are appropriately configured as they greatly influence performance.

• The following parameters improved both SQL performance and stability on our system:
• This is just to give you an idea, please do not simply reuse all those exact settings. You will have to adjust all values according to your system size and setup. Additional recommendations are given in this blog.
• You can always use the following statement to display an overview about custom settings that have been modified from standard configuration:select * from sys.m_inifile_contents where layer_name = ‘SYSTEM’

How can I diagnose performance problems in Hana?

To verify this information, navigate to the Performance tab in SAP HANA by clicking on the Administration icon. Within this tab, you will find various options to monitor and analyze system performance. One of these options is Threads, which allows you to examine the number and status of threads running in your SAP HANA system.

Sap Hana Calculation View Performance Optimization Techniques

We tested a lot of HANA revisions and also implemented quite a few hundred SAP Notes in BW backend lately. We can certainly say that the revision and patch level you are on makes a huge difference for SQL runtime performance.

– It is advisable to always install the latest BW Support Package and HANA revision, if feasible.

– Recent HANA revisions have made significant improvements in executing external SAP HANA Views, particularly in terms of unfolding.

– Unfolding involves determining the execution plan for specific objects with more precision. Instead of performing a single search operation on a View, processing is divided into multiple steps and engines. The screenshots below illustrate explain plans for the same scenario executed on different HANA revisions:

– SPS 11 – one search operation: [screenshot]

SPS 12 introduces various operations and engines that can be used for different purposes.

As a result of this, certain situations experienced a significant boost in speed, with performance improving by 2-3 times.

Sap Hana Calculation View Performance Tuning is an important aspect to consider when working with HANA Views and SQL. It involves optimizing the technical performance of these views for better efficiency and faster execution. By making specific adjustments and enhancements, developers can improve the overall performance of their calculation views in Sap Hana. This includes techniques such as indexing, partitioning, aggregating data, using appropriate join types, and implementing filters effectively. These optimizations help in reducing query response time and enhancing the overall user experience with Sap Hana Calculation Views.

Enhancing the Efficiency of a Report Utilizing SAP HANA Data

To optimize the performance of SAP HANA calculation views, it is important to consider certain factors. One crucial aspect is selecting the appropriate table type based on its usage. Different table types have specific characteristics that can impact performance.

Another technique for improving performance involves assigning range, hash, and round-robin partitions to large tables within the calculation view. By doing so, the amount of data brought into working memory can be limited, resulting in better overall performance.

1. Choose the right table type: Selecting an appropriate table type based on usage helps optimize performance.

3. Partition large tables: Assigning range, hash, or round-robin partitions to large tables limits data brought into working memory and improves overall efficiency.

Ways to enhance my Hana performance

Furthermore, when creating indexes, it is advisable to limit the number of columns included in an index. Including too many columns in an index can increase memory consumption and slow down query execution. By carefully selecting only the essential columns for indexing, you can reduce memory overhead and improve overall performance.

Which tool is recommended for assessing the performance of Hana views?

SAP HANA Cockpit is a web-based tool designed specifically for monitoring and administering SAP HANA. With its user-friendly interface, it allows administrators to efficiently manage their SAP HANA systems. This powerful tool provides comprehensive performance monitoring capabilities, enabling users to closely monitor the health and performance of their databases.

One of the key features of SAP HANA Cockpit is its ability to provide real-time insights into system performance. Administrators can easily track various metrics such as CPU usage, memory consumption, disk I/O operations, and network traffic. These metrics are presented in intuitive graphical representations that allow for quick identification of any potential bottlenecks or issues affecting system performance.

P.S: The use of SAP HANA Cockpit simplifies the management process by providing a centralized platform for monitoring and administration tasks. Its robust performance monitoring capabilities enable efficient troubleshooting and optimization efforts while ensuring optimal system health at all times.

Katy