Modeling in SAP HANA Cloud

Using Tools to Check Model Performance

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Use tools to check model performance

Introducing the Performance Analysis mode

When building a calculation view, it is possible to have SAP HANA automatically highlight areas that might lead to poor performance. To do this, you use the Performance Analysis mode. This tool is launched from inside the calculation view editor in the toolbar (the icon looks like a speedometer). You do not need to activate or build a Calculation View in order to use this tool. The basic idea is that you are able to react at design time to warnings and other information that is being passed to you that might lead to poor performance so that you can consider alternative approaches.

Note
Some optimization tools require you to first build the calculation view. This is not true for Performance Analysis mode, which analyzes the design-time object.

Performance Analysis – Detailed Information

You switch your calculation view to Performance Analysis mode by pressing the button that resembles a speedometer in the toolbar. Once you do this, the Performance Analysis tab appears for all nodes except the semantics node. Choose this tab to view detailed information about your calculation view, in particular the settings and values that can have a big impact on performance.

Examples of the information presented on the Performance Analysis mode tab include:

  • The type of tables used (row or column)

  • Join types used (inner, outer, and so on)

  • Join cardinality of data sources selected (n:m and so on)

  • Whether tables are partitioned and also how the partitions are defined and how many records each partition contains

Note
In SAP Web IDE Preferences, you can set a flag that causes all calculation views to open in Performance Analysis mode by default. This can be useful to ensure design violation warnings are not missed by the modeler.

When you leave Performance Analysis mode, the Performance Analysis tab disappears from all nodes.

Performance Analysis Validation Warnings

As well as information about the calculation view, Performance Analysis mode also provides you with warnings such as the following:

  • The size of tables with clear warnings highlighting the very large tables

  • Missing cardinalities or cardinalities that do not align with the current data set

  • Joins based on calculated columns

  • Restricted columns based on calculated columns

This information enables you to make good decisions that support high performance. For example, if you observe that a table you are consuming is extremely large, you might want to think about adding some partitions and then apply filters so you process only the partitions that contain data you need.

Note
The tool does not support performance analysis for join view nodes that contain multiple join definitions.

Setting Number of Rows Threshold

In the figure, Setting Number of Rows Threshold, you set the threshold in the Modeler preferences of SAP Web IDE.

Introducing the Debug Query Mode

To examine the behavior of a calculation view when it is called by a query, you use the Debug Query mode. In Debug Query mode, you can interrogate each node in the calculation view to see how the SQL is generated.

When you switch to Debug Query mode, a generic query is automatically created to call the calculation view. This generated query simulates a reporting tool or application that is sending a query to the calculation view. The generated query requests all columns of the calculation view and does not have filters. In other words, the generated query requests everything from the calculation view. But you can modify the generated query if you wish, for example, to remove columns or add filters to simulate a user requesting different views of the data.

Once you execute the generated query on the calculation view, you can then view the SQL that is generated for each node of the calculation view and even execute the SQL at each node if you wish to see the interim result for the node. This helps to ensure that your calculation view is behaving as you’d expect, or perhaps to investigate a performance or output issue that has been reported. By stepping through the nodes one by one, examining the SQL, you can check if the expected query is generated. For example, you could see how a WHERE clause is added to the query when a filter is needed, or check how a PLACEHOLDER is behaving when using an input parameter to pass a value.

Using Debug Query mode, you can drill down to the lowest level of the entire modeled stack using the graphical editor of the calculation view. This means you do not have to run the Debug Query mode in separate calculation views, but you can start the debugging at the top of the model and continue debugging to the lowest level in the same flow.

One of the most useful things you can discover when using Debug Query mode is to see how each node is pruned of columns and even complete data sources under various query conditions. For example, this mode is a great way of testing if union pruning is working as you would expect by running queries that request different data sets.

Debug Query Mode

You can reset the top level debug query in the Semanticsnode at any time using the reset button, which is adjacent to the Execute button.

Note

Remember that the Debug Query mode calls the active version of the calculation view. This means that if you have made changes to the calculation view but not yet rebuilt it, then you switch on Debug Query mode, the debug results will not reflect those latest changes.

Work with Query Debug Mode

Query Debug Mode

Watch this video to learn about Query Debug Mode.

Working with Performance Analysis mode

Performance Analysis Mode

Watch this video to learn about Performance Analysis Mode.

Introducing the SQL Analyzer

Although your calculation view may appear well designed, ultimately it is the performance of the calculation view that really counts. You have seen how to display the generated SQL in a calculation view using the Debug Query mode. While this is helpful to see how filters are pushed down and data is pruned, this does not tell us is how the SQL actually performs. We really need to know the answers to the following questions:

  • What was the total execution time?

  • How long did each step take?

  • Are there any steps that are taking significantly longer than other steps?

  • Are there any bottlenecks?

  • How many records did each step process?

  • In what sequence are the steps carried out?

  • Which operators are being called?

  • To what extent was SAP HANA Cloud able to parallelize the query?

  • Which processing engines are being invoked?

  • Were all the query plan steps unfolded?

To help us learn more about the performance of the SQL, we can use the Business Application Studio tool SQL Analyzer.

In addition to analyzing the SQL of calculation views, the SQL Analyzer can be used wherever SQL is defined, for example, in functions.

The SQL Analyzer is launched from Business Application Studio.

To use the SQL Analyzer, proceed as follows:

  1. Open the Database Explorer and generate the SQL for the calculation view you want to analyze. Then use the menu option Generate SQL Analyzer Plan File

  2. Locate the generated plan file and download it.

  3. Launch Business Application Studio and upload the plan file to any project folder.

  4. Switch to the SQL Analyzer

  5. Add the plan file to the SQL Analyzer. The SQL analysis will then appear.

Save progress to your learning plan by logging in or creating an account