Using Tools to Check Model Performance

When you are developing a calculation view, the calculation view editor is able to highlight settings that might lead to poor performance.

To enable 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 relating to your choices, that might lead to poor performance so that you can consider alternative approaches.

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

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.

To examine the run-time 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 run-time 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 individual node to display the interim results. 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 even navigate to the lowest level of the entire modeled stack. This means you do not have to start the Debug Query mode in separate calculation views, you simply begin debugging at the top of the model and continue debugging to the lowest level.

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 is a great way of testing if union or join 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.

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?

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

The SQL Analyzer is launched from Business Application Studio.

1. To get started, you first generate a file that collects the run-time information that you will later analyze. To do this, launch the Database Explorer and then, either manually write, or generate the SQL select statement for the calculation view you want to analyze. Once you have the SQL select statement prepared, use the menu option Generate SQL Analyzer Plan File. You will choose a name for the plan file. The location is already set for you and you cannot change this. Other.

   

2. Next, locate the generated plan file by opening a catalog database connection and expand the folder Database Diagnostic Files until you reach the folder Other. Here you should find you plan file. Select the file and choose Download.

   

3. In the Business Application Studio, upload the plan file to any project folder by using the folder option Upload Files.

4. Switch to the SQL Analyzer. If you don't see this tool, you need to add it as an extension to your development space. To do this, close Business Application Studio and stop your development space. Edit the development space and add the extension SAP HANA Performance Tools. Start the development space and the tool should now appear.

5. Add the plan file.

6. Select the plan file to display the results in the main window.