Consuming SAP HANA data into SAP Analytics Cloud t...

vbollineni · ‎02-05-2020

Hello,

I am Venkata Saikrishna, working as part of Product Management in SAP Analytics Cloud area.

A lot of colleagues, partners and customers have been often asking me questions like following:

"How easy it is to configure the live connection between SAP Cloud platform HANA to SAP Analytics Cloud?"

What are those precise list of privileges to be assigned to the user to be able to create a connection and to be able to consume the SAP cloud platform HANA data live into SAP Analytics Cloud?

Can you show us a guide which describes end-to-end process of right from creating a SCP HANA Database until consuming that content into SAP Analytics Cloud?

After going through all these questions, I wanted to put up all these topics together with and end-to-end blog post to make it easy for our community, so that all of us could learn, how powerful SAP Analytics Cloud is and how easy it is to setup the whole interoperability.

Disclaimer: SAP does not recommend to use the trail version of SAP HANA or any trail versions of SAP products to be used by customers for any official purposes. This is purely to showcase the possibilities around live connectivity between SAP HANA and SAP Analytics Cloud.

This blog post would help you to give an end to end understanding of how live data from SAP HANA DB cloud can be consumed and all the technical aspects involved into it.

Pre-requisites:

- 1. SAP Analytics Cloud tenant with admin access
  2. An SAP HANA DB.

The steps involved in this process of consuming the live SAP HANA data into SAP Analytics Cloud are:

- 1. Create an SAP HANA DB
  2. Create user with necessary privileges.
  3. Create a Schema & Table
  4. Load data into the table
  5. Add permissions to the SAP HANA user
  6. Create calculation view
  7. Create the live connection in SAP Analytics Cloud
    1. Caution: Select the right environment during the connection creation
    2. Use the same DB user in connection dialog.
  8. Create a model
  9. Create stories on top of the model.

- - 1. Create a SAP HANA DB: I assume that you already have a SAP HANA DB created. If not, one must need to create a DB to execute the steps further
    2. Create user with necessary privileges:

- - - - Login to the cockpit : https://account.hana.ondemand.com/cockpit/#/home/allaccounts
      - Choose the SCP account
      - From the menu at the left side, choose: "Databases & Schemas"
      - Click on your DB name
      - Now click on the "SAP HANA Web-Based Development Workbench"
      - Login to the security section in SAP HANA workbench with "system" user.
      - Choose "Security" tab.
      - Right click on "Users" -> "New user" -> Enter the user name of your choice along with the password.

Note: schema is automatically created in SAP HANA while creating the user.

- - Below are the Privileges you will need to add to the user:
    - Catalog privileges are to be able to create table
    - Editor privileges are to be able to create Calculation views.

Note: In SAP HANA, I am creating the user with name 'SAI' and I will be using this throughout the blog post.

3. Create Table: (Based on your data)

- - Go to Catalog in SAP HANA workbench. Login with the user created above.

- - Below is an example SQL statement to create the table.

CREATE TABLE table_name(

Order_ID DOUBLE,

Date VARCHAR(100),

Sales_Agent_Last_Name VARCHAR(100),

Sales_Agent_First_Name VARCHAR(100),

Customer VARCHAR(100),

Customer_Segment VARCHAR(100),

Country VARCHAR(100),

Latitude DOUBLE,

Longtitude DOUBLE,

Customer_Status VARCHAR(100),

Product VARCHAR(100),

Product_Type VARCHAR(100),

No_Customer_Meetings DOUBLE,

Units_Sold DOUBLE,

Order_Value DOUBLE

);

- - I have created the below table with the table name "SALES_REPORT".

4. Load data into the table:

Now to load the data into SAP HANA tables, I am figuring our different possible ways.

For my test purpose I have just converted the records into SQL statements and inserted them.

I will update this blog post with the other possibilities of loading the data, as soon as I have the exhaustive list.

5. Add necessary permissions to the SAP HANA user:

- - Login to SAP HANA workbench with "SYSTEM" user.
  - Go to security tab
  - Add the below roles for "SYSTEM" user.

- - Go to catalog now again (Make sure you are going to the catalog with system user)
  - Execute the below SQL to assign INA role: (In this example, I am assigning all the necessary roles to the user SAI that I've created in the beginning.)
    - Call GRANT_ACTIVATED_ROLE('sap.bc.ina.service.v2.userRole::INA_USER','SAI');
    - grant MODELING SAI
  - Execute the following SQL statements with SAI user.
    - grant select, select metadata, execute on schema SAI to _sys_repo with grant option;

6. Create Calculation view:

- - Login to SAP HANA workbench with SAI user(SAP HANA user which is a non-system user).
  - Go to editor section.
  - Create a new package:

- - Enter Package name:

Create a new Calc view:

- - Calc view is now created:

- - Under 'mapping', I have added the data source:

- - Click on the calc view(e.g. sales report). It displays all the fields/data sources as shown below:

- - Right click on "Calc view" (e.g. sales report) and choose "Add to output".

- - It maps all the fields to output:

- - Now Save the calc view
  - Activate the calc view, by right clicking on the Calc view -> Activate:

Now that I am done with all the required configurations on SAP HANA, I will need to execute the further steps on SAP Analytics Cloud.

7. Create live connection:

- - Login to the SAP Analytics Cloud tenant
  - In the menu, go to Connections -> Click on '+' button to create a new onnection.
  - Explore the "Connect to Live data"
  - Choose "SAP HANA"
  - Enter all the fields like shown below:

8. Create model in SAP Analytics Cloud:

- - Using the live connection I have just created, I will now create a live model.
  - Choose "Get from a data source" and then "SAP HANA". As the data source on the right side of the screen.

- - Search for the data source. Here the data source is nothing but the calc view that I have created in the SAP HANA. (E.g. Sales report)

- - Now the modeling page opens where the 'meta data' page can be seen. In case of live connection the data will stay in the data source itself and it would not be moved to SAP Analytics Cloud. Due to this fact, I can’t see the data in SAP Analytics Cloud live model, but only the meta data.

- - Now save the model under a folder of your choice. Typically under public, you may create a folder with the name "Models' and save all the models here.

9. Create SAP Analytics Cloud story:

- - Now let's create the story. Go to Menu -> Create -> Story.

- - Choose "Add a Responsive Page"

- - Insert a new graph and then go to designer to add the required dimensions/measures.

- - I'm now good to go ahead and create any kind of visualizations to create a full story on top of the data that I have on my SAP HANA:

P.S. all the screenshots used above are the made by myself during the execution process of the end to end scenario

Conclusion:

So, it is this simple to consume the data from SAP HANA into SAP Analytics Cloud.

In case if there are any questions related to this blog post or in general about SAP Analytics Cloud, connectivity, please feel free to contact us through the community and we will try our best to get your questions answered asap.

Thanks for reading the blog post!

Consuming SAP HANA data into SAP Analytics Cloud through Live connection - End to End scenario

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win