Skip to Content

The aim of this blog is to provide details on how to expose SAP HANA modeled views through SAP HANA Extended Application Services (SAP HANA XS). The information shared within the blog does require some level of familiarity with SAP HANA – specifically around modeling but also assumes that the reader has reviewed Thomas Jung’s introductory blog on SAP HANA XS.  Please note the important information regarding the initial controller release of SAP HANA XS; details can be found toward the end of his blog.

One of the core components of SAP HANA XS is to provide http(s) access to the underlying data within SAP HANA by leveraging open technologies such as OData, REST, JavaScript and HTML5. The example outlined in this blog implements a simple version of the classical ERP CO-PA probability analysis scenario whereby a Calculation view encapsulates the business logic to provide insights into a company’s product profitability.  My team within SAP, Customer Solution Adoption (CSA), commonly runs into customer implementations where CO-PA data is of significant interest to the customer and runtimes on large scale implementations make a strong business case for the incorporation of HANA as a platform for CO-PA operations.  We will discuss several examples on how to efficiently expose the Calculation view using open standards such as REST, OData, JSON, JavaScript, and to render the result within a HTML user interface using SAPUI5.

SAP HANA modeled views

As a general rule modeled views (i.e. Calculation, Analytical and Attribute views) should be considered as a first choice when developing content for SAP HANA. They are design time objects and once deployed as runtime objects they become highly optimized in-memory columnar database views that will exploit the various optimizers within SAP HANA with the sole intent to maximize performance. Since the business logic is embedded directly within the models this automatically enables calculations and filters to be pushed down and parallelized deep within the database.  What distinguishes modeled views from SQL views is that they generate a dynamic execution path that supports query stripping and join omission and will, for example, only perform the calculations against those columns requested making them good candidates for large data sets where optimal performance is required.

It is important to consider the following basic principles when developing content for SAP HANA: first, ensure that business logic and calculations are pushed down into the database either via modeled views or procedures. Second, minimize the data movement between different layers.  To fully capture the performance benefits of SAP HANA adequate attention needs to be given when designing applications in order to ensure exploitation of the underlying engines based on the application requirements. The CO-PA scenario in our examples utilizes several engines that are concealed from end-users. First the heavy-lifting number crunching sales-data is retrieved and processed using several Analytical views executed by the OLAP engine. The aggregated reduced results-sets from these views are encapsulated and combined within a Calculation view executed by the Calculation engine. In turn the results of the Calculation view are handed over to the XS engine, then the data-set is then sent to the user’s browser via the built-in SAP HANA XS Web server.

Calculation view

The baseline Calculation view that is to be exposed through http(s) implements the classical union with constants pattern that combines different input sources in the most optimal fashion for in-memory columnar processing. The data is grouped by Sales Organization and Material and consist of several calculations that will eventually be used by the business to provide insight into product profitability.

Figure 1 – Graphical Calculation view

F1.png

 

In our example, the following SQL statement and result-set satisfy the business scenario handed to us as a requirement for our development project within SAP HANA XS. 

Figure 2 – SQL statement

F2.png

As the result-set confirms, the data-set that will be exposed through SAP HANA XS is not only grouped by Sales Organization and Material but also filtered by materials starting with ‘DPC’. 

Figure 3 – SQL results

F3.png

XS Project

In order to develop and deploy SAP HANA XS content the different artifacts (i.e. text files) must exist first within specific local projects. Throughout this blog we will be using the standard SAP HANA Development XS Project. This section assumes that the reader has some familiarity using the SAP HANA Development Perspective and is able to create a local project and link the local project to the repository within SAP HANA for source code and version management.

XS Projects contains several different development artifacts depending on the requirements of the application. Required files that enforce authorization have extensions (.xsaccess), (.xsapp), and (.xsprivileges), optional files include server-side Java Script (.xsjs), and client-side UIs using SAPUI5 (.html).  OData Service Definition Language files have extensions (.xsodata). Unlike the SAP HANA Studio modeler perspective that have sophisticated UIs for creating models, content for the SAP HANA XS can be created using simple text editors which come standard within SAP HANA Studio. It is important to note that XS Projects also contain specific libraries known as the SAP HANA XS JavaScript APIs that should be used when creating server-side JavaScript content.

Figure 4 below displays a typical XS Project structure; the CO-PA example artifacts are stored in a local project called (copaproj) and linked to a repository package called (copaxs) within SAP HANA. 

Figure 4 – XS Project example

F4.png

Depending on the http(s) port of SAP HANA, any html pages, server-side JavaScript pages or OData services can be served by using URL syntax such as:

