Loading

Incorrect Aggregate Measures are Returned when Running Query from Live SAP HANA Connection

Publiceringsdatum: Mar 7, 2025
Beskrivning

When using a live connection to a SAP HANA view, the values of aggregate measures in queries are incorrect; however such query results are correct when using an extract connection.

 

Cause

When Hana processes SQL queries for a Calculation View that is built using an Aggregation node it may perform computations using aggregate values, where under normal SQL semantics detailed values would be used. For example, for a calculated field defined in Tableau as price*quantity, the calculation performed by Hana may be SUM(price)*SUM(quantity) instead of SUM(price*quantity).

Calculation Views reside in the schema "_SYS_BIC".

With log level “debug” or higher, when Tableau connects to a calculation view it will log a line with the following message: "HanaConnection::CheckAnalyticView: Detected an analytic view of type CALC"

 

Lösning

This behavior is dictated by the configuration of the view in Hana Studio. If it is a Calculation view that includes an Aggregation node, modify the view in Hana Studio by enabling the "Keep Flag" for all columns in the Aggregation node that are part of the data's primary key. This will cause Hana to process queries with a level of detail that includes these columns.
 

If modifying the view is not viable, try one of the following workarounds:


Option 1

Use an extract instead of live connection, being sure to include all dimensions that are part of the data's primary key.
 

Option 2

Avoid using SAP HANA Views as a data source. Instead, use the original table that the View was created from.
 

Option 3

(Please note that this third workaround may have an impact on performance)
The aggregation behavior can be circumvented by doing a “SELECT *” on the View. This can be done with a Custom SQL data connection (as opposed to direct connection to a View). For example:

  • Suppose you had an Analytical View named “User/SUPERSTORE_AV”
  • Then the custom SQL would be SELECT * FROM "_SYS_BIC"."User/SUPERSTORE_AV"
     
Knowledge-artikelnummer

001472896

 
Laddar
Salesforce Help | Article