Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Query in Analytic view creating unexpected results in SAP 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.
179 Views