>> http://hanaserver:port/copaxs/ui/PlannedvsActualSales.html

Similarly OData Service Definition Language files can be referenced the same way.

>> http://hanaserver:port/copaxs/odata/PlannedvsActualCalculationView.xsodata


 

OData

The first steps when exposing content within SAP HANA through OData is to create an OData Service Definition Language file referencing the particular database object.  Although Calculation views are used in the example the same principles applies to Analytical views.

Figure 5 – OData Service Definition Language file

F5.png

Line 2:  The <::> notation specifies the Calculation view as a runtime object that is located within the <copa> package.  It is also possible to reference design time objects. For details refer to the SAP HANA Developer guide.

Line 3: Objects that do not have a unique key in their results (i.e. Calculation views & Analytical views) requires the generated <local> key entry. Be aware that this key value numbers the result starting with 1 and is not meant for dereferencing any item in the result set. The key is valid only for the duration of the current session and is used only to satisfy OData’s requirement for a unique id in the results.

Line 4:  <Aggregates always> is required for Calculation views and Analytical views where the information regarding attributes and measures are stored within specific metadata tables.  That means that the aggregate always statement will ensure that this information is derived from metadata; hence the notion of derived aggregation.  This however means that whenever the underlying Calculation view changes this OData Service Definition Language file needs to be re-activated. This can be overcome in line 2 by referencing a design time object instead of a runtime object.

Hint:  You can verify the location of the existing modeled view referenced in the OData Service Definition Language file by expanding the Content folder, and the <copa> package from within the Navigator view.

Figure 6 – Package structure using Navigator view

F6.png

Once activated the OData Service Definition Language file can be called using a web browser as follows.

>> http://hanaserver:port/copaxs/odata/PlannedvsActualCalculationView.xsodata

Figure 7 – OData Service Definition Language file

F7.png

The ODate Service Definition Language result indicates that a collection can be obtained by appending <PlannedVsActualCalcView> to the URL. 

Hint: Be aware of the following nuance that applies to multi-dimensional reporting (i.e. Calculation views and Analytical views) and specifically when aggregating data. In order to aggregate, the <$select> parameter should be used as it will inform the OData service to return an aggregated subset.

>>http://hanaserver:port/copaxs/odata/PlannedvsActualCalculationView.xsodata/PlannedvsActualCalcView/?$select=SALESORG,ANETREVN

Figure 8 – OData service response

F8.png

At this point we have good understanding how to construct the URL query in order to call the Calculation view via http(s) and we can proceed to display the results in a more readable HTML tabular fashion.

SAPUI5

SAP UI development toolkit for HTML5 (SAPUI5) is a client-side cross-browser JavaScript library for building modern rich internet applications. It is JavaScript OpenAjax-compliant and it fully supports SAP’s product standards. It is extensible, lightweight and easy to consume and can be combined with 3rd-party JS libraries. As is standard with SAPUI5 applications the UI5 JavaScript libraries need to be loaded before they can be used, using the bootstrap notation pointing to the correct location within the script tag.

Figure 9 – SAPUI5 Library

F9.png

The data-sap-ui-libs declaration is used to import other SAPUI5 properties, functions and objects. The Navigator view can be used to verify the installation and location of SAPUI5.

Figure 10 – SAPUI5 Library in Navigator view package structure

F10.png

Hint: A good source of information regarding SAPUI5 can be found locally on the SAP HANA server.

>> http://hanaserver:port/sap/ui5/1/sdk

Figure 11 – SAPUI5 API

F11.png

The next step requires attention to detail- it is important to understand the handshake options available between the client-side JavaScript code and the server-side service. Since much of the heavy-lifting coding has already been done behind the scenes within SAPUI5, you can simply tap into the framework which will significantly speed up your development effort. There are several options available depending on the data format and protocol used.

Figure 12 – Client-side Java Script using SAPUI5

F12A.png

F12B.png

Line 23:  The first step in rendering any data using SAPUI5 is to create an instance of a particular model. SAPUI5 implements strict MVC patterns whereby the model retrieves the data and allows for data binding to UI elements. Several predefined models are available in SAPUI5; this blog has examples for the OData, JSON and XML models.

The OData model is a server-side model, so only the data that is requested by the UI is loaded from the server; any change of binding or list operations requires a new request to the server.  The OData model’s constructor consists of one required parameter which is the URI pointing to the root of the OData Service Definition Language file location. 

Line 24: Exposes the OData Model to the SAPUI5 libraries and makes it known to the framework.

