Skip to Content

Considering the option of using parameter inputs for calculation views we can indeed have all of  the data processing logic in the HANA database to get faster results. What is more fascinating is how easy it is to create an OData service to consume the results in a web application.

In my previous post I created a Calculation View to return results based on two parameter inputs. In this blog I’m going to explain how to expose the same Calculation View using OData and consume it by using parameter inputs.

Let’s just say you want to expose a simple table (like the Marker table of the DistCalc project from my previous post) using an OData service. You will use the simple code below in a .xsodata file within a Node.js module of a Multi-Target Application (MTA).

service namespace "DistCalc.data" {
	"DistCalc.db::model.Marker" as "Marker" key ("markerId");
}

Once the module is running the Marker table can be consumed by using the regular OData V2 syntax. The metadata document of the OData service will look like below.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
    <edmx:DataServices m:DataServiceVersion="2.0" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
        <Schema Namespace="DistCalc.data" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
            <EntityType Name="MarkerType">
                <Key>
                    <PropertyRef Name="markerId"/>
                </Key>
                <Property Name="markerId" Type="Edm.Int32" Nullable="false"/>
                <Property Name="markerName" Type="Edm.String" MaxLength="10"/>
                <Property Name="latitude" Type="Edm.String" MaxLength="35"/>
                <Property Name="longitude" Type="Edm.String" MaxLength="35"/>
            </EntityType>
            <EntityContainer Name="v2" m:IsDefaultEntityContainer="true">
                <EntitySet Name="Marker" EntityType="DistCalc.data.MarkerType"/>
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Exposing a Calculation View will be similar to the .xsodata code above but for a Parameterized Calculation View where you need to provide mandatory inputs for it to work, the code is slightly different. So lets start building.

Note: I will be using the same MTA project I used in my previous post for this example and will be using SAP Web IDE.

 

Step 01 : Create a Node.js Module

  1. Create a Node.js module. In this case I will name it js. Make sure to tick Enable XSJS support.
  2. Create a sub-folder called odata within the lib folder.
  3. Create a new file called distanceToMarker.xsodata in the odata folder.
  4. Use the code below for the .xsodata file and save the file.

service namespace "DistCalc.data" {
	
	"DistCalc.db::model.Marker" as "Marker" key ("markerId");
	
	"DistCalc.db::MarkerDistance" as "MarkerDistance"
	with ("markerId", "markerName", "latitude", "longitude", "InputLatitude", "InputLongitude", "DistanceToMarker")
	key ("markerId")
	parameters via entity;
	
}

Run the Node.js module by right-clicking the js folder and selecting Run -> Run as -> Node.js Application. If the application started successfully you should see the Application is running log in the Run Console. When you click the URL on the top of the Run Console you should see Hello world open-up in a new browser tab. This confirms the Node.js app is running.

 

Step 02 : Access the OData Service

I used the Postman tool make a GET request to the OData service. The metadata document of the service is as below. There is a total of 3 entity sets and an association between two of them.

 

Explanation of the OData Service Code

  • The Entity Set Marker was created by the statement below.
    "DistCalc.db::model.Marker" as "Marker" key ("markerId");​
  • The Entity Sets MarkerDistance and MarkerDistanceParameters was created by the statement below.
    	"DistCalc.db::MarkerDistance" as "MarkerDistance"
    	with ("markerId", "markerName", "latitude", "longitude", "InputLatitude", "InputLongitude", "DistanceToMarker")
    	key ("markerId")
    	parameters via entity;​
  • The entity set MarkerDistanceParameters was created because we specified the parameters to be created by a separate entity (parameters via entity).
  • I specified the output parameters of the MarkerDistance entity using the with keyword because not all Calculation View Data Types are supported by OData. In this case the columns MarkerCoordinate and InputCoordinate were left out because they are of type ST_GEOMETRY and OData version 2.0 does not support it.
  • I have also specified the key property as markerId.

 

Calling the Service using Parameters

  • The MarkerDistance entity set cannot be accessed directly. It can only be accessed via the entity set MarkerDistanceParameters and the parameters are mandatory in this case because the Calculation View Input Parameters are specified as mandatory.
  • The request is made using the following URL with parameters.
    https://<host>:<port><service_uri>/MarkerDistanceParameters(in_latitude='-37.785395',in_longitude='145.270118')/Results?$orderby=DistanceToMarker​
  • The <host> and <port> will defer based on your system configuration. The <service_uri> is based on the name and the path of the .xsodata file. In this case it is /odata/distanceToMarker.xsodata. The lib folder of the Node.js module is the service root.
  • The parameter entity set MarkerDistanceParameters is accessed and the parameters in_latitude and in_longitude are given the coordinate values. The results are accessed by the Results navigation property. I’ve also ordered the results based on the DistanceToMarker property and only fetched the top 3 result.
    /MarkerDistanceParameters(in_latitude='-37.785395',in_longitude='145.270118')/Results?$orderby=DistanceToMarker&$top=3​
  • The results are as below.

 

This concludes this blog post.  Hope you learned something new.

Cheers!

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply