Introduction:

Over the course of last few years, SAP HANA has truly evolved from the next generation database to “Real Time Data Platform”. Now all aspects of an application can be handled in the context of HANA platform i.e.

  1. Data provisioning (native EIM capabilities for real-time replication, ETL and data quality)
  2. High performance data base
  3. Sophisticated Application Libraries (BFL, PAL libraries etc.)
  4. Scalable Data Models (Attribute, Analytic/Calculation Views & Decision Tables)
  5. HTML5 compatible application development (using XS)

In this article we are going to take a step by step approach in creating an XS-based application. For more information on XS service, please refer Introducing…SAP HANA Extended Application Services (XS)

In this article, we are going to build a Calculation View and build SAPUI5 based visualizations (Tables and Graphs) to display the data from the Calculation View. This article assumes, you are familiar with HANA Modelling and HANA studio.

Step 1: Working with SAP HANA Development Perspective


We’ll use the “SAP HANA Development” perspective for HANA native application development (like XS-based application).  You need the following authorizations to work with HANA repository.

— ENABLE MODELING INFORMATION VIEWS

GRANT MODELING TO I833916;

— ENABLE HANA NATIVE DEVELOPMENT

GRANT EXECUTE ON REPOSITORY_REST TO I833916;

  GRANT REPO.READ, REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS, REPO.MAINTAIN_NATIVE_PACKAGES ON “.REPO_PACKAGE_ROOT” TO I833916;

Step 2: Creating an XS project

1_CreateProject.PNG

In the “SAP HANA Development” perspective, open the Project Explorer view.  Navigate to File -> New -> Other. (or Right Click: New -> Other)

2_SelectWizard_XS Project.PNG

Select “XS Project” under SAP HANA -> Application Development

3_ProjectName.PNG

Select your project name and click Next. In this case I’m using “myapp” as my project name

4_AddWorkspace.PNG

Click “Add Workspace” to create a Workspace. Select the HANA System and give a name for your workspace and click “Finish”. I’m using “myapp_ws” for my workspace. Click “Next” to proceed to the next step.

5_CreateObject.PNG

Select the checkboxes to create two objects (.xsaccess & .xsapp) and click “Finish”. You can also use this screen to create Schema, Tables etc.

  This should setup your project “myapp” under the Project Explorer view.

Step 3: Creating Information Models

