Skip to Content

Expose your recommendation results using SAP HANA XS OData

Previous

Expose your recommendation results using SAP HANA XS OData

By Abdel Dadouche

Understand and implement some of the options available with SAP HANA to expose your results

You will learn

  • How to setup your SAP HANA XS OData service to be used in your SAPUI5 application

Details

Step 1: Switch to the Catalog perspective

In order to create the CDS and XS OData artifacts, we will be using the Catalog perspective available in the SAP HANA Web-based Development Workbench.

From the SAP HANA Web-based Development Workbench main panel, click on Catalog:

SAP HANA Web-based Development Workbench

Else, if you are already accessing one of the perspective, then use the plus icon from the menu:

SAP HANA Web-based Development Workbench

Note

Make sure the currently connected user is TRIAL and not SYSTEM. Check the upper right corner of the SAP HANA Web-based Development Workbench.

 

Please log in to access this content.
Step 2: Create additional views

Inspired by the series of SQL used to validate our SAP HANA APL & PAL results, you will create a set of views that will help provide a better user experience.

For example, you can create a view that present a user rating summary which include the number of rating, the average notation using the following SQL:

DROP   VIEW "MOVIELENS"."SUMMARY_RATING_USER";
CREATE VIEW "MOVIELENS"."SUMMARY_RATING_USER" AS
SELECT DISTINCT
    "USERID"
  , 'User Id: ' || "USERID" || ' - Rating count: ' ||   COUNT(1) over( PARTITION BY "USERID" )  AS DESCRIPTION
  , COUNT(1)               over( PARTITION BY "USERID" ) AS "RATING_COUNT"
  , AVG("RATING")          over( PARTITION BY "USERID" ) AS "RATING_AVG"
  , NTH_VALUE("TIMESTAMP",1) over( PARTITION BY "USERID"  ORDER BY "TIMESTAMP" DESC, "MOVIEID") AS "LAST_RATING_DATE"
  , NTH_VALUE("RATING"   ,1) over( PARTITION BY "USERID"  ORDER BY "TIMESTAMP" DESC, "MOVIEID") AS "LAST_RATING"
  , NTH_VALUE("MOVIEID"  ,1) over( PARTITION BY "USERID"  ORDER BY "TIMESTAMP" DESC, "MOVIEID") AS "LAST_MOVIEID"
FROM "MOVIELENS"."public.aa.movielens.cds::data.RATINGS";

Now a movie rating summary:

DROP   VIEW "MOVIELENS"."SUMMARY_RATING_MOVIE";
CREATE VIEW "MOVIELENS"."SUMMARY_RATING_MOVIE" AS
SELECT DISTINCT
    "T1"."MOVIEID"
  , 'Movie Id: ' || "T1"."MOVIEID" || ' - Rating count: ' ||  COUNT(1) over( PARTITION BY "T1"."MOVIEID" ) AS DESCRIPTION
  , "T2". "TITLE"
  , "T2". "GENRES"
  , "T3". "IMDBID"
  , "T3". "TMDBID"       
  , COUNT(1)               over( PARTITION BY "T1"."MOVIEID" ) AS "RATING_COUNT"
  , AVG("RATING")          over( PARTITION BY "T1"."MOVIEID" ) AS "RATING_AVG"
  , NTH_VALUE("TIMESTAMP",1) over( PARTITION BY "T1"."MOVIEID"  ORDER BY "T1"."TIMESTAMP" DESC, "T1"."MOVIEID") AS "LAST_RATING_DATE"
  , NTH_VALUE("RATING"   ,1) over( PARTITION BY "T1"."MOVIEID"  ORDER BY "T1"."TIMESTAMP" DESC, "T1"."MOVIEID") AS "LAST_RATING"
  , NTH_VALUE("USERID"   ,1) over( PARTITION BY "T1"."MOVIEID"  ORDER BY "T1"."TIMESTAMP" DESC, "T1"."MOVIEID") AS "LAST_USERID"
FROM "MOVIELENS"."public.aa.movielens.cds::data.RATINGS" "T1"
LEFT OUTER JOIN "MOVIELENS"."public.aa.movielens.cds::data.MOVIES" "T2" on ("T1".MOVIEID = "T2".MOVIEID)
LEFT OUTER JOIN "MOVIELENS"."public.aa.movielens.cds::data.LINKS"  "T3" on ("T1".MOVIEID = "T3".MOVIEID);

And finally, the rating details:

