Building calculation view using Business applicati...

Jay2 · ‎04-20-2021

After the release of the Business application studio(BAS) I was eagerly waiting for the extension which will help us to build calculation views, and finally, we can build using BAS. This blog post will help you understand how to connect the existing HDI container and build a calculation view on the tables in the respective schema.

Why should you use the Business application studio?

SAP Business Application Studio is a development environment available on SAP Business Technology Platform that is lightweight and developer-friendly. Building HANA-native artifacts using the BAS template-based approach is what makes it special.A desktop-like development experience in the cloud that accelerates development using wizards, optimized code & graphical editors, local test run & debug, terminal (CLI) access, quick deployment, and more.
Right from connecting, building, and deploying is super easy on BAS - which is every developer's dream.

Prerequisites

Setup Business application studio(BAS)

Setup SAP HANA Cloud

Access to SAP Analytics Cloud

Create entities using SAP Cloud Application Programming Model (CAP)

Before we jump on directly creating the calculation view let's build some tables using SAP Cloud Application Programming Model (CAP) and deploy them on the SAP HANA Cloud instance. You can use the following entities as is so that you can relate to the blog appropriately. You will be creating 3 entities Event, Event Participants, and User details.

entity EVENT : cuid,managed {

    desc : String;

    org_unit : String ;

    start_date: Timestamp;

    end_date : Timestamp;

    km_to_accomplish : Integer;

    event_participants : Composition of many EVENT_PARTICIPANTS on event_participants.event_id = $self;

}



entity EVENT_PARTICIPANTS :managed {

    key owner_id : Integer;

    key event_id : Association to EVENT;

    registered : Boolean;

}



entity USER_DETAILS:cuid,managed{

    sap_id : String;

    owner_id : Integer;

    username : String;

    resource_state : Integer;

    firstname: String;

    lastname : String;

    city: String;

    state : String;

    country :String;

    gender :String;

    weight : Integer;

    profile : String;    

    event_list : Association to many EVENT_PARTICIPANTS on event_list.owner_id = owner_id;    

}

Make sure these artifacts are successfully deployed and have some mock data within them which will be required to test the calculation views.

Setup Dev space in BAS

The first step is to create de space with SAP HANA Native application capabilities. As you can notice once you select this capability space will have extension SAP HANA Calculation view editor by default included in it and that's what we need.

Create the Dev space, after it's initiated i.e. started you can open your dev space.

Create HANA Database project

BAS supports templates-based creation of the HANA Database project and to create one please select project from the template on the Welcome screen and select "SAP HANA Database Project" and follow the 6 steps as mentioned below.

Note - Numbering 1-6 starting top left of the table

We are going to connect this HANA Database project to the existing HDI Container service in step 5. You can select your HDI container service in this step which will give you access to the schema underneath, and you can access the tables which you created earlier. After the successful creation of the project, you should now be able to see the connection in green color which is listed under explorer -> SAP HANA Projects(Step 6)

Time to build a calculation view

What I loved about BAS is the ready-to-use templates !! Building a calculation view is pretty simple to do so select View → Find Command. Please follow the following 3 steps to create your calculation view file.

Note - Create calculation view of type CUBE as only this type of views are supported in SAP Analytics Cloud

With this, we can now start editing the calculation view file which we just now created. The next steps will be adding a join and assign the respective tables and finally setting the node to aggregation. Follow the next 6 steps to configure your view and last step which our 6th step you can decide on what fields should be measures and dimensions.

Deploying calculation view

To deploy the calculation view use the Rocket icon shown in front of your HANA artifact which you can locate under Explorer → HANA Data Projects and you should get a successfully deployed message in your console at the bottom. Refer to the below image for your reference.

To verify the calculation view is deployed properly and validate the data you can open a database explorer and run the calculation view to visualize the data.

Wow!! That fantastic isn't it with a few of these simple steps we have created our first calculation view.

Steps to consume calculation view in SAP Analytics Cloud

Now it's time to consume this view in SAP Analytics Cloud. First will be needing some DB connection information which will be used to create a live connection in SAC.

Copy the service credentials from BTP cockpit as shown below-

Open SQL Console in DB explorer and grant the following roles and privileges to the technical user which will be used to create the connection in SAC.

To grant a technical user who can access the calculation view use the following commands

CREATE USER MHFC_USER PASSWORD "PASSWORD" NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;



// pass your schema name appended with #DI

// run this sql statement  in sql console respective to the hdb

set schema "A4AF128D36614EC0E6751AED14446#DI";



CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES;