Line 30: This line is not important; it is a quick and easy way to minimize the coding by using a custom helper function to loop through the required columns.

Line 34 – 39. The OData model is bound to the table using the collection property found with the OData Service Definition Language file. Notice the select parameter used earlier.

Line 40: Places the table object within the <div> tags on line 47 (Figure 13) and renders the table result. 

Figure 13 – HTML area to render table resuts

F13.png

After successful activation and deployment the CO-PA Planned vs Actual Sales report is displayed showing the products similar to what was displayed earlier using the SAP HANA Studio SQL Editor.  However, the example is not complete; the products need to be filtered.

Figure 14 – Data result rendered using SAPUI5

F14.png

JSON

Typical, business requirements demand greater flexibility via runtime parameter selection usually to restrict the data. It is always easier to first establish the particular URL query parameter before embarking on the front end coding. This also provides a glimpse of how the JSON data is structured by appending the format=json parameter.

Hint: Filtering is achieved by introducing the <$filter> and <startswidth> parameters as follows.

>> /copaxs/odata/PlannedvsActualCalculationView.xsodata/PlannedvsActualCalcView?$select=MATERIAL,ANETREVN&$filter=startswith(MATERIAL,”DPC’)&$inlinecount=allpages&$format=json

Hint: The <inlinecount> parameter can be used to verify the correct number of expected results.

Figure 15 – OData with filtering in JSON format

F16.png

Hint: Take note of the document root element <d:> including the child element <results:>. Those elements will be referenced later when binding the data using SAPUI5.

In contrast to the OData model the JSON model is a client-side model, the data of the model is loaded completely and is available on the client. It is therefore meant for small datasets, which are completely available on the client, it does not contain any mechanism for server based paging. Notice the <$select> clause that contains all the required column names including the <filter=startswith> parameter. 

Figure 16 – JavaScript using JSONModel in SAPUI5

F16B.png

Figure 17 – Binding results using JSON Model

F18.png

Line 17:  Sets and binds the JSON model to the table UI element.

Line 18:  Binds the result set rows to the table using the </d/results> elements seen earlier within within the JSON document.

Figure 18 – Final results aggregated and filtered

F19.png

The data is aggregated and filtered according to the requirements.

 

Server-side JavaScript

The final example will be familiar to classical web developers. It uses the well-established server-side development approach whereby sever side coding is used to interact with the SAP HANA database. It is good practice to become familiar with the SAP HANA XS JavaScript API. Currently the API consists of a Request Processing API to handle http(s) request and responses and secondly a Database API to communicate with the underlying SAP HANA Database.

Figure 19 – SAP HANA XS JavaScript API location

F20.png

The following is a classic example using server-side JavaScript very similar other web development technologies. Server-side JavaScript coding can be done within any text editor but requires an extension (.xsjs). 

Figure 20 – Server-side JavaScript

F20B.png

Line 7:  The <$> sign is used to reference classes within the API.

The majority of the instructions shown below simply loop through the result set and packages up the data in XML format.  Similarly HTML can be imbedded directly within the JavaScript code.  XML is used in this example with the sole intent to utilize the SAPUI5 XML model and to separate out the UI processing.  Refer to the SAP HANA XS JavaScript API for additional parser information as they become available in future releases. 

Figure 21 – Server-side JavaScript

F21B.png

XML

Figure 22 – Server-side JavaScript producing XML

F23.png

The server-side JavaScript file produces the following results.  Notice the <product> element; this will be used within SAPUI5 to bind the data to the UI table.

Figure 23 – Client-side JavaScript using HTML5 XMLModel

F24.png

Notice that the XML models constructor takes a URL parameter string pointing to the unqualified location of the server-side JavaScript end-point. Finally notice when binding the rows to the tables the XML <product> element is used as seen in the previous step.

Figure 24 – Final results

F25.png

Conclusion

The above information will help you get started.  Since HANA is new, exciting and evolving, as revisions come out there may be additional ways to meet your goals.  As you are coding if you find more elegant solutions please don’t hesitate to comment on your experiences and share your findings with everyone.

Appendix

Calculation View with a Single Input Paramater:

service  {

      “copa::PLANNED_ACTUAL_SALES ” as “PlannedvsActualCalcView”

      keys generate local “ID”

      aggregates always

      parameters via entity;  

}

http://server:port/copaxs/odata/PlannedvsActualCalcView.xsodata/

PlannedvsActualCalcViewWithInputParamsParameters(‘.20’)/ Results?$select=salesorg,material,anetrevn&$filter=startswith(material,’DPC’)

