Why my SAP HANA Calculation Views do not perform as expected - and why BW/4HANA Queries become slow

 

Part 1: Calculation View Unfolding

When working with SAP HANA, especially in data-intensive environments such as BW/4HANA, even small performance bottlenecks can become a serious issue. When a Calculation View processes hundreds of thousands (or sometimes millions) of rows at runtime, even millisecond-level delays caused by suboptimal operations can easily turn into seconds or tens of seconds of unnecessary waiting time for end users.

What is often underestimated is that just a few commonly ignored aspects can significantly contribute to overall performance degradation. This is where the 80/20 principle works quite well.

One such often overlooked concept is Calculation View (CV) unfolding. I deliberately use the word concept here, because CV unfolding is not a runtime parameter you can simply switch on or off or a bad technique (like, for example, filters or joins on calculated columns). Instead it is a part of rule-based optimization process, and it is about how you can design a Calculation View so that the SQL optimizer can build the most efficient execution model.

Let’s start with a bit of theory.

SAP HANA is often referred to as a database, but in reality, it consists of multiple engines, both relational and non-relational, including the Column Engine, Row Engine, SQL Engine, Calculation Engine (CE), and several other highly specialized engines. Depending on your environment and project requirements, it may be useful to look into these engines in more detail.

To understand unfolding, it is important to understand the differences between some of these engines, although a deep dive into all of them is usually not required.

In a nutshell, CV unfolding happens when the SQL optimizer can translate a Calculation View model into a relational form that can be executed entirely in SQL.

An SAP HANA Calculation View is, by definition, a Calculation Engine object. It often contains CE functions alongside relational (SQL) operations and expressions.

So, what is wrong with non-relational engines?
In fact, nothing. Each engine is highly efficient at its dedicated task. However, from a cost and performance perspective, it is generally more efficient to execute the entire Calculation View within a single optimizer than to let multiple optimizers handle different parts of the model.

Put simply: it is cheaper to execute a Calculation View, together with all underlying views, in one engine that completes the optimization in a single pass, rather than letting two or more engines split the work and potentially switch optimizers multiple times during execution. Depending on model complexity, these switches can add noticeable overhead.

This is why unfolding is often beneficial, especially when working with large data volumes, as is commonly the case in hybrid BW/4HANA scenarios.

SAP provides a good visual representation of the unfolding process in the SAP HANA documentation.

Image copyright: help.sap.com

How to enforce Calculation View unfolding

The choice of execution engine cannot be fully enforced. Even “Execute in” option cannot force the SQL Engine to execute non-SQL expressions, such as native Column Engine functions. That said, in practice, we can almost always achieve the desired engine selection by following a few simple rules.

The most important one is this:

When implementing calculated columns and filters, prefer SQL expressions over native Column Engine functions.

Yes, the Column Engine language is the default choice (after all, you are working in a CE object), but you can easily switch to SQL and write your expressions using SAP HANA SQLScript syntax.

In many cases, this also results in cleaner and more readable code. For example, a routine that might initially look complex

can become much more self-explanatory when rewritten using CASE and COALESCE.

You may notice that parts of such code are highlighted in red and that neither CASE nor COALESCE appear in the function list. This can be confusing at first, but it is perfectly fine. These expressions are processed by the SQL Engine, which gives you access to a large portion of the SQLScript syntax.

For details on supported functions, refer to the official SAP HANA SQLScript Reference Guide. Naturally, reasonable limits apply. For example, you should not expect window functions to work inside a single-line calculated column expression.

This approach alone is often enough to ensure that a Calculation View can be unfolded.

Another common reason for preventing unfolding is overly complex expressions. I once encountered a conversion formula that blocked unfolding in an otherwise perfectly SQL-aligned Calculation View:

concat(  TO_NVARCHAR(YEAR("0DATE")),  LPAD(TO_NVARCHAR(MONTH("0DATE")), 3, '0'))

It is not always obvious why such cases occur, CE expression in the code or an SQL Engine fallback due to repeated concatenation and string conversion functions.

If you want to understand the exact reason in your case, you can set the “Execute in” option to SQL Engine and then analyse query optimization blockers using SQL Analyzer (see the SAP HANA Platform Guide for Developers for details).

In this particular case, a simpler version of the logic solved the problem:

YEAR("0DATE") * 1000 + MONTH("0DATE")

As a rule of thumb, if your Calculation View contains several calculated columns and filters, it is a good idea to periodically check Explain Plan during development to ensure that the view is still unfolded.

Below is an example of a not unfolded view. Although the test view is relatively complex, with multiple sources and joins, only three operators are visible in the plan, and no source tables appear:

The same view after resolving query optimization blockers and achieving unfolding:

Summary

There is nothing inherently wrong with a non-unfolded Calculation View. It is simply a design choice between relational and non-relational execution. A non-unfolded view does not generate errors or warnings, because it is not a problem by definition.

However, in many cases, an unfolded view leads to faster runtime optimization and a more straightforward execution model. In some scenarios, unfolding may not provide the best results, and in others it may not even be possible.

That said, in scenarios such as hybrid BW/4HANA modelling, it is almost always worth trying to design Calculation Views that can be unfolded. This is due to the relational nature of the sources (ADSOs, InfoObjects) and common reporting patterns such as joins or unions across multiple sources combined with calculated KPIs.

An unfolded view also helps ensure that filter pushdown works as intended - another powerful mechanism that is especially important in BW/4HANA environments.