Check on the missing Analytical Privileges of a calculation view
Use case: You are a HANA system administrator/developer and got to work on an Authorization issue in SAC/BOBJ reporting live model based on HANA Calculation view for a business user. The CV has several transactional and dimensional CVs used and It is hard to identify which object has analytical privilege issue.
In the above picture, in CV_GL_REPORT there are 4 other CVs used and the user getting an authorization error on this CV due to not having access to one or more CVs that are being used here.
Solution: SAP provided a system view EFFECTIVE_STRUCTURED_PRIVILEGES to check the missing privileges on the CV. In the DB explorer tool, you can run the SQL statement below to get the analytical privileges check done.
select * from effective_structured_privileges where root_object_name = 'CV_GL_REPORT' and root_schema_name = 'EDW' and user_name = 'SSURAMPALLY';
It requires an equal predicate on ROOT_SCHEMA_NAME, ROOT_OBJECT_NAME, and USER_NAME. So must provide them in WHERE clause.
On the execution of the SQL, you will get a result of check status for each CV that is used in CV_GL_REPORT with status Applied or Not applied.
With that result, you can fix the authorization error by adding the object to one of the Analytical privileges that is created for this model purpose.
Note: This is applicable for HANA XSA(On-premise) and HANA cloud(Cloud) products using either DB explorer in Web IDE or Business Application Studio.
I have created an calculation view however I am getting below error during drag and drop the fields in Labels axis or Value axis.
and also check the missing privilege using SQL query. Please guide me what privilege is missing and where to be assign missing privilege.
Analytic Privileges already assigned to the user.
Please help us to resolve the issue.
Hi, _SYS_BI_CP_ALL is for analytical privileges that is specific to data level access. But first, you will need to provide access on the object itself. Please add your CV in the object privileges and grant select access.
Hi Bablu Patra
I have exactly your issue.
I have applied all the steps mentioned before, and don't solve my problem but I found a solution after all.
The solution was very simple. You only need change the Type of privileges for the Calculation View (CV).
1.- Save and run your CV.
2.- Clic in Semantis section to be able the Deails workspace.
3.- Use the tab View Properties and go down in the scroll bar to find the "Apply Prvileges" combo.
4.- Select "Classical Analytic Privileges".
5.- Save and run.
I hope this can help you.