Analytic view - Error

[hardik_patel]

Hi,

Here is my query and error. Can anyone help?

Generated Data Preview Query:

SELECT TOP 1000 sum("FORCURAM") AS "FORCURAM", "MANDT", "FORCURKEY", "AGENCYNUM" FROM "_SYS_BIC"."sflight/AN_AGENCY_ID_REV" GROUP BY "MANDT", "FORCURKEY", "AGENCYNUM"

Error:

Error Executing Query: SAP DBTech JDBC: [391] (at 97): invalidated view: sflight/AN_AGENCY_ID_REV: line 1 col 98 (at pos 97)

I activate and validate my view but then i get the same error.

Thanks for your help.

Regards,

Hardik

[former_member182277 · Accepted solution]

Hello Hardik,

One suggestion form my side,

Whenever you are trying to work with any schema, please ensure that that schema should added in the _sys_repo under authorization with select and execute markes as Yes, you can choose other option also like insert, update delete as per requrement.

Hope it is useful.

Regards,

Neha

[hardik_patel · Accepted solution]

Hi Neha,

Thanks for your reply. I am able to see the data preview for my attribute view and for analytic view.

I have one question: you said I need to ensure that the schema should added in the _sys_repo under authorization with select and execute markes as Yes.

How can i check that? Can you please help?

Thanks again.

Regards,

Hardik

[former_member182277 · Accepted solution]

Hello Hardik,

Sorry for late reply..

In the SYSTEM user that we consider as a admin user in HANA _sys_repo is added in the SQL Priviledges.

If suppose that is not added in the SQL Priviledges and then you need to add the schema name and execute and select option is the basic option that we are selecting while addding the schema so that we can use the same schema and activate too.

If you want that yo want DML authorization also then you can check insert, update delete as a Yes.

Its all about authorization.

Suppose you have created one schema and you dont want that no-admin use your schema for modeling then you will not add the same in the SQL priviledges of the non-admin user.

If you want some user to view user schema then you can add the same in the SQL Privieldge of the same user so that the person is authorized to use the schema.

Hope it is clear.

Please let me know if you want furthur clarification on same.

Regards,

neha

[Former Member]

Hardik,

In SQL, field names should not be written in quotes. Change the query as shown below:

SELECT TOP 1000 sum(FORCURAM) AS "FORCURAM", MANDT, FORCURKEY, AGENCYNUM FROM "_SYS_BIC"."sflight/AN_AGENCY_ID_REV" GROUP BY MANDT, FORCURKEY, AGENCYNUM

Thanks,
Satya.

[hardik_patel]

Hi Satya,

thanks for your reply. I copy the SQL query and error from info button. I right click on Analytic view and select Data Preview.

As per your suggestion, i tried to run the query in SQL editor but got the same error:

Could not execute 'SELECT TOP 1000 sum(FORCURAM) AS "FORCURAM", MANDT, FORCURKEY, AGENCYNUM FROM ...'

SAP DBTech JDBC: [391] (at 89): invalidated view: sflight/AN_AGENCY_ID_REV: line 1 col 90 (at pos 89)

Regards,

Hardik

[Former Member]

Hi Hardik,

The column views that are created during the deployment of analytic models are owned by the user _SYS_REPO.Therefore, the user _SYS_REPO needs to have select privileges '''with grant option''' on all the underlying tables. If all tables lie in the schema SYSTEM it is sufficient to execute the following SQL command:

grant select on schema SYSTEM to _SYS_REPO with grant option

Regards, Rahul