Calculation View with multiple Input Paramaters:

service  {

      “copa::PLANNED_ACTUAL_SALES ” as “PlannedvsActualCalcView”

      keys generate local “ID”

      aggregates always

      parameters via entity “InputParams“; 

http://server:port/copaxs/odata/PlannedvsActualCalcView.xsodata/

InputParams(ip_discount=’.20’,ip_region=’NW’)/Results?$select=salesorg,material,anetrevn&$filter=startswith(material,’DPC’)&$format=json

To report this post you need to login first.

23 Comments

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

  1. Fahad Ajaib

    Hi,

    I am facing a problem while using HANA XS. When I update a file (like HTML Page) in HANA Repository and try to view this updated HTML page in browser, I can’t see the updated page infact I see the old version of the page. I also tried to delete the browser history but still no use. Also i noticed that inside HANA the file is not getting activated immedietly and take some time to activate like 1 hour. Please guide me in this regard, what to do so that I can see the updated version of a file immedietly

    Thanks and best regards.

    Fahad

    (0) 
    1. Makoto Sugishita

      Hi Fahad,

      I have seen a similar problem with the older revision of HANA Studio like rev. 45.

      If the problem persists, I would recommend you to udpate it to the latest revision.

      Regards,

      Makoto

      (0) 
  2. Manu Schmitt

    Hi,

    is it possible to create an OData service which also contains (multiple) calculation variables?

    I have read, that it s possible to use them at run-time.

    Thanks and best regards,

    Manu

    (0) 
    1. Werner Steyn Post author

      Hello Manu,

      Yes, is this what you are looking for?

      filter=startswith(MATERIAL,’DPC’) or startswith(MATERIAL,’ACT’)

      Regards, Werner

      (0) 
  3. Fahad Ajaib

    Hi, 

    How can I use Username and Password with JSON Model? like with OData Model we can use username and password as:

    var oModel = new sap.ui.model.odata.ODataModel(sServiceUrl, false, “username”, “password”);

    Thanks.

    fahad

    (0) 
    1. Makoto Sugishita

      Hi Fahad,

      If you are using the same HANA system for both REST service and UI5 screen, you don’t need user id or password to access the REST service URL.

      At the time of first authentication with UI5 screen, you will get the session cookie with the name of “xsSessionId“. And the session cookie will be used until you close your browser.

      You also can check the API of sap.ui.model.json.JSONModel.

      As far as I see, loadData method does not have the variable to pass user id or password.

      Hence, if you want to access the external REST API, you need to access the data with XMLHttpRequest or jQuery.ajax() and then  need to bind to JSON model.

      Regards,

      Makoto

      (0) 
  4. Joseph Chavadiyil

    In UI5 when using odata services on Calc views with parameters, we have the foll. Entity sets which are shown as part of the metadata

    <EntitySet Name=”TOP_N_PRODUCT” EntityType=”hana-poc.Top_N_Products_Price.TOP_N_PRODUCTType”/>

    <EntitySet Name=”TOP_N_PRODUCTParameters” EntityType=”hana-poc.Top_N_Products_Price.TOP_N_PRODUCTParametersType”/>

    These when used in the browser returns the right results

    http://xxxx:8000/hana-poc/Top_N_Products_Price.xsodata/TOP_N_PRODUCTParameters(P_COUNT=5,P_KUNNR=”,P_SPART=’10’,P_VKORG=’1000′,P_VTWEG=’10’)/Results?$format=json

    When this odata service is used via SAP UI5, UI5 generates foll queries which are which are incorrect

    http://xxxx:8000/hana-poc/Top_N_Products_Price.xsodata/TOP_N_PRODUCTParameters(P_KUNNR='0000100001‘,P_SPART=’10’,P_VKORG=’1000′,P_VTWEG=’10’,P_COUNT=5)/$metadata

    http://xxxx:8000/hana-poc/Top_N_Products_Price.xsodata/TOP_N_PRODUCTParameters(P_KUNNR='0000100001‘,P_SPART=’10’,P_VKORG=’1000′,P_VTWEG=’10’,P_COUNT=5)/TOP_N_PRODUCT/$count

    http://xxxx:8000/hana-poc/Top_N_Products_Price.xsodata/TOP_N_PRODUCTParameters(P_KUNNR='0000100001‘,P_SPART=’10’,P_VKORG=’1000′,P_VTWEG=’10’,P_COUNT=5)/TOP_N_PRODUCT?$skip=0&$top=100&$inlinecount=allpages

    Please suggest what could be the issue?

    1. SAP UI5 version
    2. Issues of declaration
    (0) 
  5. Kumar Mayuresh

    Hi Werner

    I am having a tricky problem with SAP HANA XS – Odata service.

    I am using calculation view (scripted) with 4 input parameters and want to consume the same in Odata web service. I was able to Odata file and my calculation view successfully but during web service testing input parameters are not being identified – error for the parameter ends up with “no property found”.

    I am using SAP HANA one Rev 52.

    awaiting your feedback.

    Regards

    Kumar 🙂

    (0) 
  6. Rakshetha J N

    hello

    I would like to know if it is possible to add external chart libraries to XS…i am not using SAPUI5…i would like to include a extrernal js file…like say d3.js… is it allowed… the js file is showing a lot of syntax errors. what is a possible solution ?

    (0) 
  7. John Appleby

    Hey Tom,

    The option:

    parameters via entity “InputParams”;

    Doesn’t appear to work for Variables, as are used in HANA Live. So if you try to use a HANA Live object, you get the following error when you activate the OData object:

    livetiles.odata:stock.xsodata

    No parameters found for object “sap.hba.ecc::MaterialValuatedStockQuery”.

    Maybe I’m missing something?

    John

    (0) 
  8. Siddhartha Deepak

    Hi,

    Thanks Werner for a really nice blog to understand the OData basics.

    I am facing some issues after replicating the above steps.

    I have created a Scripted Calculation View:

    CV_SALERANKING with Parameters (IP_FR_DT- Date type ,IP_TO_DT – Date type ,IP_REGION – Varchar(25)) to give the sales ranking for an organization’s customs.

    Created a corresponding OData service for the Cal. View as: –

    service { “ABAP::CV_SALERANKING” as “CV_SALESRANKING” keys generate local “ID”
    aggregates always parameters via entity “InputParams”; }

    Accessing the OData Service using the URL :

    http://vblhdevqa:8000/UI5Project_Test/CV_SALESRANKING.xsodata/InputParams(IP_FR_DT=datetime'20140101‘,IP_TO_DT=datetime’20140930′,IP_REGION=’24’)/Results?$select=CUSTOMER_NAME,SALES_RANK

    I’m getting the result as :

    <?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?><feed xml:base=”http://vblhdevqa:8000/UI5Project_Test/CV_SALESRANKING.xsodata/” xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” xmlns=”http://www.w3.org/2005/Atom“><title type=”text”>Results</title><id>http://vblhdevqa:8000/UI5Project_Test/CV_SALESRANKING.xsodata/Results</id><author><name /></author><link rel=”self” title=”Results” href=”Results” /></feed>

    Can’t seem to figure out the problem.

    Regards

    Sid

    (0) 
    1. Makoto Sugishita

      Hi Sid,

      This simply looks like there is no entry for the query.

      You will get the entry count zero if you set the parameter of inlinecount by adding this to the end of URL.

      &$inlinecount=allpages

      I guess it might be a problem to pass data to the input parameters but I’m not sure.

      Regards,
      Makoto

      (0) 
      1. Siddhartha Deepak

        Hi Makoto,

        You are right in saying that there is no entry for the query, as I added &$inlinecount=allpages at the end of my URL. and got the result as <m:count>0</m:count>.

        But the strange thing is when I run my Scripted Calc. View as a Data Preview, I do get 4 rows of data for the same parameters. How so?

        Regards

        Sid

        (0) 
  9. Jordan Tchorbadjiyski

    Hi Werner,

    Do you happen to know how the inlinecount parameter can be accessed from an ODataModel? I get the correct node in the OData feed,

    <m:count>10</m:count>

    However I can’t seem to find any way to use it for binding a control in SAPUI5…

    Thanks in advance,

    Jordan

    (0) 
  10. Amit Kumar Singh

    Hi Werner,

    Thanks for the wonderful blog. This helped me a lot.

    I need your input as the UI5 Application I built is little slower due to 6-7 oData call in a single view. I need to read data from various calculation view. I have one oData created for each Calculation view.

    I wanted to know if we can combine these into one oData Call?

    Cheers/Amit

    (0) 
      1. Yoppie Ariesthio

        Hi Makoto,

        Thank you for your reply.

        I’m using UI5 XML view to consume Hana View.

        <Table id=”table1″ xmlns=”sap.ui.table”>

        </Table>

        can’t get any data loaded by using same way as Werner (binding an oData to my table)

        Thanks

        (0) 

Leave a Reply