Now we’ll proceed with creating an calculation view.  Select your project “myapp” and right click (or Navigate to File -> New -> Other . Select “Calculation View” under  SAP HANA -> Database Development -> Modeler -> Calculation View.

6_InformationModels.PNG

As you see below, this interface is similar to the calculation view interface using “Modeler” perspective of the HANA Studio.

7_CalculationView.PNG

Build your calculation as you may like. As you see in the picture above, I’m simply exposing the data from an underlying table.  However, using the same method you can build the most complex models (combination of Attribute, Analytic and Calculation views). 


When done, you can use the “Validate”, “Activate” and “Activate All” buttons on the toolbar to validate/activate your models.

Verify your calculation view using a quick “Data Preview” option (in “Project Explorer” or “System”)

Please note you can also build these views using the “Modeler” perspective and skip this step altogether if you have already built your views.

After you activate your calculation view, you can also check the view the in “Repositories” and “System” navigation views as shown above.

8_CalculationView.PNG

Step 4: Setting up the Folders:

For managing different types of objects we are going to create for this exercise, let’s create 3 folders “views”, “services” & “odata” under our project.

9_CreateFolder.PNG

Step 5: Create XSJS Service to read from Calculation View

Now we’ll create an XSJS Service using server side Javascript to read data from the calculation and expose the data in JSON format. This XSJS service can be called using HTTP or HTTPS.

Let’s create a file “salesOrderService.xsjs” under the “services” folder. To do so, select the “services folder”, Right Click: New -> File.

Use the following code and activate the “salesOrderService.xsjs”.


var select_all_sales_orders_query =
                    "SELECT TOP 10 CUSTOMERID, SUM(NETSALES) AS NETSALES, SUM(COST) AS COST " +
                    "FROM \"_SYS_BIC\".\"myapp/CA_SALES_ORDER\" " +
                    "GROUP BY CUSTOMERID " +
                    "ORDER BY NETSALES DESC";
function close(closables) {
          var closable;
          var i;
          for (i = 0; i < closables.length; i++) {
                    closable = closables[i];
                    if(closable) {
                              closable.close();
                    } 
          }
}
function getSalesOrders(){
          var salesOrdersList = [];
          var connection = $.db.getConnection();
          var statement = null;
          var resultSet = null;
          try{
                    statement = connection.prepareStatement(select_all_sales_orders_query);
                    resultSet = statement.executeQuery();
                    var salesOrder;
                
                    while (resultSet.next()) {
                              salesOrder = {};
                              salesOrder.customerid = resultSet.getString(1);
                              salesOrder.netsales = resultSet.getDouble(2);
                              salesOrder.cost = resultSet.getDouble(3);
                              salesOrdersList.push(salesOrder);
                    }
          } finally {
                    close([resultSet, statement, connection]);
          }
          return salesOrdersList;
}
function doGet() {
          try{
                    $.response.contentType = "application/json";
                    $.response.setBody(JSON.stringify(getSalesOrders()));
          }
          catch(err){
                    $.response.contentType = "text/plain";
                    $.response.setBody("Error while executing query: [" + err.message + "]");
                    $.response.returnCode = 200;
          }
}
doGet();

Note: If your calculation view is under a different package, please make appropriate changes to the code.

Save and activate the “customerTable.view.js” view

Now let’s execute the “salesOrderService.xsjs” to see the data in JSON format.

10_SalesOrderServices.PNG

Step 6: Create a view (SAPUI5 Table) to visualize data

We’ll use SAPUI5 view to visualize the data from salesOrderService.xsjs. In this example we’ll use a SAPUI5 Table component to display the data.


For more information about SAPUI5, please visit SAPUI5 SDK – Demo Kit


We’ll create a view “customerTable.view.js” to consume the data from “salesOrderService.js”.  We’ll also create an HTML page “index.html” to embed the view to display in browser.

So  let’s create a file “customerTable.view.js” under the “views” folder. To do so, select the “views” folder, Right Click: New -> File.

Use the following code and activate the “customerTable.view.js”


sap.ui.jsview("views.customerTable", {
          /** Specifies the Controller belonging to this View.
          * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
          * @memberOf views.customerTable
          */
          getControllerName : function() {
                    return null;  
          },
          /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
          * Since the Controller is given to this method, its event handlers can be attached right away.
          * @memberOf views.customerTable
          */
          createContent : function(oController) {
   var oLayout = new sap.ui.commons.layout.MatrixLayout({width:"100%"});
 
   var oModel = new sap.ui.model.json.JSONModel();
   oModel.loadData("services/salesOrderService.xsjs");      
 
   var oControl;
   this.oSHTable = new sap.ui.table.Table("soTable1",{
            visibleRowCount: 10,
            });
   //Table Column Definitions
   oControl = new sap.ui.commons.TextView().bindProperty("text","customerid");
   this.oSHTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "CUSTOMERID"}),
   template: oControl, sortProperty: "CUSTOMERID", filterProperty: "customerid", filterOperator: sap.ui.model.FilterOperator.EQ, flexible: true }));
 
   oControl = new sap.ui.commons.TextView().bindProperty("text", "netsales");
   oControl.setTextAlign("End");
   this.oSHTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "NETSALES"}),
   template: oControl, sortProperty: "NETSALES", filterProperty: "NETSALES", hAlign: sap.ui.commons.layout.HAlign.End}));
   oControl = new sap.ui.commons.TextView().bindProperty("text","cost");
   oControl.setTextAlign("End");
   this.oSHTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "COST"}),
   template: oControl, sortProperty: "COST", filterProperty: "COST", hAlign: sap.ui.commons.layout.HAlign.End}));
 
   this.oSHTable.setModel(oModel);
   this.oSHTable.bindRows("/");
 
   this.oSHTable.setTitle("Top 10 Customer by Sales");
   oLayout.createRow(this.oSHTable);
 
   return oLayout;
}
});

