KB41081: Feature not supported: not allowed over OLAP VIEW: search without aggregation or grouping by expression other than view column” error occurs when running a report with an Analytical View joining fact tables and/or Attribute Views against SAP HANA

• Strategy

Feature not supported: not allowed over OLAP VIEW: search without aggregation or grouping by expression other than view column” error occurs when running a report with an Analytical View joining fact tables and/or Attribute Views against SAP HANA

SYMPTOM:
“Feature not supported: not allowed over OLAP VIEW: search without aggregation or grouping by expression other than view column” error occurs when running a report with an Analytical View joining fact tables and/or Attribute Views against SAP HANA 1.0 SP2.
A sample report is shown below:

Note: Objects labeled as AV-ANT use an Analytical View – this view is defined joining several tables but without using Attribute Views. For information about Analytical views and Attribute Views, refer to the following Strategy Knowledge Base document:
KB42052: Integrating MicroStrategy 9.3.0 with SAP HANA
When executing the report, the following HANA SQL error occurs:


Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. General error;7 feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expres Connection String: SERVERNODE=xxx.xx.xx.xxx: xxxxx;DSN=HANA AWS;UID=system;. SQL Statement: select a11."CALL_CTR_ID" CALL_CTR_ID,
 a12."CENTER_NAME" CENTER_NAME,
 a11."SUBCAT_ID" SUBCAT_ID,
 a13."SUBCAT_DESC" SUBCAT_DESC,
 sum(a11."TOT_DOLLAR_SALES") WJXBFS1,
 sum(a11."TOT_COST") WJXBFS2,
 sum(a11."PROFIT") WJXBFS3,
 sum(a11."TOT_UNIT_SALES") WJXBFS4
from "_SYS_BIC"."tuto-views/AN_SUBCATEG_MNTH_CTR_SLS_TABLES" a11
 join "_SYS_BIC"."tuto-views/AV_CALL_CTR" a12
 on (a11."CALL_CTR_ID" = a12."CALL_CTR_ID")
 join "_SYS_BIC"."tuto-views/AV_SUBCAT" a13
 on (a11."SUBCAT_ID" = a13."SUBCAT_ID")
group by a11."CALL_CTR_ID",
 a12."CENTER_NAME",
 a11."SUBCAT_ID",
 a13."SUBCAT_DESC"..
Error in Process method of Component: QueryEngineServer, Project HANA, Job 216, Error Code= -2147212544.

CAUSE:
It is a SAP HANA limitation that aggregation on an OLAP view with a function on a Key figure is not supported yet. Refer to http://scn.sap.com/thread/3143013 for more details.
The query hits AN_SUBCATEG_MNTH_CTR_SLS_TABLES Analytical View which is built on SUBCATEG_MNTH_CTR_SLS_TABLES fact table with a join to LU_CALL_CTR, LU_SUBCAT dimension (as highlighted in the SQL above). When any one join to the Attribute View is removed, the query works. Therefore, joining an Analytical View directly to one Attribute View works whereas when two Attribute Views are joined directly to an Analytical View that’s when the problem occurs.

ACTION:
Follow one of the approaches below to create attributes from the same view and avoid doing joins on the Strategy side:
1. Modify Analytical View to make the typical usage of it, which would be to create an Analytical view with a fact table and join to dimensions across where analysis would be done to include all the attributes needed for analysis from dimension tables.
2. Substitution of Calculation View. The basic idea of a Calculation View is the same as Action 1 where we hold Calculation View to contain fact tables and the joins to dimension tables, therefore, avoiding the necessity to create a join on the Strategy side and moving the join from Strategy to HANA. Refer to KB42052: Integrating MicroStrategy 9.3.0 with SAP HANA for further information of a Calculation View.

KB41081

Comment

0 comments

Details

Knowledge Article

Published:

June 27, 2017

Last Updated:

December 29, 2018

Error: SQL Generation Complete QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. General error;7 feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expres Connection String: SERVERNODE=xxx.xx.xx.xxx: xxxxx;DSN=HANA AWS;UID=system;. SQL Statement: select a11."CALL_CTR_ID" CALL_CTR_ID, a12."CENTER_NAME" CENTER_NAME, a11."SUBCAT_ID" SUBCAT_ID, a13."SUBCAT_DESC" SUBCAT_DESC, sum(a11."TOT_DOLLAR_SALES") WJXBFS1, sum(a11."TOT_COST") WJXBFS2, sum(a11."PROFIT") WJXBFS3, sum(a11."TOT_UNIT_SALES") WJXBFS4 from "_SYS_BIC"."tuto-views/AN_SUBCATEG_MNTH_CTR_SLS_TABLES" a11 join "_SYS_BIC"."tuto-views/AV_CALL_CTR" a12 on (a11."CALL_CTR_ID" = a12."CALL_CTR_ID") join "_SYS_BIC"."tuto-views/AV_SUBCAT" a13 on (a11."SUBCAT_ID" = a13."SUBCAT_ID") group by a11."CALL_CTR_ID", a12."CENTER_NAME", a11."SUBCAT_ID", a13."SUBCAT_DESC".. Error in Process method of Component: QueryEngineServer, Project HANA, Job 216, Error Code= -2147212544.