In a tutorial video the SAP HANA Academy’s Tom Flanagan walk throughs how to connect a SAP HANA Cloud Platform schema to SAP Lumira. In the first of a two part video series, Tom shows how to create a SAP HANA view that can be consumed by the SAP HANA Cloud Platform. This view will join two tables that are already located in the database.
As prerequisite for this series you must have a free SAP HANA Cloud Developer license and have installed and setup Eclipse. Watch these linked video from the SAP HANA Academy to learn how to get your license and set up Eclipse. Also, browse this SAP HANA Academy series for much greater detail on modeling in SAP HANA Studio.
Check out the tutorial video below.
(0:51 – 1:50) Preparing the View
Start Eclipse to access SAP HANA Studio and select the SAP HANA Development perspective. Expand the Content folder followed by the folder for your free SAP HANA Cloud Platform account (p…). Right click on your SAP HANA instance (found on the SAP HANA Cloud Platform cockpit) and create and name a new Package.
Expand your instance Package, left click on the recently created Package (in this example Lumira) and choose to create a new Analytic View. Name your Analytic View in the wizard and click finish.
(1:50 – 3:10) Joining and Configuring the Tables
Once the Analytic View has been created and the workbench has appeared navigate and expand the Catalog folder. Expand your HCP schema that begins with NEO, followed by the Table folder. Next drag and drop the CUSTOMER and ORDERS tables into the Data Foundation box in the Scenario.
Right click in between the CUSTOMER and ORDERS tables in the Details box and select Create Join. Select CUSTOMER_ID in both the CUSTOMER and ORDERS table. Leave the Join Type as Referential and make the Cardinality 1..n before clicking ok.
Once the pair of Customer_IDs have been joined click the following objects in the two tables. In the CUSTOMERS table select Customer_Name and in the ORDERS table select Order_ID, Order_Amount, and Order_Date.
(3:10 – 4:05) Enriching the Data
In the Scenario click on the Semantics box to enrich the data by selecting whether it will be typed as a Measure or Attribute. For Customer_Name and Order_Date set their type as Attribute and set Order_ID and Order_Amount as Measure. For Order_ID set the aggregation to COUNT and the Order_Amount to SUM.
Also, change the Default Client to Cross Client. Click to check the box next to Allow Relational Optimization and choose to leave Apply Privileges blank from the drop down menu.
(4:05 – 6:20) Executing Grant SELECT Privileges Stored Procedure
These analytic privileges have been disabled so we can execute a stored privilege necessary for this scenario.
Click on the SQL button to open a SQL console window. Now we will run a HCP stored procedure so that the DEV User can have SELECT privileges for the view. For further details on this and other HCP stored procedures please visit this link from help.hana.ondemand.com.
Due to the user isolation requirements of the SAP HANA Cloud Platform as well as for security reasons the default SAP HANA database user has restricted privileges and can not use the open data preview context. In order to see and select from the view that we will be activating, the user needs to be granted additional SELECT privileges.
You can do this by activating the HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS stored procedure. Copy the syntax from the page (also listed below) and paste it into the SQL console window.
call “HCP”.“ HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS”
Click the green execute button to run the stored procedure. Once the procedure has run the view has been created with all of the SELECT privileges needed to connect to SAP Lumira.
To further confirm this open the SYS_BIC folder that’s in the NEO folders and expand the Column Views folder. There you will see a new Analytic View for your HCP user account.
View more tutorial videos about SAP HANA Cloud Platform at the SAP HANA Academy.
SAP HANA Academy - over 500 free tutorial technical videos on using SAP HANA.
-Tom Flanagan
SAP HANA Academy
- SAP Managed Tags:
