SAP HANA & Einstein Analytics

Posted on by varmehta

Fellow Trailblazers,

In this blog, I am focusing on one of the key area “Data Acquisition” on Einstein Analytics via external connectors. For the demo purpose, I have decided to use one of the newest release ‘SAP HANA’ connector in conjunction with Einstein Analytics. Also, my focus is ‘Point-and-Click’ actions NOT writing a single line of CODE how you can perform these actions precisely and efficiently. Feel free to try this out as we have free SAP Cloud Platform trial instances are available.

A Big Game Changer:

Today’s fast-paced digital economy demands that companies adapt to remain competitive. Success requires agility, flexibility and the ability to connect end-to-end processes across the organization and beyond. Having a unified, business cloud platform is critical to delivering state-of-the-art solutions in a constantly changing market. If we look at the market cap for SAP-based ERP products. Then this feature is another big step to bring customer master data closer towards the end user.

Real EA Implementation Scenario:

Normally in a real implementation scenario, you will be given the SAP end URL and database credentials from the SAP Development stream to establish your EA connection. Those who want to understand both sides of the integration, or like to play around to see the feasibility check with your demo EA environment. Feel free to explore this article.

Key Considerations & Lessons Learned:

  • Focus to bring as much clean transformed data into EA using these connectors. Do not throw heavy lifting tasks on data flows and recipes only to transform your data. Purpose of these tools is to perform small data string operations to help you prepare your data on smaller data volume. NOT when you are dealing with millions of rows. Therefore, do not position this utility to construct the core data warehouse business logic or to replace any ETL products. Early alignment & engagement with data Integration team within your organization is very essential to have the right ETL strategy in place for your EA rollouts.
  • Keep your business logic as much close to the data source. By doing that you have full control over your dataflows and can debug faster in case of any conflicts. In addition, you have enough bandwidth to optimize further in your dataflow and SAQL for your final datasets and dashboards.
  • Keep that in mind resulting dataset cannot effectively mutate itself to become optimized it has to be driven by the data supplied and it takes time to settle in order to see your SAQL queries runs faster on the platform. So have your right data strategy in place is one of the key factors for successful EA rollouts.

Last but not least,  I would highly recommend following the EA camp series. Check a few sessions from Tim Bezold & Terence Wilson (ACE Team-Salesforce).

Einstein Analytics Data Connectors:

Based upon the data ‘Pull’ mechanism Einstein Analytics has several out of the box data connectors on the platform. Not only these can be scheduled individually but further down you can also prepare or transformed in your pre-post dataflow operations to simplify it for your end users on the dashboards. Below is one of the connector I will be using for my demo (Snapshot Below).

EA SAP HANA Connector

Use case Overview:

Below are the key components I am using for my use case –

  • SAP Cloud Platform Trial Account: Link
  • Salesforce Development Environment: Analytics Enabled: Link

Implementation Steps: 

SAP Cloud Platform: Phase 1

  • Step 1: SAP Cloud Platform Sign up
  • Step 2: SAP HANA Tenant DB Creation
  • Step 3: Access Enablement SAP HANA Web-based Development Workbench
  • Step 4: Creation of a new HANA Table (HANA Schema & Table Creation) 
  • Step 5: Creation of a New application & Permissions
  • Step 6: Creating OData Service via Integrated SAP HANA Web-based Development Workbench.

Salesforce Einstein Analytics: Phase 2 

  • Step 7: EA Data Connection Establishment in Einstein Analytics
  • Step 8: Pulling the HANA table data (Products & Suppliers) from SAP Cloud
  • Step 9: Post-transformation & Salesforce Data Augmentation in Einstein Analytics
  • Step 10: Final SAP HANA based Dataset creation in Einstein Analytics

Approach Description:

  • Step 1 – SAP Cloud Platform (SCP) Sign up: You need to sign up for the free trial 30 days account to test this use case.  Link


Snapshot: SCP Home Page

After the sign-up. Please select ‘Neo Trial’ application. We will be creating our HANA Database in this application. (Snapshot Below)


Snapshot: SCP Neo Trial

  • Step 2 -SAP HANA Tenant DB Creation: In this step please navigate to SAP HANA / SAP ASE -> Databases & Schemas -> Then Press NEW (Snapshot Below)

Snapshot : SCP HANA DB Page