Save and activate the “customerTable.view.js” view

Let’s create a file “index.html” in our project. Use the following code and activate the “index.html”


<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src="/sap/ui5/1/resources/sap-ui-core.js" id="sap-ui-bootstrap"
  data-sap-ui-libs="sap.ui.ux3,sap.ui.commons,sap.ui.table,sap.viz"
  data-sap-ui-theme="sap_goldreflection">
              </script>
<script>
  sap.ui.localResources("views");
  var view = sap.ui.view({id:"customerTable", viewName:"views.customerTable", type:sap.ui.core.mvc.ViewType.JS});
  view.placeAt("contentTable");
</script>
</head>
<body class="sapUiBody" role="application">
  <div id="contentTable"></div>
  <div id="contentGraph"></div>
</body>
</html>

Invoke the “index.html” display the customerTable view in Browser.

11_IndexTable.PNG

Step 7: Create SAPUI5 View (Bar Chart) to visualize data

Now let’s create another view “customerGraph.view.js” to display data from “salesOrderService.js” in a Bar Chart.

So  let’s create a file “customerGraph.view.js” under the “views” folder. To do so, select the “views” folder, Right Click: New -> File.

Use the following code and activate the “customerGraph.view.js”


sap.ui.jsview("views.customerGraph", {
          /** Specifies the Controller belonging to this View.
          * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
          * @memberOf views.customerGraph
          */
          getControllerName : function() {
                    return null;  
          },
          /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
          * Since the Controller is given to this method, its event handlers can be attached right away.
          * @memberOf views.customerGraph
          */
          createContent : function(oController) {
                    var topSalesCustomerBarChart = new sap.viz.ui5.Bar("topTenCustomerBarChart", {
                              width : "100%",
                              height : "50%",
                              xAxis: {
                                        title: { visible: true, text : "EUR" }
                              },
                              title : {
                                        visible : true,
                                        text : 'Top Ten Customers by Sales'
                              }
                              ,
                              interaction: new sap.viz.ui5.types.controller.Interaction({
                             selectability: new sap.viz.ui5.types.controller.Interaction_selectability({
                             mode: sap.viz.ui5.types.controller.Interaction_selectability_mode.single})
                                  }),
                              dataset : topSalesDataset = new sap.viz.ui5.data.FlattenedDataset({
                                        // a Bar Chart requires exactly one dimension (x-axis)
                                        dimensions : [ {
                                                  axis : 1, // must be one for the x-axis, 2 for y-axis
                                                  name : 'Customer',
                                                  value : "{customerid}"
                                        }],
                                        // it can show multiple measures, each results in a new set of bars
                                        // in a new color
                                        measures : [
                                        {
                                                  name : 'Net Sales', // 'name' is used as label in the Legend
                                                  value : '{netsales}' // 'value' defines the binding for the
                                        },
                                        {
                                                  name : 'Cost', // 'name' is used as label in the Legend
                                                  value : '{cost}' // 'value' defines the binding for the
                                        }
                                        ],
                                        // 'data' is used to bind the whole data collection that is to be
                                        // displayed in the chart
                                        data : {
                                                  path : "/"
                                        }
                              })
                    });
                    var salesModel = new sap.ui.model.json.JSONModel();
                    salesModel.loadData("services/salesOrderService.xsjs");
                    topSalesCustomerBarChart.setModel(salesModel);
                    return topSalesCustomerBarChart;
}
});

Save and activate the “customerGraph.view.js” view