INSERT INTO #PRIVILEGES (PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME) values ('SELECT','','MHFC_USER');



CALL "A4AF128D36614EC0E6751AED14446#DI".GRANT_CONTAINER_SCHEMA_PRIVILEGES(#PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);



DROP TABLE #PRIVILEGES;

Copy the Host address from the credentials which we had noted earlier from the BTP cockpit. Will be creating a Live HANA connection in SAC. Enter the technical username and password which we created in the last step. This should create a successful live connection to our database.

Let's create a model using this connection and check if we can access the calculation view -

Yes!! We can see the calculation view which we created "EVENTS_CALC_VIEW_TEST". Let's use this to create the model and finally a story to show some statistics as shown below.

Friends, it is very simple to use BAS to build calculation views and consume in SAC. Please give it a try and do drop your comments about your experience.

Reference-

BAS - https://developers.sap.com/tutorials/appstudio-onboarding.html

Connecting SAC to HANA Cloud - https://blogs.sap.com/2020/09/28/connect-sap-analytics-cloud-to-hana-cloud-in-three-clicks/

SAP CAP - https://cap.cloud.sap/docs/about/

bpnaidu1981 · ‎04-20-2021

Dear Jay,

I am not able to find "Data Preview" option on intermediate nodes of a calculation view in Business application studio.

Jay2 · ‎04-20-2021

Hi Bellam,

To do the data preview you need to open the HANA Data explorer. You can find the option to open the explorer as highlighted in the image also make sure your calculation view is deployed. I hope this will help you.

Kr,

Jay

former_member762003 · ‎08-16-2021

Thank you very much. Can you also share how have you created entities using SAP (CAP) modelling.

this_ajaykumar · ‎11-14-2021

SAP tools takes 10 times more time to do same things compare to other tools in the market. First of all it is so slow and many time tools not functioning correctly.

narengokal · ‎02-10-2022

Hi,

I have created a SAP Hana Calc View and have tried to execute your steps by running the SQL DB commands. You mention to specify your "schema" using the JSON code that exists against the instance created with a #DI - But my schema doesn't have a #DI at the end. Only HDI containers have a #DI at the end. So, when i enter my schema name with the #DI it doesn't exist. I have then tried using the HDI container with the #DI - This does work. But then im getting a priviledge error when executing this code:

CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES;

Im running this as DBADMIN.

Can you please advise ?

Thanks,

narengokal · ‎02-10-2022

Ok, I see there are some steps missing in the post.

I have done the below and it now works

Choose/select the HDI container you want to give access to within the DB SQL Explorer.

Right click and open a SQL Consol using 'Admin'.

Execute the above SQL statements in the post.

Furthermore the schema to be used has to be the HDI schema and not the schema that exists in the JSON script within the DB instance created as a 'User defined Service Instance'.

Thanks kindly,

brosenfelderhanaexpress · ‎03-02-2022

Hi Jay,

thank you for this articel! I have a further question. Is it possible with the BAS to develop a calciew which is deployed on an on-premise hana database?

Thanks kindly

SaranyaSampath · ‎06-10-2022

Hi Jay,

While executing below query I am getting insufficient privilege error.

CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES;

Detailed message shows following .

Missing privilege : Select

Object : _SYS_DI.TT_SCHEMA_PRIVILEGES

Object Type : TABLE

This privilege is for particular schema or for table which is exposed as calculation view, or for user .

And how to assign privilege. Please share steps.

Thanks & Regards

Saranya

minpyemoe_sap · ‎07-07-2022

Thank you very much, Jay. I followed your steps and managed to create a calculation view on top of the HANA database. I also noted that I did not had to create user and role for connecting the database from SAC as my HDI container deployment already create a user for it.

Jay2 · ‎07-07-2022

Thank you minpyemoe_sap for the feedback. Using DT user and password is also an option.

former_member710627 · ‎11-28-2022

Hello jayadnure , how do you manage DEV, QAS and PRD environmetns in calcualtion views on Business aplication studio.

¿what is the best practice to manage differetne environments data?

¿First you create calcualtion views with dev data , then you transport the virtual tables?

Thank you

narengokal · ‎09-19-2023

How i worked with mine is, i created DEV,QA and PROD spaces on BTP and accessed this within BAS. You can then connect to your DEV or QA or PROD space within BAS.

I then created DEV, QA and PROD on GIT and linked them on BAS. Ive used Unix terminal commands to commit my work to GIT and align the environments. I think you can use the graphical interface to do all this - Im not sure. Its been a while since ive done all this !