Skip to Content

Story

You want to expose HANA views as OData services. This document shows you how to do so using the development features within the HANA Studio. You have probably read Thomas Jungs excellent post on SAP HANA Extended Application Services (http://scn.sap.com/community/developer-center/hana/blog/2012/11/29/sap-hana-extended-application-services) – if not, you definitely should. What I want to show in this post is how to expose views (attribute, analytical or graphical views) with OData and focus on the development features in the SAP HANA Studio.

Preconditions

It is assumed that you have a SP5 Version of the HANA Studio and HANA Client installed. In the HANA Studio you must specify the location of the installed HANA Client:

Window->Preferences: SAP HANA Development -> Repository Access

When this is set, you’re ready to start using the development features in the HANA Studio. In order to be able to transport the packages we create, we have to set the content provider and create a delivery unit

Set the content provider:

Double click on your system in the Administration Console perspective.

Go to Configuration -> indexserver.ini -> repository -> content vendor

Enter any value you like. As this is a system setting it is customary to take e.g. the domain of your company (‘sap.com’ for example).

Create a Delivery Unit:

Click on Delivery Unit in the Modeler perspective. Make sure that you have selected the correct system.

Create a new delivery unit and give it any name you like. The name will be used for identifying the delivery unit (as a tuple with the already set content vendor ID).

We have created an empty delivery unit and may assign packages to it at a later stage.

Data Model

In the following chapters we will use data models and data which are available for download from:

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/c0c49112-c9ab-2f10-d591-9d40e658f7ce&overridelayout=true

If you want to use your own data model that’s just fine. The idea here is to show how to expose a view as an OData service. If you don’t have any view yet, you can follow the example based on the SFLIGHT data.

Details on how to import data can be found in the SAP HANA Developer Guide ( http://help.sap.com/hana_appliance ). The SFLIGHT data model isn’t very complicated and used in many SAP tutorials. If you’re new to SFLIGHT, that’s OK, too. All we’re doing is creating two views. For this you will probably use the Modeling perspective in the HANA Studio.

Create a package

In the navigator pane, when you expand your system you will find three folders: Catalog, Security and Content. Right click on Content and choose New->Package. Give it any name you like, e.g. teamdemo .

Attribute View: AT_AGENCY_DESC

Create an attribute view in the package based on the table STRAVELAG. Choose MANDT, AGENCYNUM, NAME, CITY, COUNTRY and LANGU as columns. If you don’t know how, you can find an XML version of the attribute view in the appendix.

All we’re doing in this attribute view is choosing a subset of the table.

/wp-content/uploads/2012/12/attribute_168430.png

Analytic View: AN_AGENCY_REV

Create an analytic view in the package with the table SBOOK as the data foundation. Mark FORCURKEY, AGENCYNUM, MANDT and FORCURAM as columns. Create a join with the previously created attribute view and join AGENCYNUM and MANDT. In the semantics, choose aggregation type SUM for the measure FORCURAM. If you’re not sure how to do this exactly, you can find the xml version of the analytic view in the appendix.

What we’re doing here in this analytic view is basically summing up all revenues for each travel agency in their respective currency.

Don’t forget to activate both views.

For details on how to model in HANA, please refer to the SAP HANA documentation.

/wp-content/uploads/2012/12/analytic_168470.png

SAP HANA Studio Development Features

Up to now we’ve used the Administration Console perspective for setting the content vendor ID and for creating a delivery unit and the Modeling perspective for creating the views. We have not yet used the SAP HANA Development perspective. The idea behind the different perspectives is (apart from it being a standard Eclipse concept) that of the segregation of duties. The HANA models (the attribute and analytic views we have created) require a know-how of the data model and underlying business problem. It cannot be assumed that the developer (which takes over now in this document) is the same person as the modeler. Much like the modeler will most likely not be the person that administers HANA.

Create a Repository Workspace

Choose the SAP HANA Development perspective. Click on the SAP HANA Repositories Tab. Right Click -> New Repository Workspace. A window will pop up with a list of systems/users that you have already assigned in your HANA Studio. If you can’t find it, choose Add System. Give the workspace a name (e.g. Demoworkspace) and a location where you want to save it (e.g. C:\Users\John\workspace ).

Remember this workspace location!

Expand your repository workspace and chose the package in which you have created the views (e.g. teamdemo). Right click and check out. This actually transfers the files on your local disk. You should now see the views you have created on your disk (using e.g. your Windows File Explorer) at e.g. C:\Users\John\workspace\Demoworkspace\teamdemo\ .

Create a Project

Now we have created a local copy of the repository workspace on disk. However, in eclipse you have to develop within a project. Therefore select the Project Explorer Tab. Right Click -> New Project.. Choose General->Project and enter a project name (e.g. DemoProject).

Now do not use the default location. De-select it and choose the workspace path where you have checked out the repository (e.g. C:\Users\John\workspace\Demoworkspace\teamdemo in the example above). Choose finish.

Now that we have created an eclipse project (in the underlying downloaded repository folder) we need to tell the HANA repository that we will use this eclipse project directory for our development, so that we’ll be able to commit to the HANA repository. This is achieved by sharing the project. Right click on the project (e.g. DemoProject) and choose Team->Share Project. Select the item SAP HANA Repository and click Next and then Finish.

Develop the OData Service

Now we’re ready to start developing. You can see little orange bubbles in the document icon next to the views. This means that the state of these files is committed and activated. You’ll see the difference between committed and activated in a second.

If we want to expose a view as an OData service, we need to create three files.

The .xsapp file:

Right click on the project and choose New->File. The corresponding project should be pre-selected. As a file name you must use .xsapp . Leave the file empty. This file is required to exist in order to tell the XS engine that there is an XS application in this package.

The .xsaccess file

Right click on the project and choose New->File. The corresponding project should be pre-selected. As a file name you must use .xsaccess . In order for the service that we will create to be accessible, you have to expose it. Therefore the file must contain the following:

{

    “exposed” : true

}

Details of other parameters for the .xsaccess file can be found in the developer guide.

The revenue.xsodata file

This is the core service definition. Right click on the project and choose New->File. The corresponding project should be pre-selected. As a file name you can choose any name followed by .xsodata (e.g. revenue.xsodata). Take the following service definition:

service {

       “teamdemo/AN_AGENCY_REV.analyticview” as “Revenue” keys generate local “GENERATED_ID” aggregates always (SUM of “FORCURAM”);

}

This service definition assumes that the view AN_AGENCY_REV is in the package teamdemo. It exposes the service as Revenue (you’ll see this in the URL later on). Further details of the syntax may be found in the respective developer guide.

Your project tree could look like this:

/wp-content/uploads/2012/12/tree_168429.png

You’ll notice the different symbols. The orange bubbles indicate that the version of the file is activated. The gray diamond indicates that the file was committed, but not activated. The dark rectangle with the star means that the file was not yet committed.

The difference is the following:

Not commited: The file is only on your local disk

Commited: The file is the same on your local disk and in the HANA repository

Activated: The files is commited and compiled into the runtime of the database

Now you need to commit and activate the files. Right click on the file and choose Team->Commit. Then right click again on the same file and chose Team->Activate. This has to be done for all three created files.

Now you should have a working XS OData service.

Consuming the XS OData Service

The created OData service can be accessed under the following URL:

http://yourhost:8001/teamdemo/revenue.xsodata (assuming that your package is called teamdemo).

The metadata can be access here:

http://yourhost:8001/teamdemo/revenue.xsodata/$metadata

The actual service could be consumed like this:

http://yourhost:8001/teamdemo/revenue.xsodata/Revenue?$format=json

This will yield the result of the analytic view as JSON. You may now use the OData features like $filter etc. For more on OData, please see http://www.odata.org.

                                  

  

Appendix

XML Definitions of the used views

Attribute View: AT_AGENCY_DESC

<?xml version=”1.0″ encoding=”UTF-8″?>

<View:RootView xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:Type=”http://www.sap.com/ndb/DataModelType.ecore” xmlns:View=”http://www.sap.com/ndb/ViewModelView.ecore“>

  <view schemaVersion=”1.2″ name=”AT_AGENCY_DESC” dataCategory=”DEFAULT” dimensionType=”STANDARD” hidden=”true” clientDependent=”true” defaultNode=”//@view/@viewNodes.0″>

    <endUserTexts label=”AT_AGENCY_DESC”/>

    <defaultSchema/>

    <executionHints engine=”JOIN” semantic=”TEMPLATE”/>

    <viewNodes xsi:type=”View:JoinNode” name=”Data Foundation” keyElements=”//@view/@viewNodes.0/@elements.0″>

      <elements name=”AGENCYNUM” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”8″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”MANDT” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”3″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”NAME” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”25″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”CITY” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”25″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”COUNTRY” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”3″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”LANGU” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”1″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <inputs>

        <entity href=”../../SFLIGHT/extdbtables/STRAVELAG.extdbtable#/”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”AGENCYNUM” sourceName=”AGENCYNUM”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”MANDT” sourceName=”MANDT”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”NAME” sourceName=”NAME”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”CITY” sourceName=”CITY”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”COUNTRY” sourceName=”COUNTRY”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”LANGU” sourceName=”LANGU”/>

        <layout xCoordinate=”187″ yCoordinate=”116″/>

      </inputs>

    </viewNodes>

  </view>

</View:RootView>

Analytic View: AN_AGENCY_REV

<?xml version=”1.0″ encoding=”UTF-8″?>

<View:RootView xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:Type=”http://www.sap.com/ndb/DataModelType.ecore” xmlns:View=”http://www.sap.com/ndb/ViewModelView.ecore“>

  <view schemaVersion=”1.5″ name=”AN_AGENCY_REV” dataCategory=”CUBE” hidden=”false” clientDependent=”true” defaultNode=”//@view/@viewNodes.1″>

    <endUserTexts label=”AB_AGENCY_REV”/>

    <defaultSchema schemaName=””/>

    <executionHints engine=”OLAP” semantic=”TEMPLATE”/>

    <viewNodes xsi:type=”View:JoinNode” name=”Data Foundation”>

      <elements name=”FORCURKEY” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”5″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”AGENCYNUM” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”8″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”MANDT” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”3″ primitiveType=”NVARCHAR” scale=”0″/>

        <searchProperties/>

      </elements>

      <elements name=”FORCURAM”>

        <endUserTexts label=” “/>

        <inlineType precision=”15″ primitiveType=”DECIMAL” scale=”2″/>

      </elements>

      <inputs>

        <entity href=”../../SFLIGHT/extdbtables/SBOOK.extdbtable#/”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”FORCURKEY” sourceName=”FORCURKEY”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”AGENCYNUM” sourceName=”AGENCYNUM”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”MANDT” sourceName=”MANDT”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”FORCURAM” sourceName=”FORCURAM”/>

        <layout xCoordinate=”217″ yCoordinate=”4″/>

      </inputs>

    </viewNodes>

    <viewNodes xsi:type=”View:JoinNode” name=”Logical Join”>

      <elements name=”FORCURKEY” aggregationBehavior=”NONE” displayAttribute=”false” hidden=”false”>

        <endUserTexts label=” “/>

        <inlineType precision=”5″ primitiveType=”NVARCHAR” scale=”0″/>

      </elements>

      <elements name=”FORCURAM” aggregationBehavior=”SUM”>

        <endUserTexts label=” “/>

        <inlineType precision=”15″ primitiveType=”DECIMAL” scale=”2″/>

      </elements>

      <inputs viewNode=”//@view/@viewNodes.0″>

        <mappings xsi:type=”Type:ElementMapping” targetName=”FORCURKEY” sourceName=”FORCURKEY”/>

        <mappings xsi:type=”Type:ElementMapping” targetName=”FORCURAM” sourceName=”FORCURAM”/>

        <layout xCoordinate=”300″ yCoordinate=”200″/>

      </inputs>

      <inputs selectAll=”true”>

        <entity href=”../../sflight/attributeviews/AT_AGENCY_DESCRIBED.attributeview#//@view”/>

        <layout xCoordinate=”24″ yCoordinate=”210″/>

      </inputs>

      <joins cardinality=”CN_1″ dimensionJoin=”true” joinType=”referential” leftInput=”//@view/@viewNodes.1/@inputs.0″ rightInput=”//@view/@viewNodes.1/@inputs.1″>

        <leftElementNames>AGENCYNUM</leftElementNames>

        <leftElementNames>MANDT</leftElementNames>

        <rightElementNames>AGENCYNUM</rightElementNames>

        <rightElementNames>MANDT</rightElementNames>

      </joins>

    </viewNodes>

  </view>

</View:RootView>

To report this post you need to login first.

16 Comments

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

  1. RAMESH PARAMESWARAN

    Hi Jens,

    When I try to run an Odata service built for an attribute view, I get the below error wehn I run the odata service

    <error xmlns=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata“>

    <code/>

    <message xml:lang=”en-US”>Service exception: distributed metadata error.</message>

    </error>

    When I try to display the metadata, the odata returns the definition, but when I use the Entity Name to retrieve the data, I get the above error.

    Any thoughts?

    Thanks,

    Ram

    (0) 
  2. Dhinesh Kumar J

    Hi,

       Thank you very much for this document

        

       I have two input parameters in my view i have created XSODATA file and services as you described. I dont know how to pass those parameters to the odata service. i dont know the syntax for adding parameters to the Xodata file. Kindly help me out.

    Thanks and Regards,

    Dhinesh Kumar.J

    (0) 
  3. Mohamed Ali JAMAOUI

    hi, thanks for your post.

    I am trying to expose an analytic view, so I did the following 

    service {

              “packageName/EFASHION_STAR_SCHEMA.analyticview” as “analyticView”

              keys generate local “ID”

              aggregates always (SUM of “MARGIN”);

    }

    activation is correct, but when try to access the odata service I get an error saying that feature not supported!

    PS. In my analytic view I have more than one measure, should I specify the aggregation function for all of them, if yes how to do so?

    edit: problem solved, I was actually accessing the data in the wrong way!

    here I should access the data,

    /analyticView.xsodata/analyticView?$format=json&$select=CITY,MONTH_NAME,MARGIN

    so I specify in the select parameter what exactly I want to see and voila!

    thanks & regards

    Mohamed Ali

    (0) 
  4. Maksim Jegorovcev

    Hi Jens,

    Thank you for this blog. However, I have couple of questions:

    1) Can we expose multiple tables/views via 1 service? There is nothing like a multiple result set i.e. returns multiple tables for a single query?

    2) Can we for example call a procedure in OData Service that returns for a single query multiple tables back?

    (0) 
    1. Karl Sanford

      I am far from an expert, but seeing as no one else has attempted to answer this question, I will give it a try.

      My understanding is that OData is really about exposing a data model and allowing applications to navigate through the model.

      I’m not sure if this will help, but I suggest reading up on OData associations (HANA developer guide page 386) and navigation (HANA developer guide page 389). Adding navigation properties (require associations to work) allows nice client side binding for single page applications.

      If you truly want all the data in one call you could always create a view in hana studio and extend that view as an OData service.

      Also, RDL (River Development Language) is helpful for creating multidimensional objects if you have River installed on your server.

      (0) 
    2. Thomas Jung

      Yes you can call multiple tables/views as separate entities within one XSODATA service. If you want to return multiple entities within one request you must build associations between them.  Normally child entities are only loaded upon demand, but you can use the $expand URL parameter and explode child details in place within one request/response.

      No you can not call a procedure from within an OData Service.  Not directly at least.  You can wrap a procedure within a Scripted Calculation view and use that as the source of your entity. Of course once you have a Scripted Calculation view you can only have one result set.

      (0) 
  5. Leela V

    Hi Jens,

       How to access the Hana Xs Odata url to third party tool ,since we encounter the authorization page as main page for the odata service ( where it allow only after authorization to view the meta data).Can we set the default username with password for the odata url.

    (0) 
  6. Tanveer Shaikh

    Hi Jens,

    I successfully created xsodata service and able to test the service in browser with various parameters. And all works fine.

    Need of my application is to GET data using the generated local id for example –

    My result set is –

    {"__metadata": {"type":"odata.CV_ProjectDetailType"
    
        ,"uri":"/odata/odata.xsodata/CV_ProjectDetail('543859892167869371')"}
    
        ,"C_NAME":"SAP","CR_NUMBER":7},
    }

    And I want to access the same result set using –

    GET /odata/odata.xsodata/CV_ProjectDetail(‘543859892167869371’)

    Is there there a way to achieve this ? I got “RESOURCE NOT FOUND” error when I use generated IDs

    Thanks,

    Tanveer

     

    (0) 

Leave a Reply