Query in Analytic view creating unexpected results in SAP HANA

SAPSAP HANA

You haven’t provided complete details what you are trying to achieve. Refer SAP OSS note 1993033 Wrong result for query on analytic view

During activation of an analytic view, column view is generated. However, there are some artifacts that cannot be calculated in this column view. In this case, one additional calculation view is generated on top of it. The generation of the calculation view has to be performed if the following elements are defined in the analytic view:

  • Input parameters
  • Calculated attributes
  • Unit or currency conversions

If at least one of these elements is defined in the analytic view, the activation creates one OLAP and one calculation view. The query at run-time is always addressed to the calculation view, which himself retrieves data from the OLAP view. The calculation view gets data that have been aggregated and queries on calculated measures could return unexpected results depending on their formula and complexity.

There are three possibilities concerning the placement of calculated measures:

  • OLAP, calculation before aggregation (this has to be tagged explicitly in the analytic view)
  • OLAP, calculation after aggregation (the default behavior)
  • Calculation view

Calculated measures have to be calculated in the calculation view if the formula is using calculated attributes, input parameters or other calculated measures that require a calculation view.

Now the result of a query depends on the formula and where it is calculated. A formula could be transparent to the aggregation, e. g. if it multiplies the value of a measure with a certain factor: CM1 = M1 * 1000

The result is always the same, no matter ifthe formula is calculated before a (sum) aggregation or after.

However, this does not apply to formulas that add constant values, e. g. CM2 = M1 + 1

Explanation (as an example, there are 10 values for M1, each equals to 1000):

    Calculation before aggregation

    The calculation for each CM2 is 1001, the sum aggregation results to 10010

    Calculation after aggregation

    The result is 10*1000 + 1 = 10001

In case an analytic view defines calculated measures, some calculated in the OLAP view and some in the calculation view, a mixture of them could be dangerous depending on the formula.

Calculated measures in analytical views which do not commutate with their aggregation type can produce unexpected results - dependent on the particular query on this view. Queries requesting such a calculated measure may be dangerous if at least one of the following elements is used:

    Calculated view attributes in the group by definition

    Requesting a unit/currency conversion measure

    Requesting a count distinct measure

    Stacked SQL queries on an analytical view, e.g.

SELECT SUM(CALC_MEASURE) FROM (

SELECT SUM(CALC_MEASURE),A

FROM AVIEW GROUP BY A)

      

Solution −

In case such complex queries will be used on analytic views as described above, the calculated measures need to be handled carefully and should be moved to an additional calculation view that needs to be modelled on top of the analytic view. The analytic view serves as a data source to the calculation view which defines the calculated measures. Calculated measures with the option "calculate before aggregation" must be kept in the analytic view.

raja
Published on 26-Dec-2017 00:00:56
Advertisements