Friday, 27 May 2016

Step by Step: Visualization using XS-based Application

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.

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

Step by Step: Visualization using XS-based Application

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

Step by Step: Visualization using XS-based Application

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

Step by Step: Visualization using XS-based Application

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

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

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.

Step by Step: Visualization using XS-based Application

Source: scn.sap.com