Use the following code and activate the “index.html”. See the code changes in line 14 and 15 below


<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src="/sap/ui5/1/resources/sap-ui-core.js" id="sap-ui-bootstrap"
  data-sap-ui-libs="sap.ui.ux3,sap.ui.commons,sap.ui.table,sap.viz"
  data-sap-ui-theme="sap_goldreflection">
              </script>
<script>
  sap.ui.localResources("views");
  var view = sap.ui.view({id:"customerTable", viewName:"views.customerTable", type:sap.ui.core.mvc.ViewType.JS});
  view.placeAt("contentTable");
  var view = sap.ui.view({id:"customer", viewName:"views.customerGraph", type:sap.ui.core.mvc.ViewType.JS});
  view.placeAt("contentGraph");
</script>
</head>
<body class="sapUiBody" role="application">
  <div id="contentTable"></div>
  <div id="contentGraph"></div>
</body>
</html>


Invoke the “index.html” display the “Table” and “Bar Chart” view in Browser.


12_IndexTableGraph.PNG

Step 8: Dynamic Visualization using VizContainer

The VIZContainer provides a set of reusable HTML5 based UI controls that easily enable application developers to create an analytical application using a generic UX pattern. With VIZContainer, you are able to switch the visualization types to see the data from a different point of view, or feed data on the fly to the visualization that helps get new insights.

For more details Table – SAPUI5 Demo Kit: VizContainer

Let’s create another view “customerMultiGraph.view.js” to switch visualization dynamically based on the data from “salesOrderServices.xsjs”.

Use the following code and activate the “customerMultiGraph.view.js”


sap.ui.jsview("views.customerMultiGraph", {
          /** Specifies the Controller belonging to this View.
          * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
          * @memberOf views.customerMultiGraph
          */
          getControllerName : function() {
                    return null;  
          },
          /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
          * Since the Controller is given to this method, its event handlers can be attached right away.
          * @memberOf views.customerMultiGraph
          */
          createContent : function(oController) {
              var oModel = new sap.ui.model.json.JSONModel();
              oModel.loadData("services/salesOrderService.xsjs");
            
         // A Dataset defines how the model data is mapped to the chart
              var oDataset = new sap.viz.ui5.data.FlattenedDataset({
                  // a Bar Chart requires exactly one dimension (x-axis)
                  dimensions : [ {
                      axis : 1, // must be one for the x-axis, 2 for y-axis
                      name : 'Customer',
                      value : "{customerid}"
                  } ],
                  // it can show multiple measures, each results in a new set of bars in a new color
                  measures : [
                  // measure 1
                  {
                      name : 'NetSales', // 'name' is used as label in the Legend
                      value : '{netsales}' // 'value' defines the binding for the displayed value 
                  } ],
                  // 'data' is used to bind the whole data collection that is to be displayed in the chart
                  data : {
                      path : "/"
                  }
              });
            
              // create a VizContainer
              var oVizContainer = new sap.viz.ui5.VizContainer({
                  'uiConfig' : {
                      'layout' : 'vertical',
                      'enableMorphing' : true
                  },
                  'width': '100%',
                  'height': '100%'
              });
        
              // attach the model to the chart and display it
              oVizContainer.setVizData(oDataset)
              oVizContainer.setModel(oModel);
        
              // set feeds
              var aobjCustomer = new sap.viz.ui5.controls.common.feeds.AnalysisObject({
             uid : "customer_id",
                  name : "Customer",
                  type : "Dimension"
              });
              var aobjNetSales = new sap.viz.ui5.controls.common.feeds.AnalysisObject({
             uid : "netsales_id",
                  name : "NetSales",
                  type : "Measure"
              });
              var feedPrimaryValues = new sap.viz.ui5.controls.common.feeds.FeedItem({
           uid : "primaryValues",
                  type : "Measure",
                  values : [ aobjNetSales ]
              });
              var feedAxisLabels = new sap.viz.ui5.controls.common.feeds.FeedItem({
             uid : "axisLabels",
                  type : "Dimension",
                  values : [ aobjCustomer ]
              });
    
              oVizContainer.addFeed(feedPrimaryValues);
              oVizContainer.addFeed(feedAxisLabels);
        
              // attach event listener for feedschange
              oVizContainer.attachEvent('feedsChanged', function(e) {
                  // You could add your own logic to handle feedsChanged to set new dataset to vizContainer.
                  // Reset current data for demo purpose.
                  oVizContainer.setVizData(new sap.viz.ui5.data.FlattenedDataset({
                      dimensions : [ {
                          axis : 1,
                          name : 'Customer',
                          value : "{customerid}"
                      } ], measures : [ {
                          name : 'NetSales',
                          value : '{netsales}'
                      } ], data : {
                          path : "/"
                      }
                  }));
                  oVizContainer.setModel(oModel);
              });
        
              return oVizContainer;
}
});

