The hidden JSON-Hack for Database Hints in SAP Datasphere

Anyone who works with SAP Datasphere on a regular basis is familiar with this issue: a view doesn’t return the expected results or runs much slower than expected. In most cases, the cause can be quickly traced back to a user error or a misunderstanding of SQL logic. But what should you do if you’ve ruled out all the obvious causes and the problem persists? In this post, we’ll show you how to fix it.

In 99% of cases, the cause of the problem can be found in front of the monitor. In the vast majority of cases, this is due to user error or a lack of understanding of the underlying SQL logic. However, the remaining 1% involves a serious issue caused by a bug in the HANA DB Query Optimizer.

Fortunately, this error occurs only rarely. After five years of intensive work with SAP Datasphere and HANA, as well as dozens of successfully implemented projects, the error has occurred only three or four times. Nevertheless, we would like to share our experience on how you can respond in such exceptional cases.

First, we will address the underlying cause and show you how to use database hints to resolve the issue. In doing so, we will present a previously undocumented solution in Datasphere that allows you to use database hints in Datasphere objects.

Problem statement

The anomaly is caused by a bug in the HANA Database Optimizer. The database sometimes chooses a mathematical “shortcut” that is, however, logically invalid for the specific data context. For example, amounts are aggregated before currency conversion, which leads to incorrect results. This phenomenon occurs when the optimizer incorrectly forces an “aggregation pushdown.” However, this is a fallacy in the context of currency conversion. Since exchange rates are variable and can vary per data record (e.g., by date), summing the amounts before conversion results in an incorrect total. In this case, the optimizer “sacrifices” semantic correctness in favor of supposedly higher performance by reducing the number of rows to be processed.

To ensure that HANA executes the steps in the correct order - that is, performs the currency conversion first and then aggregates the values - you can use database hints. These allow you to override the SQL optimizer’s automatic selection and force a specific execution plan for the query.

What are Database Hints? 

Introduction

Database hints in SAP HANA are specific instructions used to influence or override the default behavior of the SQL optimizer. Normally, the optimizer independently determines which execution plan (e.g., which engine to use or how to perform joins) is most efficient, based on statistics and heuristics. A hint is used when this automatic decision - as in the case of incorrect pre-aggregation - leads to suboptimal performance or logically incorrect results.

Syntax

In SQL statements, hints are placed at the end, after the WITH HINT clause. For example, if we are dealing with bulk aggregations and want to use the OLAP engine, we can use USE_OLAP_PLAN.
SELECT * FROM TABLE1 WITH HINT( USE_OLAP_PLAN ); 

In CDS Views, the hints are defined in the header. For example:

@Consumption.dbHints: ['USE_OLAP_PLAN'] 

You can also define database hints for Datasphere artifacts such as analytical models in the following format:

"@Analytics.dbHints": [
  {
    "engine": "SQL",
    "hints": [
      {
        "key": "NO_PREAGGR_BEFORE_CONVERT_CURRENCY",
        "value": "true"
      }
    ]
  }
]

---

Watch the recording of our webinar "Bridging Business and Analytics: The Plug-and-Play future of Data Platforms"

---

Typical use cases of Databe Hints and reference

In addition to the execution engines (OLAP, HEX, ESX), you can also influence specific behaviors. For example, you can use the CS_AGGR and NO_CS_AGGR hints to control column store aggregation. The CS_AGGR hint instructs the optimizer to perform the aggregation directly in the column engine as much as possible. Instead of reading the data row by row, entire column blocks are aggregated simultaneously.

However, this optimization can lead to incorrect results when dealing with complex user-defined functions or specific join conditions. In this case, you can use NO_CS_AGGR to force the aggregation to occur outside the Column Engine.

You can find a complete list of DB hints in the SAP HANA SQL Reference Guide for the SAP HANA Platform and in SAP Note 2142945 - FAQ: SAP HANA Hints.

Finding the right Database Hint

But how do you find the hint that solves your problem from this endless list? Unfortunately, finding the right database hint often feels like methodical detective work. SAP does point out specific bugs in Note 2142945 - FAQ: SAP HANA Hints - that certain hints can resolve. However, the note by no means covers all the errors that can occur in day-to-day project work.

One systematic approach is to compare the SQL execution plan (PlanViz) of the faulty query with the plan of a manually corrected version. By creating an equivalent query that enforces the logically correct order, by using a hint, you obtain a reference model. A direct comparison of the plan steps in SAP HANA PlanViz then often reveals the problematic section.

Adding Database Hints to Analytical Models

Finally, we would like to show you how we resolved the issue with the incorrect currency conversion.

After a thorough analysis and with SAP’s assistance, we had to add the DB hint NO_PREAGGR_BEFORE_CONVERT_CURRENCY to our analytical model.

To do this, export the object's CSN/JSON definition in Datasphere Data Builder.

Open the file in the editor of your choice, e.g., Notepad++. Here you can see the object definition in JSON format.

Insert the following JSON notation into the definition of your Analytical Model:

            "@Analytics.dbHints": [
                {
                    "engine": "SQL",
                    "hints": [
                        {
                            "key": "NO_PREAGGR_BEFORE_CONVERT_CURRENCY",
                            "value": "true"
                        }
                    ]
                }
            ],

Now close the file and switch to the main screen of Data Builder. Here you can import the adjusted JSON file.

For details on the procedure, see our article SAP Datasphere JSON Hack: Transform Graphical to SQL View.

What to do when the HANA Optimizer delivers false results: Our Conclusion

Incorrect query results in SAP Datasphere are usually caused by a faulty application - but when the HANA Optimizer fails, technical expertise is required. In such cases, Database Hints provide the decisive lever to ensure data integrity in the event of bugs like faulty pre-aggregation. With the JSON hack described here, these corrections can now be implemented directly in Datasphere objects. Use this workaround as a last resort for truly stubborn cases.

Are you facing similar challenges in your Datasphere project, or do you need help analyzing complex performance and logic issues? Feel free to reach out - we look forward to finding the right solution together with you.