DROP   VIEW "MOVIELENS"."SUMMARY_RATING_DETAILS";
CREATE VIEW "MOVIELENS"."SUMMARY_RATING_DETAILS" AS
SELECT
    "T1"."MOVIEID"
  , "T1"."USERID"
  , 'User Id: ' || "T1"."USERID" || ' - User Rating: ' || "T1"."RATING" || ' - Movie Id: ' || "T1"."MOVIEID" || ' - Title: ' || "T2"."TITLE" AS DESCRIPTION
  , "T2"."TITLE"
  , "T2"."GENRES"
  , "T3"."IMDBID"
  , "T3"."TMDBID"       
  , "T1"."RATING"  
  , "T1"."TIMESTAMP"
FROM "MOVIELENS"."public.aa.movielens.cds::data.RATINGS" "T1"
LEFT OUTER JOIN "MOVIELENS"."public.aa.movielens.cds::data.MOVIES" "T2" on ("T1".MOVIEID = "T2".MOVIEID)
LEFT OUTER JOIN "MOVIELENS"."public.aa.movielens.cds::data.LINKS"  "T3" on ("T1".MOVIEID = "T3".MOVIEID);

Note

You may receive a series of errors and warnings in the console log while running the above code. They should all be related to the drop statements at the beginning which are intended to help you re-run the script if needed.

 

Please log in to access this content.
Step 3: Switch to the Editor perspective

In order to create the CDS and XS OData artifacts, we will be using the Editor perspective available in the SAP HANA Web-based Development Workbench.

From the SAP HANA Web-based Development Workbench main panel, click on Editor:

SAP HANA Web-based Development Workbench

Else, if you are already accessing one of the perspective, then use the plus icon from the menu:

SAP HANA Web-based Development Workbench

Note

Make sure the currently connected user is TRIAL and not SYSTEM. Check the upper right corner of the SAP HANA Web-based Development Workbench.

 

Please log in to access this content.
Step 4: Create SAP HANA XS OData services

SAP HANA XS OData services

OData is a resource-based web protocol for querying and updating data. OData defines operations on resources using HTTP commands (for example, GET, PUT, POST, and DELETE) and specifies the uniform resource indicator (URI) syntax to use to identify the resources.

The main aim of OData is to define an abstract data model and a protocol which, combined, enable any client to access data exposed by any data source. Clients might include Web browsers, mobile devices, business-intelligence tools, and custom applications (for example, written in programming languages such as PHP or Java); data sources can include databases, content-management systems, the Cloud, or custom applications (for example, written in Java).

SAP HANA version 1.0 SPS 12 currently supports OData version 2.0.

To create an OData service, you create a flat file that contains the set of OData service. You save this file with the suffix .xsodata in the appropriate package for your application in the SAP HANA repository.

 

Create a new file named data.xsodata in the public/aa/movielens/cds package.

Paste the following content in the console.

service {

  // expose the model result views
  "MOVIELENS"."APL_MODEL_USERS_RESULTS" as "APL_MODEL_USERS_RESULTS" key ("USERID" , "RANK");
  "MOVIELENS"."PAL_MODEL_USERS_RESULTS" as "PAL_MODEL_USERS_RESULTS" key ("USERID" , "RANK");
  "MOVIELENS"."APL_MODEL_ITEMS_RESULTS" as "APL_MODEL_ITEMS_RESULTS" key ("MOVIEID", "RANK");
  "MOVIELENS"."PAL_MODEL_ITEMS_RESULTS" as "PAL_MODEL_ITEMS_RESULTS" key ("MOVIEID", "RANK");

  // expose the summary user and movie views
  "MOVIELENS"."SUMMARY_RATING_USER"       as "SUMMARY_RATING_USER"     key ("USERID");
  "MOVIELENS"."SUMMARY_RATING_MOVIE"      as "SUMMARY_RATING_MOVIE"    key ("MOVIEID");
  "MOVIELENS"."SUMMARY_RATING_DETAILS"    as "SUMMARY_RATING_DETAILS"  key ("USERID", "MOVIEID");
}

Here we have added a set of services that we will be using in our SAPUI5 application to read the algorithm result but any other declared services.

Save the file using the save icon from the menu or press CTRL+S.

Check the message console, and make sure there is no errors. The following messages should be displayed:

[xx:xx:xx] File /public/aa/movielens/cds/data.xsodata created successfully.
[xx:xx:xx] File /public/aa/movielens/cds/data.xsodata saved & activated successfully.

Now, let’s run the XS OData service using the execute icon run from the menu.

Append the following text to your XS OData URL:

/APL_MODEL_USERS_RESULTS(USERID=1,RANK=1)/TITLE?$format=json

You should get the first recommendation from the APL algorithm results for user id 1.

Provide an answer to the question below then click on Validate.

What is the first recommendation for user id 123 from the APL algorithm results? You can paste the full title excluding the double quotes, like : Pulp Fiction (1994)
×

Updated 09/27/2017

Time to Complete

5 Min

Beginner
Next
Back to top