Save and activate the “customerMultiGraph.view.js” view.

Let’s create a file “graph.html” in our project. Use the following code and activate the “graph.html”



<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src="/sap/ui5/1/resources/sap-ui-core.js" id="sap-ui-bootstrap"
  data-sap-ui-libs="sap.ui.ux3,sap.ui.commons,sap.ui.table,sap.viz"
  data-sap-ui-theme="sap_goldreflection">
              </script>
<script>
  sap.ui.localResources("views");
  var view = sap.ui.view({id:"multiGraph", viewName:"views.customerMultiGraph", type:sap.ui.core.mvc.ViewType.JS});
  view.placeAt("content");
</script>
</head>
<body class="sapUiBody" role="application">
  <div id="content"></div>
</body>
</html>

Invoke the “graph.html” display the graphs dynamically.


13_MultiGraph.PNG


References:

8 Easy Steps to Develop an XS application on the SAP HANA Cloud Platform

Introducing…SAP HANA Extended Application Services (XS)

Table – SAPUI5 Demo Kit

Conclusion:

As shown above, creating a simple XS-based application to visualize data is straight forward. Following the steps outlined here should get you started in creating your next generation application.


See the next article on using OData service to create the same visualization  Step by Step: Visualization using XS-based Application – Part 2

To report this post you need to login first.

8 Comments

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

  1. Sergio Guerrero

    Abani,

    this is a very useful blog. If I had to change anything I would recommend to use an odata file instead of using the XSJS service and you would replace all that code in your XSJS file with a 1 line of code in XSODATA. The XSOData service will still give you the same result as what the XSJS service has here with less code.

    other than that, I like your post. thank you for sharing

    (0) 
      1. Saritha Koroth

        Hi Abani,

        I was following your above example and have a query on how to invoke your xsjs appilication url if you are having parameters being passed?

        meaning how will the below url look incase of parameters ??

        “in the screenshot “:8002/myapp/services/salesOrderService.xsjs””

        Regards,

        Saritha K

        (0) 
        1. Sergio Guerrero

          hi Saritha,

          when you need to pass parameters you can look into odata querying or when having a parametized view, look into the developer guide for the odata aggregations.. there is a specific syntax when you have a parametized view. hope this helps

          server/path/file.xsodata/endpoint(param_name=’value’)   is one example

          (0) 
  2. Abdul Waheed

    Hi Abani

    While browsing for inbuilt calculation view I do not have any table named FCTCUSTOMERORD in my calculation view list.

    How did you get it ?

    Should we manually get CustomerID, EmployeeId……..?

    (0) 
  3. Anindya Bose

    Hi Abani

    Very nicely written article and helped me to expose my calculation view via XS for the first time. I think this would be very helpful for people who do not have a background in web development.    Can you please tell me how can I pass different variables to this SQL query ? Let’s say I have another JSON/XML array with some values and I want to pass that to this SQL query. Is that possible ?

    Thanks for your response !

    (0) 

Leave a Reply