In the next step, we have to populate the database information credentials as follows –

  • Database ID: eahana
  • Database System: HANA MDC (<Trial>)
  • System USER Password: **********
  • Confirm Password: **********
  • Web Access: On
  • DP Server: On

(Note: Make sure to keep your all credentials written somewhere as we need them in the last step for our EA connection establishment).

You can also monitor these events as it might take a few minutes to create your DB in your trial org. – (Snapshot Below)

Snapshot : HANA DB Events

  • Step 3: Access Enablement SAP HANA Web-based Development Workbench: In this step, we will try to access the HANA workbench. Before doing that we have to do a bit housekeeping (Assign some rights to our user to access Web Catalog/Editor).

Click on your Account Number (Snapshot Below)

Click on your “DBSchema ID” (Snapshot Below)

When you click on “DB Schema ID” then a page is appeared as below: There are three links available. They are as follows: (Snapshot Below)

  • Administration Tools: SAP HANA Cockpit
  • Development Tools: SAP HANA Web-based Development Workbench
  • Education Tools: SAP HANA Interactive Education (SHIN

Click on Administration Tools- “SAP HANA Cockpit” (Snapshot Below)

Then an alert message is shown. Click on ok–>Click on Continue (Snapshot Below)

Then a webpage is opened as below, which consists of SAP HANA Database Administration. (Snapshot Below)     


Go to your SAP Cloud Platform Cockpit. Then Click on Development Tools: SAP HANA Web-based development workbench. (Snapshot Below)

Provide your Username as “SYSTEM” and Password(Which is provided while creating your database) to Login to the Editor in Workbench. (Snapshot Below)

SAP HANA Web-based Development Workbench is opened. (Snapshot Below)

Now Click on “Editor” or “Catalog”. (Snapshot Below)

After creating a database instance on the SAP Cloud platform, while you are trying to access the “SAP HANA Web-based Development Workbench” link, you might be getting “403-Forbidden” error message. (Snapshot Below) –

This is happening due to no rights assigned to your user role to access the workbench. While facing an error like the above Click on “Security” in the workbench page and follow the steps below. (Snapshot Below)

The following screen will appear and you would need to select “Users” from the left side panel. (Snapshot Below)

Under the ‘Users’ tab -> Click on SYSTEM -> then on the right canvas role setting screen would appear. -> you would need to click on “+” sign (green colored) as shown below.

Then a screen appears like below.

Now you need to insert the phrase “sap.hana.ide.roles” to search and here we have to select “Editor”.Then, press the “OK” button to proceed further. (Snapshot Below)

Same steps you have to follow again for ‘Catalog’ role assignment (Snapshot Below)


As you could see, the new roles are assigned, but yet to be saved, that’s why “SYSTEM” tab is highlighted in yellow color indicating unsaved changes made. So, press the “Save” button or (Ctrl+s). (Snapshot Below)

Now in order to check whether the permissions are assigned correctly to your role. Go on Granted Roles tab -> write ‘editor’ or ‘catalog’ in the input box (Snapshot Below)

That’s all we would need to do while facing an error like “403 forbidden”, now you would be able to access “Editor” and “Catalog” tabs as you have granted access to your role.

  • Step 4: Creation of a new HANA Table (Schema & Table Creation)-Go to “SAP HANA WEB BASED DEVELOPMENT WORKBENCH.” and select Catalog. On left side Select Catalog and right click on Catalog, you will find a Schema. Then Click on that and Enter your new Schema name. In my case, I am using EA-SAP HANA (snapshot Below).

Snapshot: HANA Schema

Click on your newly created Schema name and select “TABLES”. Then Right click on “Tables” and select “CONSOLE EDITOR”.

On the “Console Editor” you can write the DB SQL “Statements for table creation, insertion etc. Or copy & paste the same code from below

-- DROP EA TABLE "Products";

  CREATE COLUMN TABLE "Products" (
  "ProductID" VARCHAR(20) NOT NULL ,
  "Name" NVARCHAR(256),
  "Description" VARCHAR(256),
  "Price" DECIMAL(10,4),
  "CurrencyCode" VARCHAR(3),
  "PictureURL" VARCHAR(256),
  "SupplierID" VARCHAR(20),
  PRIMARY KEY ("ProductID")
);

-- DROP EA TABLE "Suppliers";

  CREATE COLUMN TABLE "Suppliers" (
  "SupplierID" VARCHAR(20) NOT NULL ,
  "SupplierName" NVARCHAR(256),
  "Addresss" VARCHAR(256),
  "EmailAddress" VARCHAR(256),
  PRIMARY KEY ("SupplierID")
);

Now click on the execute button and check the message in the console. Two new tables, Suppliers and Products, have been created. (Snapshot Below)

From the Catalog Explorer on the left side expand the name of your schema: you will find the new table under the Tables branch of your SCHEMA

Insertion of Table Records –  

-- TRUNCATE TABLE "Products";
INSERT INTO "Products" VALUES('P001','Apple','Apple Gala',50.67,'USD','/images/P001','S001');
INSERT INTO "Products" VALUES('P002','Pineapple','Gold Pineapples',20.31,'USD','/images/P002','S002');
INSERT INTO "Products" VALUES('P003','Peach','Super Sweet Peaches',30.12,'EUR','/images/P003','S001');
INSERT INTO "Products" VALUES('P004','Banana','Bananas',80.48,'CHF','/images/P004','S002');
INSERT INTO "Products" VALUES('P005','Milk','Milky',60.96,'EUR','/images/P005','S003');

-- TRUNCATE TABLE "Suppliers";
INSERT INTO "Suppliers" VALUES('S001','Acme Export','New York','[email protected]');
INSERT INTO "Suppliers" VALUES('S002','Nature Food','Boston','[email protected]');
INSERT INTO "Suppliers" VALUES('S003','Northern Cathering','Denver','[email protected]');

If you right click on the name of one of the tables in the Catalog Explorer you can choose Open Content to display its records

  • Step 5: Creation of a New application & Permissions –

Go back to the SAP HANA Web-based Development Workbench tab and press Editor. Then right click on the ‘Content’ and choose -> New package (i.e “Dev”)

In order to create a new sub-package of “dev”. Enter the name of the sub-package (i.e. “myproducts”) and a description and click on Create


In the last step, we will create an empty application. Right Click on the child package and Press Create Application. (Snapshot Below)

Definition the application permissions

Once the new application is created you should find 3 new files: .xsaccess, .xsapp, index.html

In the next step Right click on the “myproducts” application and select New –> File (Snapshot Below)

Create a new file named .xsprivileges, enter the following content and save the file. You shouldn’t get any error message in the console

{ "privileges": 
[
  {"name": "Execute", "description": "Execute"}
]
}

Click on the .xsaccess file, it will be opened in the editor. And make sure in line number 6 should be your package path. And change the “prevent_xsrf” parameter from “true” to “false” at line 13. Then save the file. Again, no errors should appear in the console window.  

{

"exposed": true,
    "authentication": [{
        "method": "Form"
    }],
    "authorization": ["Dev.myproducts::Execute"],
    "mime_mapping": [{
        "extension": "jpg",
        "mimetype": "image/jpeg"
    }],
    "force_ssl": false,
    "enable_etags": true,
    "prevent_xsrf": false,
    "anonymous_connection": null,
    "cors": [{
        "enabled": false
    }],
    "cache_control": "no-cache, no-store",
    "default_file": "index.html"
}

In the next step, we will assign our newly created application privilege to our global role. Now go back to Development Workbench and press security. (Snapshot Below)

Now navigate to the Users panel and select ‘SYSTEM’. Then go to the application privilege tab and assign your newly created application package to your user. (Snapshot Below)

  • Step 6: Creating OData Service via Integrated SAP HANA Web-based Development Workbench – Open Data Protocol (OData) is an open protocol which allows the creation and consumption of queryable and interoperable RESTful APIs in a simple and standard way. It provides an entire query language directly in the URL to consume. 
  1. Go back to the SAP HANA Web-based Development Workbench
  2. Right click on the myproducts application and select New –> File
  3. Create a new file named services.xsodata and paste the following content there. Then save the file. 
service {

   "EA-SAPHANA"."Products" as "Products"
        create forbidden
        update forbidden
        delete forbidden;
    "EA-SAPHANA"."Suppliers" as "Suppliers"
        navigates ("SupplierProducts" as "Products")
        create forbidden
        update forbidden
        delete forbidden;
    association "SupplierProducts"
        principal "Suppliers"("SupplierID") multiplicity "1"
        dependent "Products"("SupplierID") multiplicity "*";
}

When pressing the Execute button on the toolbar, you should get the working service. Voila 🙂 This is the URL you can use for addressing your new OData service for your EA integration.

Optionally, you can also fill in the index.html file with the following content, paying attention to replace the hostname with the one in your URL 

<!DOCTYPE HTML>
<html>
    <head></head>
    <body>
        <h1>The service is working fine</h1>
        <h2>Click here for the metadata file</h2>
        <a href="https://eahanap2001117483trial.hanatrial.ondemand.com/Dev/myproducts/services.xsodata/$metadata">Metadata file</a>
    </body>
</html>

When clicking on the execute button you get the following page:

Click on the “Metadata file” link. You can access the metadata file for this service. (Snapshot Below)

Summary : Phase 1

Let’s Summarise what we have done so far – We have successfully finished all the relevant steps (1 to 7) on the SAP Cloud side. We have our HANA DB created and service to access the DB Table content. Now we will move on to the last section on SFDC EA side to finish the integration. Those who are able to follow the steps – Good Job!  ‘You all deserve a pat on the back’ 🙂

Lets move on to the Second EA Phase

Step 7: EA Data Connection Establishment in Einstein Analytics  – In this step, we will create an SAP HANA connection from Einstein Analytics. In order to fetch our product & supplier table data and then store into DATA Connector cache in Einstein Analytics.

Now go to your Salesforce Environment and then Analytics Studio. Then you need to press ‘Data Manager’ at the very right corner (snapshot below).

Press ‘Setup’ from the left panel and then press ‘Connect to Data’ at the very right corner.

Select the connection type ‘SAP HANA for your new remote connection.

Then insert your SAP HANA database credentials and give some unique name to this connection and developer name with some description and press save. Make sure to put the right syntax in the credentials. (Snapshot Below)

  • Connection Name: Any meaningful Name
  • Developer Name:  Any Name
  • Description: Any meaningful Name
  • Password: Your DB Password
  • Username: SYSTEM
  • URL: Your OData service URL: https://eahanap2001117483trial.hanatrial.ondemand.com/Dev/myproducts/services.xsodata

Press SAVE & Test – You will see the ‘Test Connection’ successful pop-up. (Snapshot Below)

Now Press Continue and you will see your SAP HANA Connection set up. (Snapshot Below)

Step 8: Pulling the HANA Data (Products & Suppliers) from SAP Cloud: Now go back and open your SAP HANA Connection. Now you will see your HANA DB tables in Einstein Analytics. In my case Suppliers & Products table (Snapshot Below).

Now Select each table object and bring your data into Einstein Analytics. (Snapshot Below) –

Products : 5 Records

Supplier : 3 Records

Then in the LAST step, we will Run our data sync and monitor the dataflow (Snapshot Below)

Now 5 Product & 3 Supplier record rows from SAP HANA will be stored in the Einstein Analytics data connection cache. You can also monitor those in your data sync job run and check the Remote Sync NODE (Snapshot) below.

Now data is successfully Synced and both of the tables have flagged ‘Green’

Step 9 : Post transformation & Salesforce Data Augmentation in Einstein Analytics – 

Now we have our data in the EA environment. Now we can further consume this data with our CRM data and augment or can perform further data transformations. Opportunities are endless opportunities to consume this data further. In order to make it simple, I will only create a simple Data flow and register my one Dataset in Einstein Analytics named as hana_masterdata_products.

Step 10: Final SAP HANA based Dataset creation in Einstein Analytics –

Below I am creating a simple dataflow and augmenting my products with my supplier (key: Supplier Id) (Snapshot Below) –

Now you can see your SAP HANA based dataset registered in EA successfully : (Snapshot Below)

Snapshot : EA Lense (Table Widget)

Summary & Conclusion:

  • In the above scenario, I have focused on unidirectional flow to ingest the data into EA from SAP HANA connector. But this could be easily transformed into bi-directional flow based upon your use case.
  • I have used the fairly straightforward sample use case in order to showcase the dataset creation via this connector.  You can extend this integration with further SAP products and bring your SAP Cloud data closer towards your end user & CRM operational data. Without storing in Salesforce (Sales & Service Cloud).

For the new audience on this topic, I hope it gave you a fair idea about the SAP HANA data utilization in regards to Einstein Analytics.  Let me know your feedback or any missing pieces you found in this scenario.

Cheers!

Varun

Leave a Comment

Your email address will not be published. Required fields are marked *