Data Preview of Analytic View in SAP HANA Cloud Platform (Trial Account)
Background
I started using SAP HANA cloud platform (trial account) in recent times to understand the concepts of SAP HANA. Using the instructions given at the Development center & other SCN blogs, I was able to create a trial account, HANA instance & install HANA studio using eclipse successfully. Using few examples, I could even create columnar tables & information models like attribute View, analytic view successfully.
However, the main challenge was to do the “Data Preview” of these HANA models especially the analytic views. In a trial account, we are not given all the privileges of a regular HANA instance. So we usually get insufficient privilege error through a regular method. After numerous tryouts, analysis, research, look ups, I was able to overcome this disability using an alternate method of data preview which I will be discussing in this blog.
Create columnar Tables
As per the example in the sap HANA tutorials (see reference for the link to the tutorial) below tables are created in the SAP HANA Cloud system under the schema “NEO_*************************” using SQL console. We will use 2 dimension tables – Product & Region and connect it to the fact table – Sales using an analytic view.
Note:- Do not create any schema objects like tables, procedures etc. in the schema “DEV_*******************” of the HANA cloud system. Also do not forget the grant the select to schema “NEO_****************” to _SYS_REPO with grant option.
Create an Analytic View
Using the SAP HANA Modeler perspective, create an analytic view – AV_SALEREP using the sales fact table & 2 dimension tables as shown below.
Since we are not using an attribute view, all the 3 tables can be joined at the Data Foundation level itself using referential join. Add the required columns to the output in Data Foundation and assign the fields as “Attribute” & “Measure at the Semantics layer.
Create a dummy Calculated Attribute – Analytic View
As per the privileges assigned to the developers in the trial HANA account, only calculation views can preview the data. So we need to convert the analytic view into a calculation view by creating a dummy Calculated Column – Attribute (not the measure) as shown below:-
As you see, I created a calculated column – CALC_ATTR_DUMMY_FLAG with column type – Attribute, Data Type – varchar(1) & value of ‘1’.
Save, Validate & activate the analytic view.
Select Rights for Activated Modeled Views
Open the SQL console of the system and execute the below statement to provide the “SELECT” rights to the developer user for all the modeled views in the developer package (check the references for additional info). Once it is executed successfully, refresh the system.
CALL “HCP”.”HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS”;
Data Preview
Now we can preview the data of the analytic view through the schema _SYS_BIC by accessing the path :-
System –> Catalog –> _SYS_BIC –> <package>/<analytic view>
You can use all the features of the data preview from here – Raw Data, Distinct Values & Analysis.
Note: Variables & Input parameter functionality is not working using this approach.
Reference:
- SAP HANA Cloud Platform (Developer Center) – http://hcp.sap.com/developers.html
- SAP HANA Tutorial – http://saphanatutorial.com/sap-hana-training-basic-tutorial/
- Specific Procedures and Views – https://help.hana.ondemand.com/help/frameset.htm?2cb80530a7a748f792050730782f9bef.html
Thank you, Dinesh for the tutorial. Especially for pinpointing the limitations of the HANA trial version and showing the ways to overcome them.
Dinesh
Can you please expand on. I am struggling to create the dummy view
Create a dummy Calculated Attribute - Analytic View
As per the privileges assigned to the developers in the trial HANA account, only calculation views can preview the data. So we need to convert the analytic view into a calculation view by creating a dummy Calculated Column - Attribute (not the measure) as shown below:-
As you see, I created a calculated column - CALC_ATTR_DUMMY_FLAG with column type - Attribute, Data Type - varchar(1) & value of '1'.
Save, Validate & activate the analytic view.
You need to create a "Calculated Column" (Type - Attribute) in the analytic view. And assign with any length & default value. I am just saying it as dummy because it will used just to choose the calculation engine instead of OLAP engine for data preview.
Let me know if you have more questions.
Regards
Dinesh
Hi Dinesh,
very useful article and came to my rescue today when i am facing the same issue 🙂
hi Dinesh,
I am using an HCP trial account and am creating a simple calculation view with just a fact table and trying to do data preview after activation, but still it shows "insufficient privilege".
any inputs?
Thanks,
Pratik